Question: What is the Default Datatype of NULL?
Answer: This is a very popular question to ask to various DBA and developers. The most common answer I usually receive is – there is no datatype for the NULL value. A very fair point. However, please read the question again –
“What is the Default Datatype of NULL?”
The word default is a keyword here.
Well, let us find out the answer of this question today.
NULL is undefined and every column can have NULL values except columns with timestamp datatype (which represent NULL values differently). There is a simple method to know the default datatype of NULL values.
First, let us create a table with SELECT INTO TABLE Syntax and then we will try to find out the datatype of the column where we have stored NULL. Please note that we are not providing any datatype for the NULL value.
SELECT NULL AS col INTO #TempTable
Now let us look at the structure of the table and see what datatype is assigned to the column named col in the table #TempTable
EXEC tempdb..sp_columns #TempTable
Now please look at the column named Type_Name in the resultset. You will find that the default datatype is an Integer.
So, theoretically, I agree that it is difficult to come up with the datatype of the NULL value, but in SQL Server the default datatype of the NULL is an Integer.
Do let me know what you think of this blog post. When I discovered this very first time, I personally found it very interesting.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
In MySQL its binary(0)
Its more economical to have a default int which is fixed known number of bytes. I don’t find it particularly interesting myself as an interview question. What does it prove about the person and how would we utilize this in the application of any DBA work.
BY the way liked your Group By video , now that was interesting
nice to know, but not really a question for an interview
Not a great question for an Admin position but great question for a Developer position. This question helps when writing UNION (ALL) queries and getting datatype errors because of selecting a NULL for a column.
This blog has come in handy when explaining these types of datatype errors.
Not a good question for an interview.
He he… It is indeed fun to see so many says not a good question for interview.
Please note that you are allowed to NOT ask this question in interview. This questions are for beginners and not for all of you who knows everything.
There are many people like me who learned this today.
Ok, but what is the practical implication of this ? what does it matter whether the default is int or varchar (or any other datatype for this matter) ?
Comes in handy when explaining datatype errors in UNION (ALL) queries
It doesn’t matter whether a question for interview or not, nice to know the fact, that matters.
good explanations
SELECT * FROM sys.dm_exec_describe_first_result_set(‘
SELECT NULL AS col
‘, NULL, 0)
So how do you control the datatype of NULL column when using SELECT INTO TABLE statement?