Does NULL have a data type?

Question

I ran into code similar to this today.

SELECT AuditDomain, 
    ObjectId,
    AuditSubdomain = CONVERT(VARCHAR(50), NULL),
    SubDomainObjectId = CONVERT(INT, NULL)
FROM Audit

It seems to imply that data type information can be associated with a NULL value. Does this attach metadata to the NULL value identifying it as the specified data type?

This post details a way to find a data type in Sql Server but when I try the following line it comes back as NULL:

SELECT CAST(SQL_VARIANT_PROPERTY(CONVERT(INT, NULL), 'BaseType') AS VARCHAR(20))

Drift

have you tried googling "null datatype"? You may find that it's not a datatype... and the reason the above code works is because a varchar can be null. – Esau

Answer 1

In SQL Server, NULL is an INT by default in all of the scenarios I can think of. You can determine this with the following code:

SELECT x = NULL INTO #x;
EXEC tempdb..sp_columns '#x';

Results:

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
--------------- ----------- ---------- ----------- --------- ---------
tempdb          dbo         #x___...   x           4         int

Before you've put it into a table or otherwise associated it with some contextual metadata, what does that buy you? What difference does it make it it is INT or DATETIME or something else? What will you do with that information?

SQL_VARIANT_PROPERTY returns NULL because it appears to require both metadata and a value to be meaningful. Observe (using a different type just to mix it up):

SELECT SQL_VARIANT_PROPERTY(NULL, 'BaseType');

DECLARE @x DATE;

SELECT SQL_VARIANT_PROPERTY(@x, 'BaseType');

DECLARE @y DATE = SYSDATETIME();

SELECT SQL_VARIANT_PROPERTY(@y, 'BaseType');

Results:

NULL

NULL

date

So it seems to need both a type and a value in order to accurately determine base type.

As for exactly why it works this way, shrug. You'd have to ask folks with source code access.

Note that NULL only has to adopt a base type when you've forced SQL Server's hand: you've created a table based on it. It could very well have been the case that SQL Server would return an error in this situation (and in fact many situations where it has to guess at what data type you meant). The way to avoid this is to not create situations where SQL Server has to guess (which is why I asked, what will you do with this information?).

Tolly

Do you have an answer for why the sql_variant_property function is returning NULL? – Mississippian

This is used with a UNION. The two tables don't have the same schema and where the second table is missing a column they have put these CONVERT(<DataType>, NULL) statements. I've run multiple tests where I use NULL instead of converting and everything works as expected. Perhaps it was a misunderstanding of how UNIONs and/or type conversion works. – Drift

@Drift I bet just having NULL without the convert would have worked fine, but perhaps it may depend on whether the first table in the UNION had missing columns. – Tolly

Counterexample (even using UNION): SELECT CAST(NULL AS int) UNION SELECT 'abc' vs. SELECT NULL UNION SELECT 'abc'. The former fails because int has higher precedence than varchar, so an error is reported for the invalid conversion from 'abc' to type int. The latter succeeds, so apparently doesn't treat NULL as having type int. Yet the semantics for UNION do specify that types are determined for all columns in all subqueries. – Hermaphrodite

I can think of at least two counter scenarios (i) SELECT DATEADD(day, NULL, CURRENT_TIMESTAMP) fails; it refuses to treat null as integer by default (ii) SELECT MIN(x) FROM (VALUES (NULL)) AS v(x) fails in the absence of explicit data type. The SELECT INTO behavior is inconsistent imo. – Pocketbook

Answer 2

Of couse NULL has data type. Try next code to confirm:

SELECT 
  CAST(NULL AS date) AS c1,
  CAST(NULL AS time) AS c2
INTO #x;
EXEC tempdb..sp_columns '#x';

And i think that there is bug in SQL_VARIANT implementation because it lossing information about NULL type. Too bad!

Vitebsk

Answer 3

Null has no data type. The purpose of a null is to represent an "unknown", both in value and type.

ISNULL() returns the datatype for the first argument with a data type presented to it.

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

Sting

Answer 4

I know you already have some good answers but SQL_VARIANT_PROPERTY I think has been misunderstood.

You use SQL_Variant_Property with a column and then an indication what you want on it's metadata property. However if it is null it does not tell you much.

EG:

declare 
    @Start date = getdate()
,   @End datetime= getdate()
,   @Int int = 1
,   @DateNull date
;


select 
    sql_variant_property(@Start, 'BaseType')
,   sql_variant_property(@End, 'BaseType')
,   sql_variant_property(@Int, 'BaseType')
,   sql_variant_property(@DateNull, 'BaseType')

Will return three datatypes and a null. Handling NULL is a big part of SQL. A great many people, myself included, at times want to handle null with a value to represent it or else at other times not care. SQL Variant ONLY WORKS for a populated value with the argument: 'BaseType', else it will return a null. As far as I know this is due to SQL saying: "You have no data here, nothing to determine for memory use."

Generally I will specify isnull(@thing, 0) when working with integers were I explicitly want a dataset to include zeros for unknowns. At other times I may wish to have a user know a null occurrence did something else isnull(@thing, 'not present') for a report. Still other times you can use coalesce for basically an array of posibilities coalesce(@thing, @otherthing, @yetotherthing, 'unknown').

I think in the code you are witnessing someone is converting something when I am not certain of where you would really need to do such a thing. The datatype of a column in a table is maintained of what it needs to be and SQL will not store memory in it when it is NULL. So the need to change it seems arbitrary IMHO. I do know that you can handle better memory consumption when you expect more nulls with the SPARSE option that was introduced I believe in SQL 2008. But I don't know when to cast something that consumes hardly anything to be more.

Dissimulate

Answer 5

I am not agreed with @aaron-bertrand. Since By default SQl server created the column with integer datatype to store the NULL value as Null can be inserted in Int, Datetime, Date, Varchar any other column.

When inserting Null in a table with SELECT x = NULL INTO #x;

SQl server created integer type column by default as Null can be inserted to Integer column.

So may be it is due to the nature of the query we written "SELECT x = NULL INTO #x;" which makes the column as integer and put the NULL value.

Pye

Answer 6

SQL Server 2022 appears to have a "datatype" of NULL. Compare the results of the following two queries.

SELECT SUM(v)
FROM (
    VALUES (NULL)
        ,(cast(NULL AS INT))
    ) AS t(v)

Output: NULL

SELECT SUM(v)
FROM (
    VALUES (NULL)
        ,(NULL)
    ) AS t(v)

Output: Operand data type NULL is invalid for sum operator.

The first query explicitly casts NULL as INT. This would seem to demonstrate that NULL and INT are both datatypes, and that INT has a higher precedence. However, I've never seen this documented anywhere.

Acroter