Default nullability of a new column

,

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

Rate

Share

Share

Rate