ANSI NULLs set at table level?

2 May 2005 7:21 PM

DWalker

SQL 2000:

Ack! I thought my database had ANSI NULLS set to On. I find that it
doesn't. I always use IS NULL to check for the existence of nulls,
rather than using = NULL, so this setting might not matter much.

I see there is a database-wide setting for ANSI NULLS. Also, there
appears to be a setting per each table. I think.

If I modify some tables and ask to see the change script, Enterprise
Manager tells me that the table was created with ANSI NULLS set to Off,
and it will be re-created with ANSI NULLS set to ON.

For other tables, I don't get this warning.

-- Is the ANSI NULLS setting saved per table (or view)? If so, how can
I check this setting per table and change it?

Lots of the info in BOL uses the phrase "When SET ANSI_NULLS is ON," as
in the topic ANSI Comparison Options which says "When SET ANSI_NULLS is
ON, all comparisons against a null value evaluate to UNKNOWN." It
doesn't say whether this means the database-wide setting or the table
setting (assuming there is one.)

What if the database is set to ANSI NULLS ON and you're comparing two
tables, one of which was created with ANSI NULLS ON and one was created
with ANSI NULLS OFF? Or what if the database option is set one way, and
TableA is set another way, and you select from a field in that table
using WHERE FieldA = Null? Which ANSI NULLS setting is used? Where is
this *documented*?

I also see that BOL says "For stored procedures, SQL Server uses the SET
ANSI_NULLS setting value from the initial creation time of the stored
procedure." but it doesn't say anything about tables or views.

I thought I knew all about nulls, but now I'm confused about how and
where SQL server keeps track of the ANSI NULLS setting.

-- Related question: If I right-click a view or a proc in Enterprise
Manager, select Copy, then switch to Notepad and select Edit/Paste, I
get the view definition wrapped in SET QUOTED_IDENTIFIER (ON/OFF) and
SET ANSI_NULLS (ON/OFF) statements. The settings are different for
different views and procs. I don't think the database settings were
different when I created these views and procs, 'cause I'm not ever
switching the database-wide settings for these. What's up with that?

-- How can I get everything set to ANSI NULLS ON?

Thanks for any enlightenment.

David Walker

2 May 2005 7:57 PM

Tibor Karaszi

There's no table level setting for ANSI_NULLS. As for stored procedures, they inherit the setting
from create time. You can check current setting using the OBJECTPROPERTY function. Be aware that the
database level setting is essentially useless and a connection level setting overrides the database
level setting. And most modern API's will issue a connection level ANSI_NULLS ON whether you like it
or not. A client app, can of course have some config file where you specify the connection level
setting. QA is such an app.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quoteHide quote

"DWalker" <n***@none.com> wrote in message news:%23medfw0TFHA.4056@TK2MSFTNGP15.phx.gbl...
> SQL 2000:
>
> Ack! I thought my database had ANSI NULLS set to On. I find that it
> doesn't. I always use IS NULL to check for the existence of nulls,
> rather than using = NULL, so this setting might not matter much.
>
> I see there is a database-wide setting for ANSI NULLS. Also, there
> appears to be a setting per each table. I think.
>
> If I modify some tables and ask to see the change script, Enterprise
> Manager tells me that the table was created with ANSI NULLS set to Off,
> and it will be re-created with ANSI NULLS set to ON.
>
> For other tables, I don't get this warning.
>
> -- Is the ANSI NULLS setting saved per table (or view)? If so, how can
> I check this setting per table and change it?
>
> Lots of the info in BOL uses the phrase "When SET ANSI_NULLS is ON," as
> in the topic ANSI Comparison Options which says "When SET ANSI_NULLS is
> ON, all comparisons against a null value evaluate to UNKNOWN." It
> doesn't say whether this means the database-wide setting or the table
> setting (assuming there is one.)
>
> What if the database is set to ANSI NULLS ON and you're comparing two
> tables, one of which was created with ANSI NULLS ON and one was created
> with ANSI NULLS OFF? Or what if the database option is set one way, and
> TableA is set another way, and you select from a field in that table
> using WHERE FieldA = Null? Which ANSI NULLS setting is used? Where is
> this *documented*?
>
> I also see that BOL says "For stored procedures, SQL Server uses the SET
> ANSI_NULLS setting value from the initial creation time of the stored
> procedure." but it doesn't say anything about tables or views.
>
> I thought I knew all about nulls, but now I'm confused about how and
> where SQL server keeps track of the ANSI NULLS setting.
>
> -- Related question: If I right-click a view or a proc in Enterprise
> Manager, select Copy, then switch to Notepad and select Edit/Paste, I
> get the view definition wrapped in SET QUOTED_IDENTIFIER (ON/OFF) and
> SET ANSI_NULLS (ON/OFF) statements. The settings are different for
> different views and procs. I don't think the database settings were
> different when I created these views and procs, 'cause I'm not ever
> switching the database-wide settings for these. What's up with that?
>
> -- How can I get everything set to ANSI NULLS ON?
>
> Thanks for any enlightenment.
>
> David Walker

2 May 2005 9:03 PM

DWalker

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote
in news:uXjxbE1TFHA.1404@TK2MSFTNGP09.phx.gbl:

> There's no table level setting for ANSI_NULLS. As for stored
> procedures, they inherit the setting from create time. You can check
> current setting using the OBJECTPROPERTY function. Be aware that the
> database level setting is essentially useless and a connection level
> setting overrides the database level setting. And most modern API's
> will issue a connection level ANSI_NULLS ON whether you like it or
> not. A client app, can of course have some config file where you
> specify the connection level setting. QA is such an app.
>

OK, knowing that the database level setting is essentially useless and the
connection level setting is important, is valuable information to me. Does
Enterprise Manager also use a connection to the database, which means that
there's really no way to "get" to the database without using a connection
of some sort anyway? So the database level setting would be 100%
completely useless since everyone who uses the database has to connect to
it. Strange.

My database has ANSI_NULLS set OFF. If I go into *certain* tables in
Enterprise Manager (EM), and ask to Design the table, and move (reposition)
an existing field to come before another existing field, then click on the
"Save Change Script" icon, I get this message:

"- Warning: The table was created with ANSI_NULLS 'off' and will be re-
created with ANSI_NULLS 'on'."

In this scenario, EM creates a big script that makes a temp table with the
fields in the new requested order, then copies the data, then deletes the
old table, then renames the temp one.

BUT, this warning doesn't occur if I do the same thing to *other* tables,
it just occurs if I do this procedure on the tables that were originally
created inside a stored proc.

So it LOOKS like SQL knows that ANSI_NULLS were on or off when each *table*
was created. If that's not the case, can you please explain why I get this
message when I move fields around in some tables but not others. (I'm not
really trying to move fields around in a table, I just know that this is
one way to get EM to make a script that recreates the table. And it gives
me the warning on some tables and not on others.)

Thanks.

David Walker

3 Jun 2005 3:41 AM

Tim S

OBJECTPROPERTY IsAnsiNullsOn applys to
Scalar and Inline Table-valued Function, Procedure, Table, Trigger, View
Specifies that the ANSI NULLS option setting for the table is ON,
meaning all comparisons against a null value evaluate to UNKNOWN.
This setting applies to all expressions in the table definition,
including computed columns and constraints, for as long as the table exists.

Tim S