Ever assume that when you don’t specify NULL or NOT NULL on a new column it’s going to allow NULLs? I always did. Turns out I was wrong.

When you don’t specify NULL vs NOT NULL it will usually default to NULL. Here are some exceptions where it will default to NOT NULL.

• Use the PRIMARY KEY clause on column creation

  CREATE TABLE Null_Test (NotNullCol varchar(50) PRIMARY KEY );

• Data type is timestamp

  CREATE TABLE Null_Test (NotNullCol timestamp );

• Data type is an user data type that was defined as NOT NULL

  CREATE TYPE NotNull FROM varchar(50) NOT NULL;
  GO
  CREATE TABLE Null_Test (NotNullCol NotNull );
  GO

• SET ANSI_NULL_DFLT_OFF is set ON

  SET ANSI_NULL_DFLT_OFF ON;
  GO
  CREATE TABLE Null_Test (NotNullCol varchar(50) );
  GO

• The ANSI_NULL_DEFAULT setting of the database is set to OFF and both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON are set to OFF

To be safe it’s probably a good idea to include NULL or NOT NULL when defining a column. Just saying.

Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication, T-SQL Tagged: code language, default settings, language sql, sql statements, T-SQL