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.

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)