Ansi null DFLT Off statement in SQL Server

The SET ANSI_NULL_DFLT_OFF setting in SQL server controls the default nullability of new column in create table or alter table statement, only when we don’t explicitly specify 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. Therefore, either ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON can be set ON, or both can be set OFF. If either option is ON, that setting (SET ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON) takes effect.

Let’s see the example

First of all, we need to set it on database level

ALTER DATABASE test_DB SET ANSI_NULL_DEFAULT ON;  -- Create table ANSI_NULL_DEFAULT_ON_DFLT.  CREATE TABLE ANSI_NULL_DEFAULT_ON_DFLT (nm Varchar(50));  GO  -- NULL INSERT should succeed.  INSERT INTO ANSI_NULL_DEFAULT_ON_DFLT (nm) VALUES (NULL);  GO    select * from ANSI_NULL_DEFAULT_ON_DFLT

 

Table created but we cannot able to insert the record. IF we want to insert null then explicitly we need provide the null or not null column value as below.

-- Create table ANSI_NULL_DEFAULT_ON_DFLT.  CREATE TABLE ANSI_NULL_DEFAULT_ON_DFLT (nm Varchar(50) null);  GO  -- NULL INSERT should succeed.  INSERT INTO ANSI_NULL_DEFAULT_ON_DFLT (nm) VALUES (NULL);  GO    select * from ANSI_NULL_DEFAULT_ON_DFLT

 

Record inserted successfully.

SET ANSI_NULL_DFLT_OFF ON; 

-- SET ANSI_NULL_DFLT_OFF to ON and create table ANSI_NULL_DEFAULT_ON_DFLT.  SET ANSI_NULL_DFLT_OFF ON;  GO  CREATE TABLE ANSI_NULL_DEFAULT_ON_DFLT (nm Varchar(50) null);  GO  INSERT INTO ANSI_NULL_DEFAULT_ON_DFLT (nm) VALUES (NULL);  GO    select * from ANSI_NULL_DEFAULT_ON_DFLT

      
If we SET ANSI_NULL_DFLT_OFF OFF; 

-- Set the 'ANSI null default' database option to false. 

ALTER DATABASE test_DB SET ANSI_NULL_DEFAULT OFF; 

SET ANSI_NULL_DFLT_OFF ON;  GO  CREATE TABLE ANSI_NULL_DEFAULT_ON_DFLT (nm Varchar(50));  GO   INSERT INTO ANSI_NULL_DEFAULT_ON_DFLT (nm) VALUES (NULL);  GO    select * from ANSI_NULL_DEFAULT_ON_DFLT

Now we are setting the value as OFF.

SET ANSI_NULL_DFLT_OFF OFF;  GO  CREATE TABLE ANSI_NULL_DEFAULT_ON_DFLT (nm Varchar(50));  GO   INSERT INTO ANSI_NULL_DEFAULT_ON_DFLT (nm) VALUES (NULL);  GO    select * from ANSI_NULL_DEFAULT_ON_DFLT

If set ANSI_NULL_DEFAULT OFF on database level and session level if we set ON or OFF, we and we are not explicitly providing null or not null other wise if we insert null vale on the column, it will throw an error.