How Default Value and Nullable Column Works? – Interview Question of the Week #129

Question: How Default Value and Nullable Column works together for a single table?

Answer: This question is indeed a very interesting. It is quite possible that different user will interpret it differently. Recently, when I was helping an organization to select DBAs, and Developer, I heard this question from another person from the interviewing panel. Before I the candidate answer this question, I intervened and asked him to clarify this question.

Well, after a while, we come up with a really good script which can explain the mind of the interviewer.

Please see following two SQL scripts and observe its output as well.

Script 1:

In this case a table has three columns and two columns have default values. Out of the two columns, one is nullable and another one is not nullable .

-- Test1
CREATE TABLE TestTable 
		(ID INT, 
		NotNullCol INT NOT NULL DEFAULT 1, 
		NullCol INT NULL DEFAULT 1)
GO
INSERT INTO TestTable (ID)
VALUES (1)
GO
SELECT *
FROM TestTable
GO
DROP Table TestTable
GO

Once we created a table, we inserted the data into the table.

Now let us select the data from the table and it will bring following resultset.

You can clearly see from the result set that when we insert the data both the columns with default values contains default values.

Script 2:

In this case a table has only one column.

Once we created a table, we inserted a value in it.

-- Test 2 
CREATE TABLE TestTableAgain
		(ID INT)
GO
INSERT INTO TestTableAgain (ID)
VALUES (1)
GO
ALTER TABLE TestTableAgain
ADD NotNullCol INT NOT NULL DEFAULT 1
GO
ALTER TABLE TestTableAgain
ADD NullCol INT NULL DEFAULT 1
GO
SELECT *
FROM TestTableAgain
GO
DROP Table TestTableAgain
GO

After that we altered table and added two columns with the default value. One of the newly added column is nullable and the other is not nullable.

Now let us select the data from the table and it will bring following result set.

You can clearly see from the result set that one of the column, which is nullable with default value contains the NULL value and the column which is not nullable with default value contains default values.

The Real Question

Now what the interviewer was actually asking was  – Why SQL Server is demonstrating such a behavior?

The Simple Answer

When a user adds a column with default to a table in SQL Server, which already exists, it always checks the nullable property of the column. If the column is nullable then it will create the column with a NULL value instead of the default value, however, if column is not nullable and there is a default value, SQL Server has to apply that value to column to avoid violating not null constraint.

Though the question was a bit complex, I hope the answer is clear.

Reference: Pinal Dave (https://blog.sqlauthority.com)