Friday, April 5, 2013

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.


image


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)

image


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.
image

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

No comments:

Post a Comment