SET ANSI_NULL_DFLT_ON (TSQL Statement)

forces new columns that have no NULL or NOT NULL clause to be specified as NULL.

Syntax

SET ANSI_NULL_DFLT_ON {ON | OFF}

Remarks

This option only affects parsing stage, not execution stage.

When a new table is created, if a column has no NULL or NOT NULL clause, it will be specified with a NULL clause.

By default, this option is ON.

Examples

SET ANSI_NULL_DFLT_ON OFF

CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10), c DATE NULL);

SHOW T t1;

SET ANSI_NULL_DFLT_ON ON

CREATE TABLE t2 (a INT NOT NULL, b VARCHAR(10), c DATE NULL);

SHOW T t2;

The result is:

CREATE TABLE [dbo].[t1] (
    [a]                   INT                  NOT NULL,
    [b]                   VARCHAR(10)          NOT NULL,
    [c]                   DATE                 NULL
);

CREATE TABLE [dbo].[t2] (
    [a]                   INT                  NOT NULL,
    [b]                   VARCHAR(10)          NULL,
    [c]                   DATE                 NULL
);

The column b is NOT NULL when the option is OFF, and NULL when the option is ON (default).