If I execute like this
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000Since you know that you are setting jml to 5, why can't you use 5 instead of
jml while setting the value for total?
"Use total=5*5000" instead of "total=jml*5000"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
If I execute like this
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000|||In the query below, [total] will be multiplied by the original value of
[jml], not the new value of 5. One option is to set a variable to the value
of 5. For example:
declare @.x as int
set @.x = 5
update tcoba set jml=@.x,total=@.x*5000
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
> If I execute like this
> update tcoba
> set jml=5,total=jml*5000
> So value of field "total" is null. In fact I wish to be its value become
> 25000.
> may be, Any there nicer sentence to solve problem? If can, just one
> statement. But not like it:
> update tcoba
> set jml=5,total=5*5000
>|||the result you are getting in your column "Total" is because your
column contains NULL initially, and though you have placed jml for
updation before the total, it won't actually update jml first and
update Total after it. But it is good example to know how Update query
works!|||The UPDATE assigns a whole row at a time; it does not work left to
right like a procedural language. Google some of my old posting about
the semantics of UPDATE.
2012年3月11日星期日
2012年3月6日星期二
Any quick way to find middle value out of 3?
Please help
declare @.t table (id1 int not null, id2 int not null, id3 int null)
insert @.t values (1,2,null) -- middle null
insert @.t values (10,20,1) -- middle 10
insert @.t values (11,23,12) -- middle 12
insert @.t values (20,100,123) -- middle 100
select *, middle(ID1,id2,id3) --< middle out of 3
FROM @.t
Thank you!There are probably better ways, but this should work.
You could do the same thing using BETWEEN but I prefer to avoid using
BETWEEN.
I assumed that the values could be the same between 2 or 3 of the columns
SELECT ID1, ID2, ID3,
CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
ELSE ID3 END as Middle
FROM @.t
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Farmer" wrote:
> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>
>|||One additional note, this would get a bit uglier if id1 or id2 could also be
null. I was able to cheat a bit since only id3 was allowed to be null, and
you seemed to want the null value for your middle value.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Ryan Powers" wrote:
> There are probably better ways, but this should work.
> You could do the same thing using BETWEEN but I prefer to avoid using
> BETWEEN.
> I assumed that the values could be the same between 2 or 3 of the columns
> SELECT ID1, ID2, ID3,
> CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
> WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
> WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
> WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
> ELSE ID3 END as Middle
> FROM @.t
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Farmer" wrote:
>|||using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Middle(SqlInt32 i1, SqlInt32 i2, SqlInt32 i3)
{
if (i1 < i2)
{
if (i3 < i1)
return i1;
if (i3 > i2)
return i2;
return i3;
}
if (i1 > i2)
{
if (i3 > i1)
return i1;
if (i3 < i2)
return i2;
return i3;
}
if (i3 <= i1)
return i1;
return i2;
}
};
William Stacey [MVP]
"Farmer" <someone@.somewhere.com> wrote in message
news:eFURSHkFGHA.1816@.TK2MSFTNGP11.phx.gbl...
> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>
declare @.t table (id1 int not null, id2 int not null, id3 int null)
insert @.t values (1,2,null) -- middle null
insert @.t values (10,20,1) -- middle 10
insert @.t values (11,23,12) -- middle 12
insert @.t values (20,100,123) -- middle 100
select *, middle(ID1,id2,id3) --< middle out of 3
FROM @.t
Thank you!There are probably better ways, but this should work.
You could do the same thing using BETWEEN but I prefer to avoid using
BETWEEN.
I assumed that the values could be the same between 2 or 3 of the columns
SELECT ID1, ID2, ID3,
CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
ELSE ID3 END as Middle
FROM @.t
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Farmer" wrote:
> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>
>|||One additional note, this would get a bit uglier if id1 or id2 could also be
null. I was able to cheat a bit since only id3 was allowed to be null, and
you seemed to want the null value for your middle value.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Ryan Powers" wrote:
> There are probably better ways, but this should work.
> You could do the same thing using BETWEEN but I prefer to avoid using
> BETWEEN.
> I assumed that the values could be the same between 2 or 3 of the columns
> SELECT ID1, ID2, ID3,
> CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
> WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
> WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
> WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
> ELSE ID3 END as Middle
> FROM @.t
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Farmer" wrote:
>|||using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Middle(SqlInt32 i1, SqlInt32 i2, SqlInt32 i3)
{
if (i1 < i2)
{
if (i3 < i1)
return i1;
if (i3 > i2)
return i2;
return i3;
}
if (i1 > i2)
{
if (i3 > i1)
return i1;
if (i3 < i2)
return i2;
return i3;
}
if (i3 <= i1)
return i1;
return i2;
}
};
William Stacey [MVP]
"Farmer" <someone@.somewhere.com> wrote in message
news:eFURSHkFGHA.1816@.TK2MSFTNGP11.phx.gbl...
> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>
2012年2月23日星期四
Any improvements to this: Cannot apply value null to property Login: Value cannot be null.
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
Jayaram Krishnaswamy wrote:
Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.
Perhaps if you explained what on earth you're talking about someone could help. Explaining how you got this error would be a good place to start.
The software is 16 months old by the way.
-Jamie
2012年2月11日星期六
ANSII NULL AND QUOTED IDENTIFERS - Upgrade problems
I inherited a system that was started in Access and moved to SQL 2000. The business has grown and we are trying to replace our older systems with ASP.NET and Server 2005.
Currently, we are trying to make a new asp.net page for searching the database for records with matching dates or date ranges. There are several types of dates to search, so they are all optional. Set to default as null in the proc. For each date there is an operator field, such as equal or greater, etc. The proc only looks at the date if the operator is set to EQ" or "IN" and ignores the date if operator set to "NO"
The proc works fine when running under Management Studio, but fails coming through a SQLDataSource to a gridview. It works with integer and string filters, but fails when entering the same date ('07/20/2007') that works in the testing tool. All dates are actually stored as datetime, and they are set as DateTime Control Parameters in the SQLDataSource.
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:someConnectionString %>"
SelectCommand="spTESTSearch"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ParameterDefaultValue="M"Name="TypeCode"Type="String"/>
<asp:ParameterDefaultValue="EQ"Name="FirstPubOp"Type="String"/>
<asp:ControlParameterControlID="FirstPubDateTextBox"DefaultValue=""Name="FirstPubDate"
PropertyName="Text"Type="DateTime"/>
<asp:ParameterDefaultValue="C"Name="UserName"Type="String"/>
<asp:ParameterDefaultValue="NO"Name="SearchTextOp"Type="String"/>
<asp:ParameterName="SearchText"Type="String"/>
</SelectParameters>
</asp:SqlDataSource>The dates are selected properly in the testing tool, with code such as :
DateDiff(day, FirstPubDate, @.FirstPubDate)= 0
I think my problem is based on option settings for the databases themselves. The old database was set to Ansii Nulls and Quoted Identiers to OFF, and the new ones were defaulted to them being ON. I noticed that the tool also, sets those options on when creating new stored procedures.
Would this difference be causing the dates to be quoted and viewed as objects rather than strings? What are the dangers in changing those options on the database that still gets uploads from the old SQL 2000 database and some Mac-based systems?
I welcome any suggestions on how to get my new stuff running while not breaking my old production systems.
Thanks for the assist!
The options you mentioned should have no relevance to your current issue. Quoted identifiers is referring to whether a statement like:
SELECT "my_column" or SELECT [my_column] is the correct way to specify column/table names that have characters in them that could cause confusion, or are reserved words.
The ANSI NULLS parameter refers to a number of things like if the boolean operation (NULL=NULL) should return true, or NULL.
BTW, the default values on the stored procedure (as specified in the stored procedure) aren't used. They only come into play when you call a stored procedure without specifying a value for that parameter. The parameters you have defined in the SqlDatasource will always pass a value.
Personally, I would first hook up an event to the SqlDatasource_Selecting event. By checking the e.Command.Parameters collection in that event, you can see what it about to be passed to the stored procedure. Verify that passing the values (as they were in the selecting event) works in Management Studio.
If that does not lead you to the problem, then run Sql Profiler, and compare the difference between the queries that get submitted via the web application, and the query submitted by management studio.
ANSI_NULLS and null comparison
Hi All,
I'm converting some stored procs from Sybase to SQL Server, and I'm
having serious problems with Null comparisons.
When I run the code below on Sybase, it returns both rows. When I run
it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
the code (and on the session through Query Analyzer), but it doesn't
seem to make any difference. Am I missing something?
-----
set ANSI_NULLS off
drop table #TestNull
create table #TestNull (Field1 varchar(10), Field2 varchar(10))
insert into #TestNull values (1, null)
insert into #TestNull values (1,1)
declare @.TestVar varchar(10)
select @.TestVar = Null
select * from #TestNull where Field1 = @.TestVar
-----
Thanks in advance,
SaulYour SQL Server select should look like this:
SELECT * FROM #TextNull WHERE Field1 IS NULL
CJ
"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul|||"Saul Margolis" <saul_margolis@.hotmail.com> wrote in message
news:c20aa081.0402171416.744303af@.posting.google.c om...
> Hi All,
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
>
> -----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
>
> Thanks in advance,
> Saul
I guess you mean WHERE Field2 = @.TestVar ? That will return the (1, NULL)
row.
See SET ANSI_NULLS in Books Online - there are a number of examples. By the
way, you should generally say which version of MSSQL you're using, as some
behaviour may vary - see the comments in Books Online.
Simon|||Saul Margolis (saul_margolis@.hotmail.com) writes:
> I'm converting some stored procs from Sybase to SQL Server, and I'm
> having serious problems with Null comparisons.
> When I run the code below on Sybase, it returns both rows. When I run
> it on MS SQL Server, it returns nothing. I have set ANSI_NULLS off in
> the code (and on the session through Query Analyzer), but it doesn't
> seem to make any difference. Am I missing something?
-----
> set ANSI_NULLS off
> drop table #TestNull
> create table #TestNull (Field1 varchar(10), Field2 varchar(10))
> insert into #TestNull values (1, null)
> insert into #TestNull values (1,1)
> declare @.TestVar varchar(10)
> select @.TestVar = Null
> select * from #TestNull where Field1 = @.TestVar
> -----
I fail to see how the batch above could return any rows with any
setting. Looks like there is a typo in your repro.
Anyway, first of all, try to write your code so that you can use
ANSI_NULLS ON. There are features in MS SQL Server that requires
ANSI_NULLS to be ON:
o Indexed views.
o Index on computed columns.
o Access to linked servers.
With ANSI_NULLS off, these features are unavailable.
Also, be aware of when writing stored procedures, that the setting
of ANSI_NULLS when you create the procedure applies. That is, not
the run-time setting.
Also, you may need to have a go for ANSI_NULLS to have effect.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The Sybase family used to allow "foobar = NULL" to mean "foobar IS
NULL"; you just need to set everything to ANSI and change all your code.
Also, why are you putting integers into VARCHAR(10) columns in your
sample code?
Now you know why experienced progrmamers laugh at newbies who use
proprietary code because they think they will never have to port it :)
That is probably not funny right now; and it might get worse if the
Sybase is old -- look for *= as the outer join and other non-standard
behavior in some of the predicates.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
2012年2月9日星期四
ANSI_NULL setting and null comparisons
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).
> > > >
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).
> > > >
ANSI NULLs set at table level?
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
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 andwhere 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
ANSI NULL DEFAULT database option
Hi,
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 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.
Thanks in Advance.
Om PrakashOm (bubu.prakash@.gmail.com) writes:
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
订阅：
博文 (Atom)