Anyone got a chart of datatype conversions?

I'm looking for a chart that shows the VB6 > .NET > SQL 2005 datatypes.
E.g.
VB6 .NET SQL2005
integer int16 smallint
long int32 int
byte byte tinyint
But I want a whole chart with all datatypes. Anyone know of a URL with a
chart?Do a search in google. There are several sites with VB6 and VB.NET type
comparisons.
Regarding SQL 2005 types there are no direct mapping for all the available
types. What you may have to look for is ADO.NET type comparisons. Checkout
the MSDN site for a full list of all ADO.NET type mappings.
--
Anith|||I did a google search and didn't much that was helpful with SQL 2005.
That's why I opened this thread. I appreciate msdn suggestion but hopefully
someone will still have a URL they like for datatype conversions.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%2385m9c4UGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Do a search in google. There are several sites with VB6 and VB.NET type
> comparisons.
> Regarding SQL 2005 types there are no direct mapping for all the available
> types. What you may have to look for is ADO.NET type comparisons. Checkout
> the MSDN site for a full list of all ADO.NET type mappings.
> --
> Anith
>

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David
|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
--
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Consider the following:
> >
> > TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> > There is a clustered index on (c1, c3), nonclustered index on (c2)
> >
> > The UDF contains a SELECT statement of the form:
> > select c1 from TableA where c2 = @.InputParm
> >
> > TableA contains about 300000 rows and there are some values for column
c2
> > that have a cardinality of over 30000. So obviously if this value gets
> > passed into the UDF, a Clustered Index Scan is probably best. On the
> other
> > hand if a parm gets passed for a value that has a few corresponding rows
> for
> > c2 then it is best that the nonclustered index on c2 be used.
> >
> > The problem is that when the UDF is first invoked with a parm that has a
> > high number of corresponding rows for c2, the execution plan uses a
> > clustered index SCAN and all subsuquent calls use this same execution
> plan.
> > Ideally I would like to have this UDF defined to recompile each call so
> that
> > I get the best execution plan for each call.
> >
> > If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> > (doesn't appear to be valid for functions), or I would use dynamic SQL
> > (since Functions cannot access temp tables I cannot use this approach
> > either).
> >
> > Any ideas would be appreciated.
> >
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

Any way to force a recompile on a statement within a User Defined Function?

Consider the following:
TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
There is a clustered index on (c1, c3), nonclustered index on (c2)
The UDF contains a SELECT statement of the form:
select c1 from TableA where c2 = @.InputParm
TableA contains about 300000 rows and there are some values for column c2
that have a cardinality of over 30000. So obviously if this value gets
passed into the UDF, a Clustered Index Scan is probably best. On the other
hand if a parm gets passed for a value that has a few corresponding rows for
c2 then it is best that the nonclustered index on c2 be used.
The problem is that when the UDF is first invoked with a parm that has a
high number of corresponding rows for c2, the execution plan uses a
clustered index SCAN and all subsuquent calls use this same execution plan.
Ideally I would like to have this UDF defined to recompile each call so that
I get the best execution plan for each call.
If this were a Stored Proc - I would either define it "WITH RECOMPILE"
(doesn't appear to be valid for functions), or I would use dynamic SQL
(since Functions cannot access temp tables I cannot use this approach
either).
Any ideas would be appreciated.
I have a UDF in which I would like to force a recompile based on the value
of one of the parameters. It seems that when the UDF is first executed, if
it is passed a value that has a it performs
Within the UDF is a select from"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
> Consider the following:
> TableA (c1 int, c2 int, c3 int, c4 int, c5 varchar(80, etc...)
> There is a clustered index on (c1, c3), nonclustered index on (c2)
> The UDF contains a SELECT statement of the form:
> select c1 from TableA where c2 = @.InputParm
> TableA contains about 300000 rows and there are some values for column c2
> that have a cardinality of over 30000. So obviously if this value gets
> passed into the UDF, a Clustered Index Scan is probably best. On the
other
> hand if a parm gets passed for a value that has a few corresponding rows
for
> c2 then it is best that the nonclustered index on c2 be used.
> The problem is that when the UDF is first invoked with a parm that has a
> high number of corresponding rows for c2, the execution plan uses a
> clustered index SCAN and all subsuquent calls use this same execution
plan.
> Ideally I would like to have this UDF defined to recompile each call so
that
> I get the best execution plan for each call.
> If this were a Stored Proc - I would either define it "WITH RECOMPILE"
> (doesn't appear to be valid for functions), or I would use dynamic SQL
> (since Functions cannot access temp tables I cannot use this approach
> either).
> Any ideas would be appreciated.
>
I don't think there's any way to get the function to recompile.
Here are your options:
1. Change the indexing scheme so a single plan is appropriate.
-The obvious candidate here is to add c1 to the nonclustered index on c2,
thus covering your query.
2. Examine the cardinality inside the stored function and switch between two
hinted queries.
3. Rewrite the function as a join and use dynamic SQL.
David
David|||Can you wrap the query in the UDF inside sp_executesql? If so, do not use
any parameters to sp_executesql, put the entire query in the query string>
that way each different value for c2 will have a different plan.
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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23uah$MMeEHA.3016@.tk2msftngp13.phx.gbl...
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:O7J6bCLeEHA.712@.TK2MSFTNGP09.phx.gbl...
c2[vbcol=seagreen]
> other
> for
> plan.
> that
>
> I don't think there's any way to get the function to recompile.
> Here are your options:
> 1. Change the indexing scheme so a single plan is appropriate.
> -The obvious candidate here is to add c1 to the nonclustered index on
c2,
> thus covering your query.
> 2. Examine the cardinality inside the stored function and switch between
two
> hinted queries.
> 3. Rewrite the function as a join and use dynamic SQL.
> David
>
> David
>

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 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's stance on SmallInt vs. Int

I decided to push this out to it's own thread. Here's the question, if you a
re
deciding to abide purely by ANSI SQL 92 standards and are only thinking in t
erms
of the logical model, how does the modeler decide when to use a SmallInt vs.
an
Integer if the allowed range is considered a physical implementation?
A secondary question relates to boolean values. Is the official ANSI solutio
n to
storing boolean values (0 or 1) to use either a full blown Int (of course, s
ince
we are only talking about ANSI, we have no idea how big that is ;-> ) or a
Char(1)?
ThomasANSI / ISO SQL doesn't define absolute numeric precision so the
decisions on which datatype to use can only be made in the context of
an actual implementation. Those physical details are just outside the
scope of the standard. Does it matter?
SQL99 defines a Boolean datatype but SQL Server doesn't support it.
David Portas
SQL Server MVP
--|||> ANSI / ISO SQL doesn't define absolute numeric precision so the
> decisions on which datatype to use can only be made in the context of
> an actual implementation. Those physical details are just outside the
> scope of the standard. Does it matter?
By specifying the concept of a SmallInt but providing no information that wo
uld
help the modeler choose one over the other, it sounds like the ANSI team mad
e an
outright error in including SmallInt in the first place. Said another way, s
ince
designers are already required to accommodate the given DBMS for things like
the
range of values for a SmallInt, where is the problem in using DBMS specific
features like TinyInt? After all, if portability is the holy grail of standa
rds
use, it is already the case that porting a database from one DBMS to another
might break because one vendor used 2-bytes for their SmallInt while another
used 1-byte for their SmallInt. Thus, in terms of data types certainly, it m
akes
sense to use things like TinyInt to accomplish you goal.
In case you were wondering, I bring this up becuase of a comment Mr. Celko m
ade
about not using SQL's TinyInt datatype.

> SQL99 defines a Boolean datatype but SQL Server doesn't support it.
Is that true of SQL 2005 as well?
Thomas