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))

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?).