THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

The Most Natural Set Option


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

 

Oh well.....

Published Wednesday, December 05, 2007 2:35 PM by Denis Gobo
Filed under:

Comment Notification

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 RSS

Comments

 

Kalen Delaney said:

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.

~Kalen

December 5, 2007 1:49 PM
 

Denis Gobo said:

I see, it depends on the ANSI null default option for the database, if this is set to true or false

Thanks

December 5, 2007 1:54 PM
 

AaronBertrand said:

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?

December 5, 2007 4:44 PM
 

Kalen Delaney said:

Probably because this option has been around since the days of 8.3 filenames, and we need them to be backward compatable. ;-)

December 5, 2007 6:21 PM
 

JD said:

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

SET ANSI_NULL_DFLT_ON ON

SET ANSI_NULL_DFLT_OFF ON

DECLARE @options INT

SELECT @options = @@OPTIONS

PRINT @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 )

GO

August 13, 2013 10:37 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement