Posted by Erland Sommarskog on 11/13/07 22:15

Om (bubu.prakash@gmail.com) writes:
> I am using SQL Server 2005 32 bit version. Could any one clarify me
> the caption database option with some examples? I am quite confused
> with the explanation given in MSDN. Kindly help me.

I'm a little uncertain on what options you are asking about. Is it
only the ANSI NULL DEFAULT option, or is it the ANSI options in general
or options in general.

I'll expand this to the ANSI options in general, since this is a quite con-
fusing topic, and if you did not get it from MSDN one can hardly blame you.
The good news is that those settings have little importance in practice.


There are several ways to turn on the ANSI options:
o Set by default on connection with most APIs.
o Explicit SET command.
o Database option.
o The server-wide configuration option "user options".

As long as there is no explicit SET OFF command, an option is ON if any of
the four says so. And since SQL Server sets these options ON with most
APIs, if follows that these options only matters if you use API that
does not. And the only API I can think of here is DB-Library. Possibly
very old ODBC drivers.

Note that some tools, Enterprise Manager and SQLCMD, runs with some of
these off, but they send an explicit SET command, so the database
setting does not matter.

Note also that ANSI_NULLS and QUOTED_IDENTIFIER are saved with the
procedure, so here what matters is the setting at create-time, not
at run time.


The particular option ANSI NULL DEFAULT controls what this means:

CREATE TABLE gogo (a int)

Is a nullable or not? With ANSI NULL DEFAULT on, the column is nullable,
with old-style SQL Server it is not. In this particular case, my
recommendation is that you always explicitly say NULL/NOT NULL.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]