Conclusion: The default datatype of NULL is INT (wait lets first prove
it, we might be wrong).
Yesterday my teammate called me and showed me a strange behavior of NULL
with character datatype in union clause (in Teradata database) and this strange
behavior is applicable to most of databases (NOT in SQL Server J).
Let’s try to stimulate the same scenario.
Here we have below Union statement
select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4
union
select 'x' as col1, 'y' as col2, null, null
col1 col2 col3 col4
---- ---- ----------- -----------
a
b 100 200
x
y NULL NULL
(2 row(s) affected)
There are four columns in query, first two columns are of character
family datatype and col3 and col4 are from Integer family datatype.
Now look at the second query
select 'x' as col1, 'y' as col2, null, null
Here, notice that first two columns are of char datatype and rest two columns
are of NULL and union worked perfectly.
Lets revise the union rule:
1.
Column number
should match in all select query involved in union
2.
The datatype of
column in one select query should match with other select query participating
in UNION.
Now return back to our first select statement in UNION query
select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4
So by Union rule book, any query doing union with above query should
have first two columns as character datatype and last two columns as INT
datatype.
Now if you see our second query
select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4
union
select 'x' as col1, 'y' as col2, null, null
First two columns are of character datatype which matches with the
datatype of first query and unlike first two columns, last two are NULL which doesn’t
match with the datatype of first query col3 and col4 datatype. Still query work
fine.
There could be two reason why query worked fine:
- NULL is compatable with INT datatype
- The is an implicit conversion happening with NULL and INT datatype, something like this ( cast( NULL as INT)
Let dig it further by rewriting the query
select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4
union
select 'x', NULL, NULL, NULL
Above query fails, that could means
- Compiler is able to do implicit conversion of NULL to character database
- NULL is not compatible to Character family datatype.
Now let’s rewrite the query and cast the NULL in second column of second
query
select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4
union
select 'x', cast (NULL as varchar(1)), NULL, NULL
col1 col2 col3 col4
---- ---- ----------- -----------
a
b 100 200
x
NULL NULL NULL
The query work fine.
We can inference from above set of query that NULL is compatible with
INT but not with CHAR datatype.
Let find out the root clause of this.
The question must be in your mind must be: Does NULL has any DEFAULT
datatype? Lets try to find out this answer.
Here I am storing result of query in temporary table #tempotable
select null as col1 into #tempotable
Lets now see the table and column property of #tempotable
TABLE_QUALIFIER TABLE_OWNER COLUMN_NAME TYPE_NAME PRECISION LENGTH
-----------------------------------------------------------------------
tempdb dbo
col1 int 10 4
J here we can see Datatype of NULL is treated as INT Type with 4 byte
length.
What we concluded, default datatype of NULL is INT??
Wait before concluding anything so early J
Let declare a variable of variant type and assign it with NULL
declare @var sql_variant
= NULL
Now let find out the datatype of @Var variable
select sql_variant_property(@var, 'Basetype') as TypeName,
sql_variant_property(@var, 'Precision') as Precision,
Output:
Type Name Precision
------------------------------
NULL NULL
Now this shows NULL has no datatype J
What we concluded: NULL has no datatype, but when it takes part in forming
result set, the compiler consider NULL as INT datatype with 4 byte length and
10 precision.
NOTE: SQL Server 2005 had this issue, in all later versions they
rectified this issue and have high degree of NULL handling Enjy coding…SQL J
Post Reference: Vikram Aristocratic Elfin Share
