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
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 WalkerThere's no table level setting for ANSI_NULLS. As for stored procedures, the
y inherit the setting
from create time. You can check current setting using the OBJECTPROPERTY fun
ction. 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_NULL
S 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/
"DWalker" <none@.none.com> wrote in message news:%23medfw0TFHA.4056@.TK2MSFTNGP15.phx.gbl...[
color=darkred]
> 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
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[/color]|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.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|||> 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?
Correct. EM uses ODBC.
> So the database level setting would be 100%
> completely useless since everyone who uses the database has to connect to
> it.
Well, not all API's will set this setting automaticallue for you. The stone-
age DbLibrary does not.
So the database level settings can be important for for DbLibrary applicatio
ns. Here's a quote from
Books Online:
"The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server aut
omatically set
ANSI_NULLS to ON when connecting. This setting can be configured in ODBC dat
a sources, in ODBC
connection attributes, or in OLE DB connection properties that are set in th
e application before
connecting to SQL Server. SET ANSI_NULLS defaults to OFF for connections fro
m DB-Library
applications."
I have not tried ADO.NET...
As for EM stating the message about ANSI NULLs for the table, that surpised
me. Yes, I can reproduce
it. Turns out this can be relevant for expressions for computed columns and
constraints. Read about
it in BOL, OBJECTPROPERTY, the 'IsAnsiNullsOn' option. You can check ANSI_NU
LLS setting with below:
select objectproperty(id, 'IsAnsiNullsOn'), name
from sysobjects
where type = 'u'
order by name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DWalker" <none@.none.com> wrote in message news:eUQv2p1TFHA.580@.TK2MSFTNGP15.phx.gbl...[col
or=darkred]
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:uXjxbE1TFHA.1404@.TK2MSFTNGP09.phx.gbl:
>
> OK, knowing that the database level setting is essentially useless and the
> connection level setting is important, is valuable information to me. Doe
s
> 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 thi
s
> 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
>[/color]|||Thanks. See below.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:#CHBHW7TFHA.2556@.TK2MSFTNGP12.phx.gbl:
> Correct. EM uses ODBC.
>
> Well, not all API's will set this setting automaticallue for you. The
> stone-age DbLibrary does not. So the database level settings can be
> important for for DbLibrary applications. Here's a quote from Books
> Online:
[...]
I see, not every library sets ANSI NULLS. But we have to assume that we
don't know how ANSI NULLS are set when we run a view that's stored in
the database, because it depends on how we're connecting. So we can't
use the semantics that ANSI NULLS OFF would provide. Not that I would
want to anyway.
> As for EM stating the message about ANSI NULLs for the table, that
> surpised me. Yes, I can reproduce it. Turns out this can be relevant
> for expressions for computed columns and constraints. Read about it in
> BOL, OBJECTPROPERTY, the 'IsAnsiNullsOn' option. You can check
> ANSI_NULLS setting with below:
> select objectproperty(id, 'IsAnsiNullsOn'), name
> from sysobjects
> where type = 'u'
> order by name
>
I'm glad I was able to surprise you. It sure surprised me to see that
message. Thanks for the pointer to that piece of BOL (it says that
IsAnsiNullsOn applies to: Function, Procedure, Table, Trigger, View).
The other parts of BOL (SET ANSI NULLS) sure don't tell you that it can
be saved at the table level.
Thanks so much for the info, I appreciate it. Good thing I'm not the
only one surprised by this table setting.
David|||Tibor:
BOL says "For stored procedures, SQL Server uses the SET ANSI_NULLS setting
value from the initial creation time of the stored procedure."
I made a test database, and I can't create a stored proc from Enterprise
Manager that has ANSI NULLS set to On! No matter what I do. The database
default setting doesn't seem to control it.
David Walker|||Skip EM for these things. Really. QA is a much better tool for writing datab
ase objects, and you're
not left out to some behavior in EM. Control!
Seems like EM does it this way (overrides the connection setting) and we don
't have control over
this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DWalker" <none@.none.com> wrote in message news:%23GaE5nAUFHA.2304@.tk2msftngp13.phx.gbl...[
color=darkred]
> Tibor:
> BOL says "For stored procedures, SQL Server uses the SET ANSI_NULLS settin
g
> value from the initial creation time of the stored procedure."
> I made a test database, and I can't create a stored proc from Enterprise
> Manager that has ANSI NULLS set to On! No matter what I do. The database
> default setting doesn't seem to control it.
> David Walker
>
>[/color]|||I agree with Tobor, EM shouldnt be your power tool for control over the
database. If you know what to do in commandline or QA you will be able to
reproduce it even you got a "naked" MSDE without any (richful) GUI.
The creating or altering of a stored procedure is also a know issue to me,
it roubled me some time ago while creating queries against a herterogenous
datasource. After ALtering the Proc in the Enterprse Manager it didnt work
saying something that the ANSI NULL value wasnt set properly. With QA this
didnt happen at all.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DWalker" <none@.none.com> schrieb im Newsbeitrag
news:%23GaE5nAUFHA.2304@.tk2msftngp13.phx.gbl...
> Tibor:
> BOL says "For stored procedures, SQL Server uses the SET ANSI_NULLS
> setting
> value from the initial creation time of the stored procedure."
> I made a test database, and I can't create a stored proc from Enterprise
> Manager that has ANSI NULLS set to On! No matter what I do. The database
> default setting doesn't seem to control it.
> David Walker
>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:eh3tgJBUFHA.2128@.TK2MSFTNGP15.phx.gbl:
> Skip EM for these things. Really. QA is a much better tool for writing
> database objects, and you're not left out to some behavior in EM.
> Control! Seems like EM does it this way (overrides the connection
> setting) and we don't have control over this...
>
OK, thanks.
David|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:eh3tgJBUFHA.2128@.TK2MSFTNGP15.phx.gbl:
> Skip EM for these things. Really. QA is a much better tool for writing
> database objects, and you're not left out to some behavior in EM.
> Control! Seems like EM does it this way (overrides the connection
> setting) and we don't have control over this...
>
... Except EM is sometimes easier to create views, especially with 3 or
4 joins, although I always check the generated SQL to make sure it's
right.
But it's interesting that EM won't let you create a proc with ANSI NULLS
ON. Ugh.
David
(OBJECT:([PROD_GART_S40M0T0ODB].[dbo].[instrument].[pk_instrument_instrument_id]),