SQLcommitted

ANSI_NULL_DEFAULT Vs ANSI_NULL_DFLT_ON

ANSI_NULL_DEFAULT:

This option is set at database level.

When ANSI_NULL_DEFAULT option is ON, columns are comply with SQL-92 rules for column nullability. If this option is set to ON, if we don’t specify a column with allow NULL explicitly, It will allow NULL. If this option is set to off and if we are defining any new column without specifying nullability constraint then it will not allow NULL for the new column.

You can verify this database option value from is_ansi_null_default_on column of catalog view sys.databases.

ANSI_NULL_DFLT_ON:

This option is applies to set the environment of a session. It overrides the option ANSI_NULL_DEFAULT only if the ANSI_NULL_DEFAULT is set to OFF.

While creating or modifying a table (to add new Columns) if we are not explicitly specifying the nullability constraint, it will allow Null value for the new column if ANSI_NULL_DFLT_ON is set to ON.

Like ANSI_NULL_DFLT_ON we have ANSI_NULL_DFLT_OFF option too.

Note:

- Both ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF can’t be set to ON at the same time but both options can be set to OFF at the same time.

- The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server   automatically ANSI_NULL_DFLT_ON to ON when connecting.

- When ANSI_DEFAULTS is ON, ANSI_NULL_DFLT_ON is also ON.

- ANSI_NULL_DFLT_ON is set at run time not at parse time.

- This option doesn’t apply to tables created using Select Into statement.

Note: These setting are only affected when the nullabilty of columns are not specified explicity in Create or Alter table statement .

Let’s see how ANSI_NULL_DFLT_ON and ANSI_NULL_DEFAULT option works together

Example 1: When ANSI_NULL_DEFAULT is OFF

Alter Database SQLCommitted  SET ANSI_NULL_DEFAULT OFF;

GO

Create Table Table_1 (Column_1 Int)

GO

Here in Example 1, Database option ANSI_NULL_DEFAULT is set to OFF and also explicit nullability constraint is not specified. In this case it shouldn’t allow NULL value for column Column_1.

Let’s try to insert NULL into table Table_1

INSERT INTO Table_1 (Column_1) VALUES (NULL);

GO

Example 2: When ANSI_NULL_DEFAULT is OFF and ANSI_NULL_DFLT_ON is ON.

Alter Database SQLCommitted  SET ANSI_NULL_DEFAULT OFF;

GO

SET ANSI_NULL_DFLT_ON ON;

GO

Create Table Table_2 (Column_2 Int)

GO

Here in example 2, the Database option ANSI_NULL_DEFAULT is off but ANSI_NULL_DFLT_ON is set to ON. In this case the database option will override by ANSI_NULL_DFLT_ON setting which will allow accepting NULL value.

Let’s try to insert NULL into table Table_2

INSERT INTO Table_2 (Column_2) VALUES (NULL);

GO

Example 3: When ANSI_NULL_DEFAULT is OFF and ANSI_NULL_DFLT_ON is also OFF.

Alter Database SQLCommitted  SET ANSI_NULL_DEFAULT OFF;

GO

SET ANSI_NULL_DFLT_ON OFF;

GO

Create Table Table_3 (Column_3 Int)

GO

Here in example 3 both ANSI_NULL_DEFAULT and ANSI_NULL_DFLT_ON are OFF. Now if you try to insert NULL value it will throw error.

INSERT INTO Table_3 (Column_3) VALUES (NULL);

GO

In rest of the examples we’ll see there will be no impact of ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF setting if ANSI_NULL_DEFAULT is set to ON.

Example 4: When ANSI_NULL_DEFAULT is ON and ANSI_NULL_DFLT_ON is ON

SET ANSI_NULL_DFLT_ON ON;

GO

CREATE TABLE Table_4 (Column_4 TINYINT);

GO

INSERT INTO Table_4 (Column_4) VALUES (NULL);

GO

In the above example 4, Both ANSI_NULL_DEFAULT and ANSI_NULL_DFLT_ON set to ON, in result it allow NULL value for Column_4 of Table_4.

Example 5: When ANSI_NULL_DEFAULT is ON and ANSI_NULL_DFLT_ON is OFF

Here in the below example we’ll see even though the ANSI_NULL_DFLT_ON value is set to OFF it will allow NULL because the database option ANSI_NULL_DEFAULT is set to ON. So it doesn’t matter whether you set ANSI_NULL_DFLT_ON to ON or OFF when ANSI_NULL_DEFAULT is set to ON.

SET ANSI_NULL_DFLT_ON OFF;

GO

CREATE TABLE Table_5 (Column_5 TINYINT);

GO

INSERT INTO Table_5 (Column_5) VALUES (NULL);

GO

(1 row(s) affected)

Tags: ANSI_NULL_DEFAULT,ANSI_NULL_DFLT_ON,NULL,environment,session,ANSI_NULL_DFLT_OFF,options,ANSI_DEFAULTS

Reference : MSDN

May 29, 2012 Posted by SQLcommitted | SQL Server Database properties | ANSI_NULL, database option, database properties | 2 Comments