When working with SQL Server, it is important to understand how default values and nullable columns work together in a single table. This concept can sometimes be confusing, but with the right explanation, it becomes clear.

Let’s consider a scenario where we have a table with multiple columns, some of which have default values and some are nullable. To illustrate this, we will use two SQL scripts.

Script 1

In this script, we create a table called TestTable with three columns. Two of these columns have default values, with one being nullable and the other not nullable.

CREATE TABLE TestTable (
	ID INT,
	NotNullCol INT NOT NULL DEFAULT 1,
	NullCol INT NULL DEFAULT 1
)

INSERT INTO TestTable (ID)
VALUES (1)

SELECT *
FROM TestTable

DROP TABLE TestTable
	

After creating the table and inserting data into it, we can observe the result set. As expected, both columns with default values contain the default values.

Script 2

In this script, we create a table called TestTableAgain with only one column. After inserting a value into the table, we alter the table and add two columns with default values. One of these columns is nullable, while the other is not.

CREATE TABLE TestTableAgain (
	ID INT
)

INSERT INTO TestTableAgain (ID)
VALUES (1)

ALTER TABLE TestTableAgain
ADD NotNullCol INT NOT NULL DEFAULT 1

ALTER TABLE TestTableAgain
ADD NullCol INT NULL DEFAULT 1

SELECT *
FROM TestTableAgain

DROP TABLE TestTableAgain
	

After altering the table and adding the new columns, we can observe the result set. In this case, the nullable column with a default value contains a NULL value, while the non-nullable column with a default value contains the default value.

The Explanation

The question that the interviewer was actually asking is why SQL Server demonstrates this behavior. The answer is quite simple.

When a user adds a column with a default value to an existing table in SQL Server, the database engine checks the nullable property of the column. If the column is nullable, it will create the column with a NULL value instead of the default value. However, if the column is not nullable and there is a default value specified, SQL Server applies that value to the column to avoid violating the not null constraint.

Although the question may have seemed complex, the answer is clear. Understanding how default values and nullable columns work together in SQL Server is essential for effective database design and development.