THE SQL Server Blog Spot on the Web
SET ANSI_NULL_DFLT_OFF ON
this is what this means, in English....
Set the setting ANSI NULL Default Off to ON
Why not just SET ANSI_NULL_DFLT [ON|OFF]?
This is even worse than SET NOCOUNT ON
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
The reason is because it's not just a boolean, ON or OFF. Notice there is also a SET ANSI_NULL_DEFAULT_ON. Either setting can be ON or OFF. So there are four possible combinations.
By having both ANSI_NULL_DEFAULT_ON and ANSI_NULL_DEFAULT_OFF, we have fine-grained control with this option, much more than with other options. We can control the relationship between the database setting and the session setting much more than we can with any of the other options that exist at most levels.
I agree that it can be confusing, but just having one setting to make ON or OFF would not give us the same behavior.
I see, it depends on the ANSI null default option for the database, if this is set to true or false
My big issue here is, why not spell out the word? Is it really that much faster or more intuitive to type DFLT than DEFAULT?
Probably because this option has been around since the days of 8.3 filenames, and we need them to be backward compatable. ;-)
Just found this while I was looking for a reason for both I know it is an old post but it doesn't look like it supports four different configurations. It looks like turning one on turns the other off unless I am misunderstanding something? I am using the following query to set and check the various set options associated with a connection. The first two set options yield dflt_on on the second yield dflt_off on?
SET ANSI_NULL_DFLT_OFF ON
SET ANSI_NULL_DFLT_ON ON
DECLARE @options INT
SELECT @options = @@OPTIONS
SELECT 'DISABLE_DEF_CNST_CHK' WHERE ( (1 & @options) = 1 ) UNION
SELECT 'IMPLICIT_TRANSACTIONS' WHERE ( (2 & @options) = 2 ) UNION
SELECT 'CURSOR_CLOSE_ON_COMMIT' WHERE ( (4 & @options) = 4 ) UNION
SELECT 'ANSI_WARNINGS' WHERE ( (8 & @options) = 8 ) UNION
SELECT 'ANSI_PADDING' WHERE ( (16 & @options) = 16 ) UNION
SELECT 'ANSI_NULLS' WHERE ( (32 & @options) = 32 ) UNION
SELECT 'ARITHABORT' WHERE ( (64 & @options) = 64 ) UNION
SELECT 'ARITHIGNORE' WHERE ( (128 & @options) = 128 ) UNION
SELECT 'QUOTED_IDENTIFIER' WHERE ( (256 & @options) = 256 ) UNION
SELECT 'NOCOUNT' WHERE ( (512 & @options) = 512 ) UNION
SELECT 'ANSI_NULL_DFLT_ON' WHERE ( (1024 & @options) = 1024 ) UNION
SELECT 'ANSI_NULL_DFLT_OFF' WHERE ( (2048 & @options) = 2048 ) UNION
SELECT 'CONCAT_NULL_YIELDS_NULL' WHERE ( (4096 & @options) = 4096 ) UNION
SELECT 'NUMERIC_ROUNDABORT' WHERE ( (8192 & @options) = 8192 ) UNION
SELECT 'XACT_ABORT' WHERE ( (16384 & @options) = 16384 )