Nullability settings with select into and variables


By:   |   Updated: 2009-09-10   |   Comments   |   Related: More > T-SQL


Development Best Practices for SQL Server

Free MSSQLTips Webinar: Development Best Practices for SQL Server

Attend this webinar to learn about development best practices for SQL Server. Andy Warren will share his many years of experience to give some pointers on what has worked best for him and how you can utilize some of this knowledge.


Traditionally there has been a single option for pulling data from one result set into a new table using a minimally logged operation without pushing the data to a flat file first - the select...into statement (if you aren't familiar with the new minimally-logged insert...into functionality in Sql 2008, you should check it out). One of the irritations I've had with this operation revolves around the way Sql handles setting the nullability attributes for columns created from a static variable value in the new table create from the select...into statement (for which I have a workaround further down). Take the following simple code example:

use tempdb;
go

if object_id('tempdb..#testSelectIntoNullability') > 0
drop table #testSelectIntoNullability;
go

declare @created_on datetime;
select @created_on = getutcdate();

select @created_on as variable_column,
'hard-coded-value' as hard_coded_column,
o.name as object_name
into #testSelectIntoNullability
from sys.objects o;
go

select name as column_name, is_nullable as column_is_nullable
from sys.columns c
where c.object_id = object_id('tempdb..#testSelectIntoNullability');
go

Here are the results:

column_name column_is_nullable
variable_column 1
hard_coded_column 0
object_name 0

If you run the code, you'll notice that the "varible_column" column is nullable, despite the fact that the column was created from a scalar, static, non-null variable. IMHO, there is no reason the engine shouldn't be able to decipher this fact and create the column as non-nullable, just as it does with the "hard_coded_column" column created from a static hard-coded value inline with the select statement. Of course, I'm sure part of the reasoning behind why it behaves this way is due to the meta-data behind the variable structure that allows variables to contain actual null values, but still, if it's been set prior to the statement execution, seems reasonable to me that the resulting column should be non-nullable by default since every row is guaranteed to have a value (at least initially).

A simple workaround for this problem is to create a scalar temp-table with a non-nullable column matching the data-type semantics of the variable, insert the variable value into the temp table, and then cross-join this scalar table with the actual data you want to insert - such as in the following code:

use tempdb;
go

if object_id('tempdb..#testSelectIntoNullability') > 0
drop table #testSelectIntoNullability;
go
if object_id('tempdb..#scalarData') > 0
drop table #scalarData;
go
create table #scalarData (variable_column datetime not null);
go

declare @created_on datetime;
select @created_on = getutcdate();

insert #scalarData (variable_column)
select @created_on;

select s.variable_column as variable_column,
'hard-coded-value' as hard_coded_column,
o.name as object_name
into #testSelectIntoNullability
from #scalarData s
cross join sys.objects o;
go

select name as column_name, is_nullable as column_is_nullable
from sys.columns c
where c.object_id = object_id('tempdb..#testSelectIntoNullability');
go

Here are the results:

column_name column_is_nullable
variable_column 0
hard_coded_column 0
object_name 0

You may be wondering at this point why this is such an irritation to me - well, that's another blog post (my next one) but it involves partitioning, tiering data across different storage and filegroups, and partitioned views.



Last Updated: 2009-09-10


get scripts

next tip button



About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips



Comments For This Article





download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

Find MAX value from multiple columns in a SQL Server table

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms