Any way to pass command line variables to osql

I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
Is it possible to do this.if ur parameter is positioned 2nd then add this code in your command file.

<varname> is some variable name that you want to use to trap the parameter. lets say empid for instance

SET empid=%2

after this, the place where u wud be calling ur stored procedure use this syntax.

osql -S servername -l 60 -n -E -d dbname
-Q"EXEC sp123 @.Var1 = '%empid%'"

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 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!
>

Any performance issues if we use BigInt

In the past, I've always used "int" for the Identity field. But as the
application usage increases, it seems to demand for values that exceed the
"int" scope. I'm thinking of upgrading all my Primary Keys that were "Int"
to "BigInt".
I'd like to know if there's any performance degradation by doing so.
Also, what would be a compatible datatype of BigInt in Classic ASP and in
ASP.NET 2.0 C#.
Thanks for your response.Bob (spamfree@.nospam.com) writes:
> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed
> the "int" scope. I'm thinking of upgrading all my Primary Keys that
> were "Int" to "BigInt".
> I'd like to know if there's any performance degradation by doing so.
There is one, although not dramatic. bigint takes up eight bytes, where
as int takes up four bytes. This means that you row size increases, and
you can fit fewer rows per page, so to read the same number of rows, SQL
Server will have to access more pages.
int goes all the way to 2,147 milliards, which is quite lot, so int may
last longer than you believe.

> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
No idea.
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|||> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
Bigint is a 64-bit number so you can use Int64 in .NET languages. Assuming
you are using VBScript in ASP, all data types are variant. I believe
variant subtype double would be used for a large bigint value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <spamfree@.nospam.com> wrote in message
news:u$pzOOF%23FHA.740@.TK2MSFTNGP11.phx.gbl...
> In the past, I've always used "int" for the Identity field. But as the
> application usage increases, it seems to demand for values that exceed the
> "int" scope. I'm thinking of upgrading all my Primary Keys that were
> "Int"
> to "BigInt".
> I'd like to know if there's any performance degradation by doing so.
> Also, what would be a compatible datatype of BigInt in Classic ASP and in
> ASP.NET 2.0 C#.
> Thanks for your response.
>|||The right answer is not to ever use IDENTITY, but to find a relational
key.
You don't seem to know that fields and columns are totally different,
which is probably why you ask this kind of question about a table
property.
In effect, if this was a furniture forum you would be asking for the
best kind of rocks to smash screws into furniture. The kludge answer
is "Granite!", but the right answer is to take the time to learn about
screws, screw drivers, nails, glue, etc.|||> int goes all the way to 2,147 milliards,
Erland, you can set the seed value to -2 billion and get twice as much
values out of a regular int, right?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> Erland, you can set the seed value to -2 billion and get twice as much
> values out of a regular int, right?
Correct, as long as you plan for it ahead. Not very fun of changing all
that when you are about reach 2^31-1.
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

Any help solidifying the following?

I am trying to create a table structure that would allow users to define custom formulas for computing 1 or more values for any given number of variables.

Table Structure

FORMULA
ID | Descr
-----
1 | Rectangle

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * V

FORMULA_VARIABLE_VALUE
fkID | Variable | Value
-------
1 | X | 10
1 | Y | 10
1 | Z | 2

In the above, notice on FORMULA_VARIABLE the field named expression. If this is NULL, this will be a parameter that must be specified by the user, else, this value from the expression must be evaluated using the variable values.

Notice the Volume expression uses the expression for the variable A. This is where the fun begins...

I have produced the following using the code below. If you could please look at it and let me know if there are any ways to make this more effective, efficient, and stable. That would be greatly appreciated.

The code is a first time run-through!

USE Northwind
GO

CREATE TABLE FORMULA
(
FormulaID int NOT NULL,
Descr char(30)
)
GO

CREATE TABLE FORMULA_VARIABLE
(
fkFormulaID int NOT NULL,
Dimension char(30),
Variable char(10),
Units char(5),
Expression char(255)
)
GO

CREATE TABLE FORMULA_VARIABLE_VALUE
(
fkFormulaID int,
Variable char(10),
Value float
)
GO

INSERT INTO FORMULA (FormulaID, Descr)
SELECT 1, 'Rectangular'
GO

INSERT INTO FORMULA_VARIABLE (fkFormulaID, Dimension, Variable, Units, Expression)
SELECT 1, 'Side 1', 'X', 'IN', NULL UNION ALL
SELECT 1, 'Side 2', 'Y', 'IN', NULL UNION ALL
SELECT 1, 'Height', 'Z', 'IN', NULL UNION ALL
SELECT 1, 'Area', 'A', 'SI', 'X * Y' UNION ALL
SELECT 1, 'Volume', 'V', 'I3', 'A * Z'GO
GO

INSERT INTO FORMULA_VARIABLE_VALUE (fkFormulaID, Variable, Value)
SELECT 1, 'X', 10 UNION ALL
SELECT 1, 'Y', 10 UNION ALL
SELECT 1, 'Z', 2
GO

CREATE PROCEDURE usp_BuildExpressions
@.iFormula int
AS

CREATE TABLE #TempFormulaResults
(
Dimension char(30),
Value float
)

DECLARE @.cDimension char(30), @.cOldExp varchar(255), @.cNewExp varchar(4000), @.cVariable char(5)

DECLARE OldExpCursor CURSOR FOR
SELECT Dimension, Expression FROM FORMULA_VARIABLE WHERE fkFormulaID = 1 AND Expression IS NOT NULL
OPEN OldExpCursor

FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Iterate through expresions, build new cursor where dimension is not equal
-- Replace expression variable with expressions
DECLARE NewExpCursor CURSOR FOR
SELECT Variable, Expression FROM FORMULA_VARIABLE
WHERE fkFormulaID = @.iFormula AND Expression IS NOT NULL AND NOT (Dimension = @.cDimension)

OPEN NewExpCursor
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(RTRIM(@.cOldExp), RTRIM(@.cVariable), RTRIM(@.cNewExp))
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
END
CLOSE NewExpCursor
DEALLOCATE NewExpCursor

-- Get the variable values, replace values in expression and calcluate result
DECLARE @.fValue float, @.cVarName char(5)
DECLARE ValueCursor CURSOR FOR
SELECT Variable, Value FROM FORMULA_VARIABLE_VALUE
WHERE fkFormulaID = @.iFormula

OPEN ValueCursor
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
WHILE(@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(@.cOldExp, RTRIM(@.cVarName), CONVERT(VARCHAR, @.fValue))
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
END

DECLARE @.cSelect nvarchar(4000), @.param nvarchar(4000), @.Eval float

SET @.cSelect = 'SET @.fResult = ' + @.cOldExp
SET @.Param = '@.fResult float OUTPUT'

EXEC sp_executesql @.cSelect, @.Param, @.Eval OUT
INSERT INTO #TempFormulaResults (Dimension, Value) VALUES (@.cDimension, @.Eval)
FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
CLOSE ValueCursor
DEALLOCATE ValueCursor
END

CLOSE OldExpCursor
DEALLOCATE OldExpCursor

SELECT * FROM #TempFormulaResults
DROP TABLE #TempFormulaResults
GO

EXEC usp_BuildExpressions 1

DROP TABLE FORMULA
GO
DROP TABLE FORMULA_VARIABLE
GO
DROP TABLE FORMULA_VARIABLE_VALUE
GO
DROP PROCEDURE usp_BuildExpressions

Any thoughts?

Mike BOn flaw I see, but I am not sure how to fix is if the "nested" variables are more then 2 deep. What if I used Volume from above in a different formula?

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * Z
1 | 1/2 Vol | v | V / 2

Now after executing the stored proc usp_BuildExpressions, the expression for 1/2 Vol would not work and end up looking like
A * 2 / 2.

It will stop short of replacing all the variables with the appropriated nested expressions.

Any ideas?

Mike B

Any facility to hold string data of length more than 8000 characters?

Hi,
I have a typical scenario in one of our application.
In a Stored Procedure, I need to get values from different fields and form a
select statement.
The problem here is each field may be upto a length of 8000. So after
forming the final query it will exceed length of 8000. How to concatenate
these field values to form a query which will have length of more than 8000
characters? Is there any data type allows this or is there array concept?
Please provide a solution. Help of any sort is highly appreciated.
Thanks,
Su ManSu,
You concatenate them right at the execution statement:
EXEC(@.str1 + @.str2 + ...)
Ilya
"Su Man" <subu501@.yahoo.com> wrote in message
news:d89f1u$neq$1@.news.mch.sbs.de...
> Hi,
> I have a typical scenario in one of our application.
> In a Stored Procedure, I need to get values from different fields and form
a
> select statement.
> The problem here is each field may be upto a length of 8000. So after
> forming the final query it will exceed length of 8000. How to concatenate
> these field values to form a query which will have length of more than
8000
> characters? Is there any data type allows this or is there array concept?
> Please provide a solution. Help of any sort is highly appreciated.
> Thanks,
> Su Man
>|||But we do not know how many fields to be concatenated. They are dynamic
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:u8uM#YPbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Su,
> You concatenate them right at the execution statement:
> EXEC(@.str1 + @.str2 + ...)
> Ilya
> "Su Man" <subu501@.yahoo.com> wrote in message
> news:d89f1u$neq$1@.news.mch.sbs.de...
form
> a
concatenate
> 8000
concept?
>|||Su,
What is you statement? Every statement has an end.
Ilya
"Su Man" <subu501@.yahoo.com> wrote in message
news:d89g2r$rfc$1@.news.mch.sbs.de...
> But we do not know how many fields to be concatenated. They are dynamic
> "Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
> news:u8uM#YPbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> form
> concatenate
> concept?
>|||It seems to me, although I am not an expert, that when one has to do
something like this, there is a major flaw in the data model.
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:%231HD4fPbFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Su,
> What is you statement? Every statement has an end.
> Ilya
> "Su Man" <subu501@.yahoo.com> wrote in message
> news:d89g2r$rfc$1@.news.mch.sbs.de...
>|||"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d89hcb$evf$1$8300dec7@.news.demon.co.uk...

> It seems to me, although I am not an expert, that when one has to do
> something like this, there is a major flaw in the data model.
I couldn't agree more!|||Ilya,
Eevery statement will not have end.
Statement1 may be - select a,c,h,j,e from gggjjjh
Statement2 may be - where t1.a = t2.b
Statement3 may be - group by g,k,l
Statement4 may be - having s = 'value'
Statement5 may be - Order by w,k
The above are just indcative and each statement may be upto a lengh of 8000.
Considering the above,
Statement1+Statement2+Statement3+Stateme
nt4+Statement5 may sum up to 40000
and which data type can hold the string.
Or is there any concept of array where I can hole all individula statements
and finall concatenate to execute.
Please reply.
Thanks,
Su man
"Ilya Margolin" <ilya_no_spam_@.unapen.com> wrote in message
news:#1HD4fPbFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Su,
> What is you statement? Every statement has an end.
> Ilya
> "Su Man" <subu501@.yahoo.com> wrote in message
> news:d89g2r$rfc$1@.news.mch.sbs.de...
and
after
than
>

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

Anticipated PK Violation

I am maintaining a stored proc that does the following (pseudo code for simplicity)

INSERT INTO MyTable (CountColumn, ...) VALUES (1, ...)

-- 2627 = Primary key violation
IF (@.@.ERROR = 2627)
UPDATE MyTable SET CountColumn = CountColumn + 1
WHERE ...

Basically, try to create a new record with a PK and a count of 1. If a PK violation occurs, UPDATE the count of existing record.

Pretty simple logic. On some servers this works exactly as expected; the PK violation is caught and handled internally. On other servers, this error percolates out of the stored proc and causes the calling code to receive an error and fail. All servers are SQL Server 2000 SP3a Standard Edition. Any ideas why this happens?

Is there a better (and ideally as fast or faster) way to handle the INSERT/UPDATE issue? I can put the UPDATE first and INSERT if @.@.ROWCOUNT is 0, but that has the slight potential of a race condition where two processes try to INSERT the same PK at the same time.you could try a conditional flow control block like this:

if not exists (select * from mytable where PK = x)
insert ...
else
update ...

the subquery will always be executed, but it'll be quite fast with the search condition on the primary key

hth,
Cristian Babu|||I'd reverse the if...else:

if exists (...) update... else insert...|||I'd reverse the if...else:

if exists (...) update... else insert...

What I did is:

UPDATE
IF @.@.ROWCOUNT = 0 INSERT

I would think that is slightly more efficient.

What is really odd that the original approach (INSERT, IF ERROR THEN UPDATE) worked fine in Query Analyzer on two servers running SQL Server 2000 Personal edition but produces errors on a new installation of SQL Server 2000 Standard edition. Ideally, as a maintenance programmer, I wouldn't have had to touch the stored proc.|||Are the service pack levels identical on all 3 machines?|||Are the service pack levels identical on all 3 machines?

There is:
SQL Server Personal (no SP): The original code doesn't percolate an error
SQL Server Personal (SP3): The original code doesn't percolate an error
SQL Server Standard (SP3): The original code DOES percolate an error

I'm trying to get coworkers to patch the one unpatched server (we should ALWAYS patch our servers). But either way, it doesn't seem to be related to service patck since two systems with SP3 exhibit different behavior.

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