What is the Default Datatype of NULL? – Interview Question of the Week #131

Interview question: What is the default data type of an untyped NULL in SQL Server?

Answer: NULL itself is a marker for an absent value, not a universal data type. But SELECT NULL AS col INTO #TempTable creates an int column, because SQL Server must choose a concrete type for the new table. The answer is int for that experiment, not “every NULL is an int.”

When I ask this question, many DBAs and developers tell me NULL has no data type. That’s a fair answer until you notice the word default in my question. I want to see what SQL Server infers when it has no type information to inherit.

Here is the test, with a catalog query that makes the resulting column type easy to inspect:

IF OBJECT_ID(N'tempdb..#NullType') IS NOT NULL
    THROW 50135, 'Demo temporary table exists. Open a fresh query window.', 1;
SELECT NULL AS col
INTO #NullType;
SELECT c.name AS column_name,
       TYPE_NAME(c.user_type_id) AS inferred_type
FROM tempdb.sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'tempdb..#NullType');
DROP TABLE #NullType;

The inferred_type is int. Now make the intended type explicit:

IF OBJECT_ID(N'tempdb..#TypedNull') IS NOT NULL
    THROW 50135, 'Demo temporary table exists. Open a fresh query window.', 1;
SELECT CAST(NULL AS varchar(20)) AS col
INTO #TypedNull;
SELECT c.name AS column_name,
       TYPE_NAME(c.user_type_id) AS inferred_type
FROM tempdb.sys.columns AS c
WHERE c.object_id = OBJECT_ID(N'tempdb..#TypedNull');
DROP TABLE #TypedNull;

This time the new column is varchar. A SELECT INTO column follows the type of its select-list expression. SQL Server may infer a different type when a surrounding expression or target column supplies context, so use an explicit CAST when a result contract matters.

The useful takeaway is the distinction between an absent value and the concrete type SQL Server assigns to an expression in a specific context.

NULL is not a data type, it is a missing value, and SELECT INTO still has to give its column one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.