Microsoft® SQL Server® 2012 Bible

517

Chapter 19: Confi guring SQL Server

19

If the ANSI_NULL_DEFAULT option is not set at the database level, you can also set the

nullability of new columns using the SET ANSI_NULL_DFLT_ON and SET ANSI_NULL_

DFLT_OFF commands. SET ANSI_NULL_DFLT_ON can enable null values at the connec-

tion level:

SET ANSI_NULL_DFLT_ON ON;

SET ANSI_NULL_DFLT_OFF can be set to not enable null values at the connection level:

SET ANSI_NULL_DFLT_OFF ON;

To enable ANSI_NULL_DFLT_ON at the server level in code, do the following:

EXEC sp_configure 'user options', 1024;

RECONFIGURE;

To enable ANSI_NULL_DFLT_OFF at the server level in code, do the following:

EXEC sp_configure 'user options', 2048;

RECONFIGURE;

Both SET ANSI_NULL_DFLT_ON and SET ANSI_NULL_DFLT_OFF commands cannot be set to ON at the

same time. Either one can be ON and the other can be OFF or both can be OFF.

ANSI NULLs

The ANSI_NULLS connection setting can determine comparison evaluations. When set to

ON, all comparisons to a null value evaluate to UNKNOWN. When set to OFF, the comparison

to a null value evaluates to true if both values are NULL. The default database setting for

ANSI_NULLS is OFF.

To enable ANSI_NULLS in code at the connection level, do the following:

SET ANSI_NULLS ON;

If SET ANSI_NULLS is not specifi ed, then the settings of ANSI_NULLS of the current

database apply. To enable ANSI_NULLS for the AdventureWorks2012 sample database in

code, do the following:

ALTER DATABASE AdventureWorks2012 SET ANSI_NULLS ON;

To enable ANSI_NULLS at the server level in code, do the following:

EXEC sp_configure 'user options', 32;

RECONFIGURE;

The ANSI_NULLS option is deprecated and will always be ON in a future version of SQL Server.

c19.indd 517c19.indd 517 7/30/2012 5:43:10 PM7/30/2012 5:43:10 PM

http://www.it-ebooks.info