I tried this experiment:
The database setting for ANSI_NULL is OFF. I set up two columns in a table
with null values. The following code returns my table:
SET ANSI_NULLS OFF
SELECT * FROM Exam WHERE TestNull1 = NULL
And this code does not:
SET ANSI_NULLS ON
SELECT * FROM Exam WHERE TestNull1 = NULL
But this codes won't return my table no matter what the ANSI_NULLS setting:
SELECT * FROM Exam WHERE TestNull1 = TestNull2
First question: Why doesn't the database setting determine the behavior in
the first two samples, instead of the setting I'm making in the query window?
Second question: Since both the fields are null, why aren't they considered
equal in the third sample? The documentation says:
"When OFF is specified, comparisons of non-UNICODE values to a null value
evaluate to TRUE if both values are NULL."
I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
letters, numbers, and symbols that SQL Server recognizes in the nchar,
nvarchar, and ntext data types." And I thought I got around that my defining
the two columns as char(10).Bev Kaufman,
I would suggest to move on and forget about setting it to OFF, and keep it
always set to ON. You should use IS [NOT] NULL instead, if you want you are
script to work as intended.
AMB
"Bev Kaufman" wrote:
> I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query window?
> Second question: Since both the fields are null, why aren't they considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my defining
> the two columns as char(10).
>|||In real life situations, I would always use IS [NOT] NULL. I'm just trying
to understand the database option settings, and I am puzzled when the setting
in the database properties has no effect on the behavior.
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||According to BOL, SET ANSI_NULLS OFF is deprecated and should be avoided.
The SET ANSI_NULLS OFF option is more or less a backwards-compatibility
feature going way back, and was probably originally designed for programmers
coming from other languages who couldn't understand the concept that NULL is
not equal to NULL.
NULLs are not equal to any other value, including other NULLs. Don't try to
use them in equality or comparison expressions; instead use IS NULL and IS
NOT NULL as Alejandro suggested. This will make your code that much easier
to upgrade to later versions of SQL Server, and easier for other developers
to maintain since they'll be able to reference the standard behavior of
NULLs in your code.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C1B0E1B0-5D1E-4D5F-BA61-5A57BFCA0528@.microsoft.com...
>I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a
> table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS
> setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query
> window?
> Second question: Since both the fields are null, why aren't they
> considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my
> defining
> the two columns as char(10).
>|||Many of the SET options are set on a connection-wide basis, so if your
connection specifies a different value for the setting or you set the value
differently during your connection, you will not override the default
database setting for that connection. BOL tells you which settings are
server-wide, database-wide or connection-wide.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Ooops, should read "you will override the default database setting for that
connection."
"Mike C#" <xyz@.xyz.com> wrote in message
news:eTHgtnmHIHA.280@.TK2MSFTNGP03.phx.gbl...
> Many of the SET options are set on a connection-wide basis, so if your
> connection specifies a different value for the setting or you set the
> value differently during your connection, you will not override the
> default database setting for that connection. BOL tells you which
> settings are server-wide, database-wide or connection-wide.
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
>> In real life situations, I would always use IS [NOT] NULL. I'm just
>> trying
>> to understand the database option settings, and I am puzzled when the
>> setting
>> in the database properties has no effect on the behavior.
>> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the
>> > behavior in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >
>|||Bev,
For the most part you should ignore the database settings for query options.
SET options for a connection override the database options, and almost all
client interfaces set values for most of the query options including
ANSI_NULLS. So it doesn't matter what you set it to at the db level, as soon
as you open a connection with Query Analyzer or Management Studio, your
connection will set its own setting. I wrote an article about this several
years ago for SQL Server Magazine. It's very confusing to someone just
reading about database options.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Well, I think there is some inconsistency in the T-SQL behavior as Bev
described regardless what the best practices may be. Note that the ANSI_NULLS
seeting was not set by Query Analyzer or its driver, but set in the script
immediately before each SELECT.
I don't know whether this inconsistency is a feature by design or not. But
it appears to be a bug to me.
Linchi
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||From BOL:
"SET ANSI_NULLS ON affects a comparison only if one of the operands of the
comparison is either a variable that is NULL or a literal NULL. If both
sides of the comparison are columns or compound expressions, the setting
does not affect the comparison."
http://msdn2.microsoft.com/en-us/library/ms188048.aspx
Whether or not SET ANSI_NULLS OFF is inconsistent in its behavior is
probably a moot point, since BOL also says this:
"This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature."
Don't use SET ANSI_NULLS OFF, and inconsistent (though documented) behavior
won't be an issue.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:08DB5BC1-4051-4B12-84B4-0A5D3F6A9634@.microsoft.com...
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the
> ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Linchi Shea,
As Mike stated, the comparison should be against the literal NULL. Setting
ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
about it, instead trying to understand it. Microsoft is thinking in
deprecating most of those settings, and I guess the future behavior will be
like having them ON.
NULL puzzle by Steve Kass
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
AMB
"Linchi Shea" wrote:
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
> > Bev Kaufman,
> >
> > I would suggest to move on and forget about setting it to OFF, and keep it
> > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > script to work as intended.
> >
> >
> > AMB
> >
> > "Bev Kaufman" wrote:
> >
> > > I tried this experiment:
> > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > with null values. The following code returns my table:
> > > SET ANSI_NULLS OFF
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > And this code does not:
> > > SET ANSI_NULLS ON
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > >
> > > First question: Why doesn't the database setting determine the behavior in
> > > the first two samples, instead of the setting I'm making in the query window?
> > >
> > > Second question: Since both the fields are null, why aren't they considered
> > > equal in the third sample? The documentation says:
> > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > evaluate to TRUE if both values are NULL."
> > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > the two columns as char(10).
> > >|||It's good that at least it's documented in BOL. I missed it. Any clue why
comparing two columns is treated differently?
Linchi
"Alejandro Mesa" wrote:
> Linchi Shea,
> As Mike stated, the comparison should be against the literal NULL. Setting
> ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
> about it, instead trying to understand it. Microsoft is thinking in
> deprecating most of those settings, and I guess the future behavior will be
> like having them ON.
> NULL puzzle by Steve Kass
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
> AMB
> "Linchi Shea" wrote:
> > Well, I think there is some inconsistency in the T-SQL behavior as Bev
> > described regardless what the best practices may be. Note that the ANSI_NULLS
> > seeting was not set by Query Analyzer or its driver, but set in the script
> > immediately before each SELECT.
> >
> > I don't know whether this inconsistency is a feature by design or not. But
> > it appears to be a bug to me.
> >
> > Linchi
> >
> > "Alejandro Mesa" wrote:
> >
> > > Bev Kaufman,
> > >
> > > I would suggest to move on and forget about setting it to OFF, and keep it
> > > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > > script to work as intended.
> > >
> > >
> > > AMB
> > >
> > > "Bev Kaufman" wrote:
> > >
> > > > I tried this experiment:
> > > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > > with null values. The following code returns my table:
> > > > SET ANSI_NULLS OFF
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > And this code does not:
> > > > SET ANSI_NULLS ON
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > > >
> > > > First question: Why doesn't the database setting determine the behavior in
> > > > the first two samples, instead of the setting I'm making in the query window?
> > > >
> > > > Second question: Since both the fields are null, why aren't they considered
> > > > equal in the third sample? The documentation says:
> > > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > > evaluate to TRUE if both values are NULL."
> > > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > > the two columns as char(10).
> > > >
没有评论:
发表评论