any way to check if all the values in a column returned are the same value?

say i have a table, and in it are two columns, column1 and column2 and i do the following query:

SELECT column1, column2 FROM table WHERE column2 = '12345'

i want to check if column1 has all the same values, so in the first case, no

column1 column2
--- ---
4 12345
9 12345
5 12345

column1 column2
--- ---
9 12345
9 12345
9 12345

in the 2nd case, column1 contains all the same values, so yes

is there anyway i can check this? i would be doing this in a trigger.. say when a new row is inserted, the value of column1 is inserted, but col 2 is null.. so when they try to fill in the value for col2 of that row, the trigger checks to see if the value they put for col 2 is already in the table.. if it isn't, then everything is ok. but if it is already in teh table, then it checks col1 to see if all the values of col1 are the same

i hope this makes sense

thanksWithin your trigger, you may determine the min() and max() value of Column1 regarding a certain value for column 2:

SELECT min(Column1), max(column1)
Into Min1, Max1
FROM <YourTable>
WHERE Column2 = <Your current value>
GROUP BY Column2

If Min1=Max1, all values are the same.|||SELECT COUNT(NumValues) FROM (
SELECT COUNT(*) AS NumValues FROM MyTable
WHERE Column2 = MyValue
GROUP BY Column1) AS TempCount;

Any T-SQL command can show the description of specified table columns?

I want to ask something because i need.

Any SQL/T-SQL command inside MsSQL Server 2000 can show the description of {all table columns or specified table columns} of specified table inside specified database?

can you teach me how to do and any example(s)?Try checking out the INFORMATION_SCHEMA.COLUMNS view:

SELECT
 *
FROM
 INFORMATION_SCHEMA.COLUMNS
WHERE
 TABLE_CATALOG = 'myDatabase' AND
 TABLE_NAME = 'myTable'

Terri|||You can also use the following:

exec sp_columns 'table_name'

This will return the list of columns for the table you specified.

any suggestions for this report design

hi,
i need to design a report with the following requirements...
I need to have 5 static columns and i one dynamic column which increases
according to the user selection...
Can a matrix help here'?
I cant understand how to add static columns to a matrix?
Can any1 suggest me any way of doing it?
thanks,I have an example of static columns in a matrix on www.msbicentral.com
The name of it is Matrix.StaticColumns.RDl
Perhaps you could use that , and use the parameter in the SQL statement to
optionally return one column or another.. ie
select title, price, case @.parm when 1 then pub_id else total_sales end
from titles
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:457B9684-75D1-4E9F-9C0A-28459B5B319C@.microsoft.com...
> hi,
> i need to design a report with the following requirements...
> I need to have 5 static columns and i one dynamic column which increases
> according to the user selection...
> Can a matrix help here'?
> I cant understand how to add static columns to a matrix?
> Can any1 suggest me any way of doing it?
> thanks,
>

Any suggesstions please

I am passing a XML string from client, In the back end I
am verifying the data with constraints of the columns.
Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
select * into #tempApps
from openxml(@.hdoc, N'//application',2)
with AP_Applications --This is the physical table
EXEC sp_xml_removedocument @.hdoc
The system will create an ApplicationId at the time of
inserting a new record. Before inserting a record I am
checking the data.
When I run the above code I am getting the following
error.
Unexpected NULL value returned for
column '[OpenXML].AppId' from the OLE DB
provider 'OpenXML'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL
value returned for the column: ProviderName='OpenXML',
TableName='[OpenXML]', ColumnName='AppId'].
I will appreciate you can give some suggestions.
Thanks in Advance
You cannot use a table name with an identity column in the with clause. You
have to give the with clause (without the Identity column) explicit.
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:784a01c43121$a8d09420$a501280a@.phx.gbl...
>I am passing a XML string from client, In the back end I
> am verifying the data with constraints of the columns.
> Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
> select * into #tempApps
> from openxml(@.hdoc, N'//application',2)
> with AP_Applications --This is the physical table
>
>
> EXEC sp_xml_removedocument @.hdoc
>
> The system will create an ApplicationId at the time of
> inserting a new record. Before inserting a record I am
> checking the data.
> When I run the above code I am getting the following
> error.
> Unexpected NULL value returned for
> column '[OpenXML].AppId' from the OLE DB
> provider 'OpenXML'. This column cannot be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL
> value returned for the column: ProviderName='OpenXML',
> TableName='[OpenXML]', ColumnName='AppId'].
> I will appreciate you can give some suggestions.
>
> Thanks in Advance
>

Any Ideas? SQL teaser

A little SQL problem I'm having –
I want to update the MaxInd and MinInd columns of a table to indicate which
rows have minimum or maximum event date for a given URN.
It is important that only one record per URN be marked as the Maximum or
Minimum but it is possible that a single event may be both the maximum AND
the minimum
This problem is quite simple to solve if event dates are all unique;
problems start to occur when multiple minimum or maximum dates exist in this
list
Consider the table below which for the moment has no ‘problem’ entries
Tables
Event
ID URN EventDate MaxInd MinInd
-- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
Values for the MaxInd and MinInd columns where supplied using the following
update
UPDATE Event
SET
MaxInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END,
MinInd = CASE WHEN EventDate = Ranges.MaxDate THEN 1 ELSE 0 END
FROM (
SELECT MAX(EventDate) AS MaxDate, MIN(EventDate) AS MinDate, URN
FROM Event
GROUP BY URN
) AS Ranges
WHERE Event.URN = Ranges.URN
Now lets update the Event table to introduce some problem events and the
values for MaxInd and MinInd I would like –
ID URN EventDate MaxInd MinInd Notes
-- -- -- -- -- --
1 1 01/01/2006 N Y
2 1 03/03/2006 Y N
3 1 02/01/2006 N N
4 2 08/01/2006 Y N
5 2 03/01/2006 N N
6 3 08/01/2006 Y Y
7 1 01/01/2006 N N Same event date
as ID 1
8 2 08/01/2006 N N Same event date
as ID 4
9 3 08/01/2006 N N Same event date
as ID 6
When using the SQL presented above, this data scenario will result in the
three new records being marked with a MaxInd or MinInd in addition to their
related events also being marked as MinInd or MaxInd.
This situate breaks the rule that only one event per URN can be either the
Maximum or MinimumStephen wrote:
> This situate breaks the rule that only one event per URN can be either the
> Maximum or Minimum
... and therefore your requirements specification is incomplete. You
said you only want to update one row but you haven't told us which one
it should be. SQL isn't good at stuff like "Give me any one row. I
don't care which". Even if it were easier solve, code that returns
random results tends to look like a bug to end users and testers. For
that reason, you should always query an ambiguity like this one by
going back to the user, business owner or whoever.
Also, please post a CREATE TABLE statement for the table. That way we
don't have to guess what the actual datatypes and constraints are and
we can test out possible solutions for you. Don't forget to include
keys. Presumably you do have some keys to work with? I don't like to
make too many assumptions without seeing the table structure but on the
face of it the table design looks weak. I'd suggest you eliminate the
duplicate data rather than write lots of complex code to cope with it.
Finally, do tell us what version of SQL Server you are using.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Also, the maximum and minimum should be functions of the data in your table,
and not stored as data themselves. They are results of a query and should
be determined when you need to access them, not stored and physically
updated. Of course this is a rule of database design, and sometimes there
are reasons (i.e. performance) for breaking these rules. Usually, however,
you can follow the rules and still get good performance, if you are careful
with how you write your SQL queries and how you index your columns.
You should consider either creating a unique constraint that prevents these
duplicate dates, or adding a sequence column to differentiate them.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143818532.404505.236730@.i40g2000cwc.googlegroups.com...
> Stephen wrote:
the
> ... and therefore your requirements specification is incomplete. You
> said you only want to update one row but you haven't told us which one
> it should be. SQL isn't good at stuff like "Give me any one row. I
> don't care which". Even if it were easier solve, code that returns
> random results tends to look like a bug to end users and testers. For
> that reason, you should always query an ambiguity like this one by
> going back to the user, business owner or whoever.
> Also, please post a CREATE TABLE statement for the table. That way we
> don't have to guess what the actual datatypes and constraints are and
> we can test out possible solutions for you. Don't forget to include
> keys. Presumably you do have some keys to work with? I don't like to
> make too many assumptions without seeing the table structure but on the
> face of it the table design looks weak. I'd suggest you eliminate the
> duplicate data rather than write lots of complex code to cope with it.
> Finally, do tell us what version of SQL Server you are using.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek
Don't multi-post. See my answer in .programming.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
...
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
KrzysiekDon't multi-post. See my answer in .programming.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

any help with my select ?

Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
KrzysiekDon't multi-post. See my answer in .programming.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Krzysiek" <krzysiek79@.hotmail.com> wrote in message
news:c9fq0j$25cf$1@.news2.ipartners.pl...
Hello All,
I have a simple table with 2 columns.
datetime int
27.05.04 1
29.05.04 2
31.05.04 5
and i need to get a view that looks like this below :
27.05.04 1 1
29.05.04 2 3
31.05.04 5 8
The third column shows a sum of numbers that were display before present one
..
I was trying build stored proc however I'm not so familiar with SQL so I
failed.. :-(
can anybody help ?
thx in advance
Krzysiek

ANTI-JOIN with two or more columns.

Consider the following schema:
CREATE TABLE A(i int, j int)
CREATE TABLE B(i int, j int)
INSERT A VALUES(1,1)
INSERT A VALUES(1,2)
INSERT A VALUES(2,1)
INSERT A VALUES(2,2)
INSERT A VALUES(2,3)
INSERT B VALUES(3,1)
INSERT B VALUES(3,2)
INSERT B VALUES(1,1)
INSERT B VALUES(1,2)
INSERT B VALUES(2,2)
INSERT B VALUES(4,5)
INSERT B VALUES(5,4)
How do I compose a TSQL Query that will list the records in Table B, that DO
NOT exist in Table A.?
I would like a record set that looks like this:
i j
-- --
3 1
3 2
4 5
5 4
--
Bob MarleyBob,
I don't know if column "i" is supposed to represent the PK or the combination of "i" and
"j". There isn't enough data here to make that determination, so I'll assume the latter:
select *
from B as b
where not exists (select * from A as a where a.i = b.i and a.j = b.j)
Hope this helps!
"Big Bob" <Bob@.hotmail.com> wrote in message
news:%23Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Try:
select * from b
where not exists
(select * from a where a.i = b.i and a.j=b.j)
--
- Vishal
"Big Bob" <Bob@.hotmail.com> wrote in message news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Hi Bob
This is a perfect example of what correlated subqueries were invented for.
SELECT i,j
FROM B
WHERE NOT EXISTS (SELECT i, j FROM A
WHERE i = B.i AND j = B.j)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Big Bob" <Bob@.hotmail.com> wrote in message
news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that
DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Thanks Kalen!
Is there a way to do this with the new style SQL-92 joins rather than the
old SQL-89 join style. This is what I have been beating my head against the
wall with; the new join style.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> Hi Bob
> This is a perfect example of what correlated subqueries were invented for.
>
> SELECT i,j
> FROM B
> WHERE NOT EXISTS (SELECT i, j FROM A
> WHERE i = B.i AND j = B.j)
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Big Bob" <Bob@.hotmail.com> wrote in message
> news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > Consider the following schema:
> >
> > CREATE TABLE A(i int, j int)
> > CREATE TABLE B(i int, j int)
> >
> > INSERT A VALUES(1,1)
> > INSERT A VALUES(1,2)
> > INSERT A VALUES(2,1)
> > INSERT A VALUES(2,2)
> > INSERT A VALUES(2,3)
> >
> > INSERT B VALUES(3,1)
> > INSERT B VALUES(3,2)
> > INSERT B VALUES(1,1)
> > INSERT B VALUES(1,2)
> > INSERT B VALUES(2,2)
> > INSERT B VALUES(4,5)
> > INSERT B VALUES(5,4)
> >
> >
> > How do I compose a TSQL Query that will list the records in Table B,
that
> DO
> > NOT exist in Table A.?
> >
> > I would like a record set that looks like this:
> >
> > i j
> > -- --
> > 3 1
> > 3 2
> > 4 5
> > 5 4
> >
> >
> >
> > --
> > Bob Marley
> >
> >
>|||use NOT EXISTS
e.g.
select * from B
where NOT EXISTS
(select * from A where A.i = B.i and A.j = B.j)
"Big Bob" <Bob@.hotmail.com> wrote in message
news:%23Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that
DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||I don't think this can be done with any kind of join.
If you have found a way to do it with the old style, post it and I'm sure
someone can easily convert it to new style.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Big Bob" <Bob@.hotmail.com> wrote in message
news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> Thanks Kalen!
> Is there a way to do this with the new style SQL-92 joins rather than the
> old SQL-89 join style. This is what I have been beating my head against
the
> wall with; the new join style.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > Hi Bob
> >
> > This is a perfect example of what correlated subqueries were invented
for.
> >
> >
> > SELECT i,j
> > FROM B
> > WHERE NOT EXISTS (SELECT i, j FROM A
> > WHERE i = B.i AND j = B.j)
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Big Bob" <Bob@.hotmail.com> wrote in message
> > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > Consider the following schema:
> > >
> > > CREATE TABLE A(i int, j int)
> > > CREATE TABLE B(i int, j int)
> > >
> > > INSERT A VALUES(1,1)
> > > INSERT A VALUES(1,2)
> > > INSERT A VALUES(2,1)
> > > INSERT A VALUES(2,2)
> > > INSERT A VALUES(2,3)
> > >
> > > INSERT B VALUES(3,1)
> > > INSERT B VALUES(3,2)
> > > INSERT B VALUES(1,1)
> > > INSERT B VALUES(1,2)
> > > INSERT B VALUES(2,2)
> > > INSERT B VALUES(4,5)
> > > INSERT B VALUES(5,4)
> > >
> > >
> > > How do I compose a TSQL Query that will list the records in Table B,
> that
> > DO
> > > NOT exist in Table A.?
> > >
> > > I would like a record set that looks like this:
> > >
> > > i j
> > > -- --
> > > 3 1
> > > 3 2
> > > 4 5
> > > 5 4
> > >
> > >
> > >
> > > --
> > > Bob Marley
> > >
> > >
> >
> >
>|||if you really want to use it, you can use a left join
select b.*
from b
left join a on b.i=a.i and b.j=a.j
where a.i is null
the where eliminates the joined rows, however
1. it defeats the purpose of a left join somewhat, which is to get all from
left, and matching from right
2. afaik, using exists is faster
"Big Bob" <Bob@.hotmail.com> wrote in message
news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> Thanks Kalen!
> Is there a way to do this with the new style SQL-92 joins rather than the
> old SQL-89 join style. This is what I have been beating my head against
the
> wall with; the new join style.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > Hi Bob
> >
> > This is a perfect example of what correlated subqueries were invented
for.
> >
> >
> > SELECT i,j
> > FROM B
> > WHERE NOT EXISTS (SELECT i, j FROM A
> > WHERE i = B.i AND j = B.j)
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Big Bob" <Bob@.hotmail.com> wrote in message
> > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > Consider the following schema:
> > >
> > > CREATE TABLE A(i int, j int)
> > > CREATE TABLE B(i int, j int)
> > >
> > > INSERT A VALUES(1,1)
> > > INSERT A VALUES(1,2)
> > > INSERT A VALUES(2,1)
> > > INSERT A VALUES(2,2)
> > > INSERT A VALUES(2,3)
> > >
> > > INSERT B VALUES(3,1)
> > > INSERT B VALUES(3,2)
> > > INSERT B VALUES(1,1)
> > > INSERT B VALUES(1,2)
> > > INSERT B VALUES(2,2)
> > > INSERT B VALUES(4,5)
> > > INSERT B VALUES(5,4)
> > >
> > >
> > > How do I compose a TSQL Query that will list the records in Table B,
> that
> > DO
> > > NOT exist in Table A.?
> > >
> > > I would like a record set that looks like this:
> > >
> > > i j
> > > -- --
> > > 3 1
> > > 3 2
> > > 4 5
> > > 5 4
> > >
> > >
> > >
> > > --
> > > Bob Marley
> > >
> > >
> >
> >
>|||Cool. Yes, it sort of defeats the purpose of LEFT JOIN, so maybe that's why
I didn't think of it. Also, it is prime example of why not all old style
joins can be converted easily to new style joins, or visa versa, as in this
case.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Trey Walpole" <treyNOpole@.SPcomcastAM.net> wrote in message
news:#Vf18JkrDHA.1692@.TK2MSFTNGP12.phx.gbl...
> if you really want to use it, you can use a left join
> select b.*
> from b
> left join a on b.i=a.i and b.j=a.j
> where a.i is null
> the where eliminates the joined rows, however
> 1. it defeats the purpose of a left join somewhat, which is to get all
from
> left, and matching from right
> 2. afaik, using exists is faster
>
> "Big Bob" <Bob@.hotmail.com> wrote in message
> news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> > Thanks Kalen!
> >
> > Is there a way to do this with the new style SQL-92 joins rather than
the
> > old SQL-89 join style. This is what I have been beating my head against
> the
> > wall with; the new join style.
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > > Hi Bob
> > >
> > > This is a perfect example of what correlated subqueries were invented
> for.
> > >
> > >
> > > SELECT i,j
> > > FROM B
> > > WHERE NOT EXISTS (SELECT i, j FROM A
> > > WHERE i = B.i AND j = B.j)
> > >
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Big Bob" <Bob@.hotmail.com> wrote in message
> > > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > > Consider the following schema:
> > > >
> > > > CREATE TABLE A(i int, j int)
> > > > CREATE TABLE B(i int, j int)
> > > >
> > > > INSERT A VALUES(1,1)
> > > > INSERT A VALUES(1,2)
> > > > INSERT A VALUES(2,1)
> > > > INSERT A VALUES(2,2)
> > > > INSERT A VALUES(2,3)
> > > >
> > > > INSERT B VALUES(3,1)
> > > > INSERT B VALUES(3,2)
> > > > INSERT B VALUES(1,1)
> > > > INSERT B VALUES(1,2)
> > > > INSERT B VALUES(2,2)
> > > > INSERT B VALUES(4,5)
> > > > INSERT B VALUES(5,4)
> > > >
> > > >
> > > > How do I compose a TSQL Query that will list the records in Table B,
> > that
> > > DO
> > > > NOT exist in Table A.?
> > > >
> > > > I would like a record set that looks like this:
> > > >
> > > > i j
> > > > -- --
> > > > 3 1
> > > > 3 2
> > > > 4 5
> > > > 5 4
> > > >
> > > >
> > > >
> > > > --
> > > > Bob Marley
> > > >
> > > >
> > >
> > >
> >
> >
>|||Here's a solution that uses an INNER JOIN and grouping:
select B.i, B.j
from B join A
on A.i < B.i and A.j < B.j
group by B.i, B.j
having max(A.i) + max(A.j) < B.i + B.j
And another, with T-SQL proprietary TOP .. WITH
TIES. It's not as efficient, but it's, well, it's another query...
select top 1 with ties B.i, B.j
from B join A
on A.i <> B.i or A.j <> B.j
group by B.i, B.j
order by count(*) desc
-- Steve Kass
-- Drew University
-- Ref: E3D3398B-1749-4F42-B5B0-6F7B71BA2AC5
Trey Walpole wrote:
>if you really want to use it, you can use a left join
>select b.*
> from b
> left join a on b.i=a.i and b.j=a.j
> where a.i is null
>the where eliminates the joined rows, however
>1. it defeats the purpose of a left join somewhat, which is to get all from
>left, and matching from right
>2. afaik, using exists is faster
>
>"Big Bob" <Bob@.hotmail.com> wrote in message
>news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
>
>>Thanks Kalen!
>>Is there a way to do this with the new style SQL-92 joins rather than the
>>old SQL-89 join style. This is what I have been beating my head against
>>
>the
>
>>wall with; the new join style.
>>
>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
>>
>>Hi Bob
>>This is a perfect example of what correlated subqueries were invented
>>
>for.
>
>>SELECT i,j
>>FROM B
>>WHERE NOT EXISTS (SELECT i, j FROM A
>> WHERE i = B.i AND j = B.j)
>>
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Big Bob" <Bob@.hotmail.com> wrote in message
>>news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
>>
>>Consider the following schema:
>>CREATE TABLE A(i int, j int)
>>CREATE TABLE B(i int, j int)
>>INSERT A VALUES(1,1)
>>INSERT A VALUES(1,2)
>>INSERT A VALUES(2,1)
>>INSERT A VALUES(2,2)
>>INSERT A VALUES(2,3)
>>INSERT B VALUES(3,1)
>>INSERT B VALUES(3,2)
>>INSERT B VALUES(1,1)
>>INSERT B VALUES(1,2)
>>INSERT B VALUES(2,2)
>>INSERT B VALUES(4,5)
>>INSERT B VALUES(5,4)
>>
>>How do I compose a TSQL Query that will list the records in Table B,
>>
>>that
>>
>>DO
>>
>>NOT exist in Table A.?
>>I would like a record set that looks like this:
>>i j
>>-- --
>>3 1
>>3 2
>>4 5
>>5 4
>>
>>--
>>Bob Marley
>>
>>
>>
>>
>
>|||Oops - the first one should be
select B.i, B.j
from B join A
on A.i <= B.i and A.j <= B.j
group by B.i, B.j
having max(A.i) + max(A.j) < B.i + B.j
SK
Steve Kass wrote:
> Here's a solution that uses an INNER JOIN and grouping:
> select B.i, B.j
> from B join A
> on A.i < B.i and A.j < B.j
> group by B.i, B.j
> having max(A.i) + max(A.j) < B.i + B.j
>
> And another, with T-SQL proprietary TOP .. WITH
> TIES. It's not as efficient, but it's, well, it's another query...
> select top 1 with ties B.i, B.j
> from B join A
> on A.i <> B.i or A.j <> B.j
> group by B.i, B.j
> order by count(*) desc
>
> -- Steve Kass
> -- Drew University
> -- Ref: E3D3398B-1749-4F42-B5B0-6F7B71BA2AC5
> Trey Walpole wrote:
>> if you really want to use it, you can use a left join
>> select b.*
>> from b
>> left join a on b.i=a.i and b.j=a.j
>> where a.i is null
>> the where eliminates the joined rows, however
>> 1. it defeats the purpose of a left join somewhat, which is to get
>> all from
>> left, and matching from right
>> 2. afaik, using exists is faster
>>
>> "Big Bob" <Bob@.hotmail.com> wrote in message
>> news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Kalen!
>> Is there a way to do this with the new style SQL-92 joins rather
>> than the
>> old SQL-89 join style. This is what I have been beating my head
>> against
>>
>> the
>>
>> wall with; the new join style.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
>>
>> Hi Bob
>> This is a perfect example of what correlated subqueries were invented
>>
>> for.
>>
>> SELECT i,j
>> FROM B
>> WHERE NOT EXISTS (SELECT i, j FROM A
>> WHERE i = B.i AND j = B.j)
>>
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Big Bob" <Bob@.hotmail.com> wrote in message
>> news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
>>
>> Consider the following schema:
>> CREATE TABLE A(i int, j int)
>> CREATE TABLE B(i int, j int)
>> INSERT A VALUES(1,1)
>> INSERT A VALUES(1,2)
>> INSERT A VALUES(2,1)
>> INSERT A VALUES(2,2)
>> INSERT A VALUES(2,3)
>> INSERT B VALUES(3,1)
>> INSERT B VALUES(3,2)
>> INSERT B VALUES(1,1)
>> INSERT B VALUES(1,2)
>> INSERT B VALUES(2,2)
>> INSERT B VALUES(4,5)
>> INSERT B VALUES(5,4)
>>
>> How do I compose a TSQL Query that will list the records in Table B,
>>
>> that
>>
>> DO
>>
>> NOT exist in Table A.?
>> I would like a record set that looks like this:
>> i j
>> -- --
>> 3 1
>> 3 2
>> 4 5
>> 5 4
>>
>> --
>> Bob Marley
>>
>>
>>
>>
>>
>>
>|||For those that are interested,Sql99 would allow this problem
to be easily solved without any correlated subquery,join or
grouping.Only a rank/counter based on the i,j combination and
table identification is necessary.Based on the rank, a simple
select would return the rows in table B not in A.
Here's the basic idea using the RAC utility to easily simulate
the sql99 rank function.
We create a union query where we assign a 1 (tableid) if the
i,j combination is from table A and a 2 if from table B.
We sort the data by i,j and tableid and obtain a rank
of each i,j combination.
Exec Rac
@.transform='_dummy_',
@.rows='i & j & tableid',
@.pvtcol='Sql*Plus',
@.from='(select i,j,2 as tableid from B
union all
select i,j,1 as tableid from A) as alldata',
@.grand_totals='n',@.rowbreak='n',
-- Rank i,j combinations based on the sort of i,j,tableid.
-- The rank column is named tablecnter.
@.rowcounters='j{tablecnter}'
i j tableid tablecnter
-- -- -- --
1 1 1 1
1 1 2 2
1 2 1 1
1 2 2 2
2 1 1 1
2 2 1 1
2 2 2 2
2 3 1 1
3 1 2 1
3 2 2 1
4 5 2 1
5 4 2 1
As you can see only when the tableid is 2 (table B) and
the rank (tablecnter) is 1 is it the case that the i,j
combination is in table B and not in table A.Now the
solution is simple.
Exec Rac
@.transform='_dummy_',
@.rows='i & j & tableid',
@.pvtcol='Sql*Plus',
@.from='(select i,j,2 as tableid from B
union all
select i,j,1 as tableid from A) as alldata',
@.grand_totals='n',@.rowbreak='n',
@.rowcounters='j{tablecnter}',
-- Check for tableid=2 and tablecnter=1 (data only in B).
@.select='select i,j
from rac
where tableid=2 and tablecnter=1
order by rd'
i j
-- --
3 1
3 2
4 5
5 4
RAC v2.2 and QALite @.
www.rac4sql.net

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).
> > > >