Nullability settings with select into and variables

By: Chad Boyd   |   Updated: 2009-09-10   |   Comments   |   Related: > TSQL

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.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips