Is there a NULL data type?

Question

I came across code similar to this today.

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

It looks like data type information might be associated with a NULL value. Is metadata related to a NULL value that identifies it as the specified data type?

This post describes how to search for a data type on Sql Server, but when I try the following line, it returns as NULL:

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

Answer 1

In SQL Server, NULL defaults to INT in all scripts 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 put it in a table or otherwise linked to some contextual metadata, what do you buy? What is the difference: INT or DATETIME or something else? What will you do with this information?

SQL_VARIANT_PROPERTY returns NULL because it seems that both metadata and value are required to make sense. Observe (use a different type to mix it):

 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 

Thus, to accurately determine the underlying type, it seems that both the type and the value are needed.

As for exactly why he works this way, shrug. You should ask people with access to the source code.

Note that NULL should only accept the base type when you force the SQL Server command: you created a table based on this. It is possible that SQL Server will return an error in this situation (and in fact, in many situations where it must guess what type of data you had in mind). The way to avoid this is not to create situations where SQL Server has to guess (which is why I asked what you will do with this information?).

Answer 2

Null does not have a data type. The purpose of null is to represent the "unknown", both by value and by type.

ISNULL () returns the data type for the first argument with the data type provided to it.

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

Answer 3

I know that you already have good answers, but SQL_VARIANT_PROPERTY I think this is misunderstood.

You use SQL_Variant_Property with a column, and then specify what you want from its 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 data types and zero. NULL processing is a big part of SQL. A lot of people, including me, sometimes want to process a zero with a value to represent it, and sometimes at other times they don’t care. SQL Variant ONLY WORKS for a populated value with an argument: "BaseType", otherwise it will return zero. As far as I know, this is because SQL says: "You have no data here, nothing needs to be defined to use memory."

Usually I will specify isnull (@thing, 0) when I work with integers, I explicitly want the dataset to include zeros for unknowns. In other cases, I probably want the user to know that the NULL occurrence was doing something else isnull (@thing, 'not present') for the report. At the same time, you can use coalesce to create a number of features (@thing, @otherthing, @yetotherthing, "unknown").

I think that in the code you are observing, someone converts something when I'm not sure where you really need to do this. The data type of the column in the table is supported by what it needs, and SQL will not store memory in it when it is NULL. Therefore, the need to change this seems arbitrary IMHO. I know that you can handle higher memory consumption when you expect more zeros with the SPARSE parameter that was introduced. I believe in SQL 2008. But I do not know when to throw something that consumes almost nothing to be more.

Answer 4

Couse NULL specifies a data type. Try the following 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 there is an error in the implementation of SQL_VARIANT, because it has lost information about the type NULL. Too bad!

Answer 5

I do not agree with @ aaron-bertrand. Since, by default, the SQl server created a column with an integer data type to store the NULL value, since Null can be inserted into Int, Datetime, Date, Varchar in any other column.

When pasting Null into a table with SELECT x = NULL INTO #x;

The SQl server has created a default integer column, because Null can be inserted into an Integer column.

Perhaps this is due to the nature of the requested "SELECT x = NULL INTO #x;" which makes the column integer and puts a NULL value.