Data Type Chosen by SQL Server for Null Column

Question

After this question: "SQL: What does NULL as ColumnName imply" I became curious about what data type would sql server choose for the column in this query:

SELECT NULL as aColumn

So I ran this tests:

select * from (select null as aaa) resulta
UNION ALL
select 6.54

Yelds two rows:

aaa
---------------------------------------
NULL
6.54

While this one:

select * from (select null as aaa) resulta
UNION ALL
select 'ab'

In SQL Server 2000 yelded this:

aaa
-----------
NULL
Msg 245, Level 16, State 1, Line 5
Syntax error converting the varchar value 'ab' to a column of data type int.

and in SQL Server 2008 gave this:

aaa
----
NULL
ab

So any guess as to what data type sql server chooses for a all null column?

Answer 1

SQL Server will take the first data type that is explicitly defined, otherwise, it will pick a type that implicitly holds the data.

In your example, the first value that it can type is a numeric value based on the decimal point. Therefore, the table is defined as such. When you try to insert text, it can not make the conversion.

Read the link to get a good understanding of the compatible data types. There is a good chart near that end.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 2

In SQL Server 2008 I used this to create a temp table and then query the schema of the newly created table:

select null as col1
into #test

exec tempdb..sp_help '#test'

The results showed that the column was an int.