SQL Interview: 2: NULL and NOT NULL when defining Columns in Tables

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

When you are writing a CREATE TABLE statement to define a new table, after each column, you can write NULL or NOT NULL, but doing that is optional.

Why is it a good practice to include it, and what happens if you leave it out? Are the columns defined as NULL or as NOT NULL? Are there any exceptions to this?

Answer:

If you leave it out NULL and NOT NULL in a column definition, many factors determine how the column will be defined. For system-defined data types, SQL Server uses a combination of ANSI_NULL_DFLT_ON SET option and the ANSI_NULL_DEFAULT_ON database option to determine which value to use.

There are several exceptions. For a few examples, a column declared as part of a PRIMARY KEY will bet set to NOT NULL regardless of settings. SPARSE columns will always be set to NULL. For CLR types and alias data types, the NULLability is determined by the data type.

It’s always a good practice to write NULL or NOT NULL in the table definition.

2021-03-25