What is the Default Datatype of NULL? – Interview Question of the Week #131

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.

What is the Default Datatype of NULL? - Interview Question of the Week #131 nulldatatype-800x387

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)

SQL Datatype, SQL Server
Previous Post
What does BACKUP WITH NAME Command do? – Interview Question of the Week #130
Next Post
How to Count Week Days Between Two Dates? – Interview Question of the Week #132

Related Posts

11 Comments. Leave new

  • In MySQL its binary(0)

    Reply
  • Peter Davies
    July 17, 2017 7:02 pm

    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

    Reply
  • wilfred van Dijk
    July 17, 2017 7:43 pm

    nice to know, but not really a question for an interview

    Reply
  • Not a good question for an interview.

    Reply
  • Super Cricket
    July 17, 2017 10:48 pm

    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.

    Reply
  • 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) ?

    Reply
  • It doesn’t matter whether a question for interview or not, nice to know the fact, that matters.

    Reply
  • good explanations

    Reply
  • SELECT * FROM sys.dm_exec_describe_first_result_set(‘
    SELECT NULL AS col
    ‘, NULL, 0)

    Reply
  • So how do you control the datatype of NULL column when using SELECT INTO TABLE statement?

    Reply

Leave a Reply