CODEWITHSUNDEEP
sqlsql-servert-sqlnull
Jason
Jason

Reputation: 8640

Does NULL have a data type?

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

Upvotes: 13

Views: 27785

Answers (6)

bendodge
bendodge

Reputation: 514

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.

Upvotes: 0

Parthasarathi Adhikary
Parthasarathi Adhikary

Reputation: 33

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.

Upvotes: 0

limit
limit

Reputation: 31

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!

Upvotes: 1

djangojazz
djangojazz

Reputation: 13242

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.

Upvotes: 0

Joe Catania
Joe Catania

Reputation: 1

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

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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

Upvotes: 18

Related Questions