SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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;
    CREATE TABLE Null_Test (NotNullCol NotNull );
    CREATE TABLE Null_Test (NotNullCol varchar(50) );
  • 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


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...