SET ANSI_NULL_DFLT_ON & SET ANSI_NULL_DFLT_OFF

This is the ninth post of SET Statement series.

By default when you add a column to the table without specifying the nullability , column will be nullable.

If you run this script,

CREATE TABLE Test
(ID INT)

table will be as follows.

You can change the behavior of this by SET ANSI_NULL_DEFT_OFF to ON as shown below.

SET ANSI_NULL_DFLT_OFF ON

CREATE TABLE Test
(ID INT)

You can see that nullability of the column is off.

When SET ANSI_NULL_DFLT_ON is ON, new columns created allow null values if the nullability status of the column is not explicitly specified. When SET ANSI_NULL_DFLT_OFF is ON, new columns created does not allow null values if the nullability status of the column is not explicitly specified.

SET ANSI_NULL_DFLT_ON does not affect columns created with an explicit NULL or NOT NULL.

Both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON cannot be set ON at the same time. If one option is set ON, the other option is set OFF.

If the both options are off , is_ansi_null_default_on value of the database will be taken when tables are created.

SELECT name,is_ansi_null_default_on FROM sys.databases

You can change this option from the database options page as shown below.

The setting of SET ANSI_NULL_DFLT_ON does not apply when tables are created using the SELECT INTO statement. It applies only for CREATE TABLE and ALTER TABLE statements.

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL

SET FORCEPLAN

SET PARSEONLY

SET IDENTITY_INSERT

SET FMTONLY