Is there any way, I can query across all objects in a given database for what is the current collation name for each column in an object?select [Object_Name]=object_name(id),
[Column Name] = name,
[Collation] = collation
from syscolumns
where objectproperty(id, 'ismsshipped') = 0
order by object_name(id), colid
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Sunday, March 25, 2012
collation in sql 2005
Does anyone know if there is a command in query analizer for sql server 2005 (Express) that changes the collation of all the char (varchar, nvarchar etc) in a table or in a database. And I don't mean to set the collation when creating a database, but to modify a database's collation seting.
thank you
This information might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_3zw3.asp
Buck Woody
Thursday, March 22, 2012
Collation Conflict
I am trying to move my system from SQL Server 7.0 to SQL Server 2000 and I a
m
getting a "Cannot resolve collation conflict" on query which joins a table
and a view.
The relevant field in the table is defined thus:
[ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
and the linking field in the View is defined as
CONVERT(Char(8), regno) AS Account_No
Do I have to specifically declare a Collation type in the view. If so, how?
PeterYes you need to specify COLLATION
CREATE TABLE #t (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS )
INSERT INTO #t VALUES ('a')
--different collation
CREATE TABLE #t1 (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1255_CI_AS)
INSERT INTO #t1 VALUES ('b')
select * from #t1 join #t on
#t.col=#t1.col
--Server: Msg 446, Level 16, State 9, Line 1
--Cannot resolve collation conflict for equal to operation
select * from #t1 join #t on
#t.col=#t1.col COLLATE SQL_Latin1_General_CP1_CI_AS
--No error
"Petet Tickler" <PetetTickler@.discussions.microsoft.com> wrote in message
news:D1019634-175A-45F2-B012-3B3787D001B7@.microsoft.com...
>I am trying to move my system from SQL Server 7.0 to SQL Server 2000 and I
>am
> getting a "Cannot resolve collation conflict" on query which joins a table
> and a view.
> The relevant field in the table is defined thus:
> [ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> and the linking field in the View is defined as
> CONVERT(Char(8), regno) AS Account_No
> Do I have to specifically declare a Collation type in the view. If so,
> how?
> Peter
>
m
getting a "Cannot resolve collation conflict" on query which joins a table
and a view.
The relevant field in the table is defined thus:
[ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
and the linking field in the View is defined as
CONVERT(Char(8), regno) AS Account_No
Do I have to specifically declare a Collation type in the view. If so, how?
PeterYes you need to specify COLLATION
CREATE TABLE #t (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS )
INSERT INTO #t VALUES ('a')
--different collation
CREATE TABLE #t1 (col VARCHAR(10) COLLATE SQL_Latin1_General_CP1255_CI_AS)
INSERT INTO #t1 VALUES ('b')
select * from #t1 join #t on
#t.col=#t1.col
--Server: Msg 446, Level 16, State 9, Line 1
--Cannot resolve collation conflict for equal to operation
select * from #t1 join #t on
#t.col=#t1.col COLLATE SQL_Latin1_General_CP1_CI_AS
--No error
"Petet Tickler" <PetetTickler@.discussions.microsoft.com> wrote in message
news:D1019634-175A-45F2-B012-3B3787D001B7@.microsoft.com...
>I am trying to move my system from SQL Server 7.0 to SQL Server 2000 and I
>am
> getting a "Cannot resolve collation conflict" on query which joins a table
> and a view.
> The relevant field in the table is defined thus:
> [ST_ACCOUNT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> and the linking field in the View is defined as
> CONVERT(Char(8), regno) AS Account_No
> Do I have to specifically declare a Collation type in the view. If so,
> how?
> Peter
>
Collation Conflict
Modify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=
[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=
[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
Collation Conflict
Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
Tim
Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
sqlsql
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
Tim
Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
sqlsql
Collation Conflict
Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimModify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>|||Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>|||Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimModify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>|||Your TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>|||Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >|||Your [TB1].[Product] and [TB2].[ProductCode] columns have different
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_default
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx.gbl...
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> >--Original Message--
> >Hi,
> >
> >I am running a query over 2 tables in 2 different
> databases.
> >I get the following error "Cannot resolve collation
> conflict for equal to
> >operation"
> >
> >for example
> >
> >SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
> [dbo].[TB1] [TB1]
> >INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=> [TB2].[ProductCode])
> >WHERE [TB2].[ProdGroup]=@.PG
> >
> >@.PG is a string parameter.
> >
> >Regards
> >Tim
> >
> >
> >.
> >
Collation Conflict
Modify your query so that it converts your joins to
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[/vbc
ol]
[TB2].[ProductCode])[vbcol=seagreen]
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])|||Your [TB1].[Product] and [TB2].[ProductCode] columns have di
fferent
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_def
ault
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
unicode data. This will make it collation independant.
PS Do you want to know why it went wrong or are you ok
with it ?
J
>--Original Message--
>Hi,
>I am running a query over 2 tables in 2 different
databases.
>I get the following error "Cannot resolve collation
conflict for equal to
>operation"
>for example
>SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].
[dbo].[TB1] [TB1]
>INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product]=[/vbc
ol]
[TB2].[ProductCode])[vbcol=seagreen]
>WHERE [TB2].[ProdGroup]=@.PG
>@.PG is a string parameter.
>Regards
>Tim
>
>.
>Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])|||Your [TB1].[Product] and [TB2].[ProductCode] columns have di
fferent
collations, so the result of the join expression is ambiguous. Take a
look at the BOL topic "Collation Precedence" -- it provides a good
explanation of the problem. You can avoid this fairly trivially by
providing a COLLATE clause that removes the ambiguity like this:
SELECT TOP 100 [TB1].[Product] AS Q0000000
FROM ( [BD1].[dbo].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2]
ON [TB1].[Product]=[TB2].[ProductCode]) COLLATE database_def
ault
WHERE [TB2].[ProdGroup]=@.PG
but this will make it impossible for the QP to use an index seek on the
right side of the join. If this is a big problem it may be better to
change the collation of one of the two columns (using ALTER TABLE ALTER
COLUMN) so that the collations match. Note that to run ALTER COLUMN on a
column's collation you must first drop any indexes, stats, or constraints
that reference the column.
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
From: "Tim Marsden" <TM@.UK.COM>
References: <e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl>
<11c6101c4423b$568580b0$a301280a@.phx.gbl>
Subject: Re: Collation Conflict
Date: Tue, 25 May 2004 14:49:31 +0100
Lines: 46
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <OC3oY8lQEHA.3988@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: host213-122-182-242.in-addr.btopenworld.com
213.122.182.242
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp
13.phx.gbl
Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.server:342917
X-Tomcat-NG: microsoft.public.sqlserver.server
Many Thanks
Please could you explain why it when wrong, and give me an example of
unicodes joins.
Regards
Tim
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:11c6101c4423b$568580b0$a301280a@.phx
.gbl...[vbcol=seagreen]
> Modify your query so that it converts your joins to
> unicode data. This will make it collation independant.
> PS Do you want to know why it went wrong or are you ok
> with it ?
> J
>
> databases.
> conflict for equal to
> [dbo].[TB1] [TB1]
> [TB2].[ProductCode])
Collation Conflict
Hi,
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo
].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product
]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimYour TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[d
bo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Produ
ct]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
I am running a query over 2 tables in 2 different databases.
I get the following error "Cannot resolve collation conflict for equal to
operation"
for example
SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[dbo
].[TB1] [TB1]
INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Product
]=[TB2].[ProductCode])
WHERE [TB2].[ProdGroup]=@.PG
@.PG is a string parameter.
Regards
TimYour TEMPDB collation differs to your database,
You either need to preform Julie option or do a rebuildm to the correct
collation but this would wipe out your users, and user databases (you can
reattached).
J
"Tim Marsden" <TM@.UK.COM> wrote in message
news:e0iKaZjQEHA.132@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am running a query over 2 tables in 2 different databases.
> I get the following error "Cannot resolve collation conflict for equal to
> operation"
> for example
> SELECT TOP 100 [TB1].[Product] AS Q0000000 FROM ( [BD1].[d
bo].[TB1] [TB1]
> INNER JOIN [DB2].[dbo].[TB2] [TB2] ON [TB1].[Produ
ct]=[TB2].[ProductCode])
> WHERE [TB2].[ProdGroup]=@.PG
> @.PG is a string parameter.
> Regards
> Tim
>
Sunday, March 11, 2012
CogInStr function fails with MSAS 2005
Hi,
Following MDX query works with MSAS 2000 but fails with MSAS 2005:
SELECT
{ FILTER([Product].AllMembers, CogInStr([Product].CurrentMember.Name,"*", 0) > 0) }
ON AXIS(0)
FROM [Sales]
Following error is thrown:
[ 1] Microsoft OLE DB Provider for Analysis S: Query (2, 32) The '[CogInStr]' function does not exist.
Any pointers on what needs to be done in order to support this query in MSAS 2005 would be appreciated.
Thanks and Regards,
Santosh
CogInStr - was not the built in funtion in AS2000, and it is not a built in function in AS2005 either. I suspect in your deployment somebody implemented custom UDF function with such name. You will need to implement it as a stored procedure in AS2005.
|||
Thanks for your response.
Yes, in AS2000 it was implemented as a UDF as shown below:
command = USE LIBRARY "CogUdf.CogRExp"
command = SELECT
{ FILTER([Product].AllMembers, CogInStr([Product].CurrentMember.Name,"*", 0) > 0) }
ON AXIS(0)
FROM [Sales]
Can you please give me some pointers (possible code snippets) on how I can convert this to a stored procedure in AS2005.
Thanks in advance,
Santosh.
Please read BOL on the subject of stored procedures. Once they are deployed to the server, there is no need to do USE LIBRARY command anymore. For the extensive library of custom sprocs, you can check the following CodePlex project: http://www.codeplex.com/ASStoredProcedures
HTH,
Mosha (http://www.mosha.com/msolap)
|||The samples given in codeplex.com have been written in C#. Can stored procedures be implemented using OLE db for olap interfaces?|||You are not asking the right question. Take a look at the implementation of CogInStr - it doesn't use OLEDB for OLAP. You can easily replicate its functionality on C# or VB.NET or any other CLR language.|||Sorry for the confusion.
I was trying to use the ASSP project with my MSAS 2005. I registered ASSP.dll under server assembly as a COM DLL type of assembly.
Later when I tried to run the following MDX query against the Adventure Works DW sample database, I am getting following error:
Query (1, 30) The '[ASSP].[FindCurrentMemberVerbose]' function does not exist.
Is there a link which demonstrate the use of these stored procedures with MSAS2005 and how to register it under Server/Database assemblies?
Thanks and Regards,
Santosh.
I was able to use the Stored Procedure finally with MSAS 2005.
Thanks,
Santosh.
Co-exist of SQL 2000 and 2005
Dear all, 1. Click Start, click Run, type regedit, and then click OK. 2. Locate the following registry subkey: 3. Right-click the ClientSetup registry subkey, point to New, and then click String Value. 4. Rename the registry subkey that you just created SqlPath, and then press ENTER. 5. Double-click the SqlPath registry subkey, and then type C:\Program Files\Microsoft SQL Server\80\Tools in the Value data box. 6. Click OK, and then close Registry Editor. 7. Click Start, click Run, type regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll", and then click OK.
I have SQL 2000 Enterprise mgr and Query Analyzer in my desktop, and I install SQL2005 Express Edition + Mgm Studio today, however, my SQL 2000 Enterprise mgr disappear and all my registered servers disappear too when I open up the SQL 2005 Mgm Studio. Is it mean that I no longer able to use SQL 2000? How can I rollback the installation of SQL 2005 because I still have to use Enterprise mgr to manage my existing database and clients. (Yes, I give up SQL 2005 now)
Thanks,
The 2 should work side by side just fine, there is no need to uninstall. What do you mean Enterprise Manager disapears? Did you do a sidebyside install or an upgrade?|||
You say 'when I open up 2005 Management Studio..'
If you did a sidebyside install, all your 2000 stuff is till there, but you still use 2000 EM for them, they aren't in Management Studio. Your old EM should still be available from the programs menu, and 2005 have it's own start menu.
/Kenneth
|||Hi Ken,Thanks for your advice, Maybe I select "upgrade" SQL 2000 to SQL 2005 in the installation process (is there an option like this?)... so everything gone and I can't see my SQL 2000 Enterprice Manaager and Query Analyzer (you know, these 2 tools are the best friend of DBA ;)
I tried to uninstall SQL 2005 and select "side by side" installation (is there a option like this?) but it create another problem: here.
My original database in SQL 2000 disappear even I re-install SQL 2000 EM, but the MDF and LDF are still in my machine, so I want to "attach" these database immediately, but I can't do so.. do you know why?
(Installation of SQL 2005 is quite problematic... and bring some other problems as well in my opinion.. I can use my EM and QA smoothly over the last few years)
Thanks!
|||So it sounds like you did an upgrade to SQL 2005. For the tools you can uninstall 2005 and then re-install 2000 and it should work, HOWEVER for your data this is a problem. Once a database has been touched by a 2005 server it can not be attached or restored on a 2000 machine. Your only choice is to export the data from a 2005 running copy of the database, out to csv/text etc and then load that on on the other side.|||
Oh my god... I just un-install the SQL 2005 and re-install SQL 2000, that's fine. I want to make everything back to normal first.
Next, I install a lite version of SQL 2005 Express instead (Yes, I'm afraid the SQL 2005 now), and I specifically choose NOT upgrade my existing database (Well, the upgrade option is disabled by default due to the limitation of SQL 2005 Express anyway, so I'm pretty sure it WON'T affect my existing SQL 2000 DBs and Enterprise Mgr, alright?)
However, the result is - It break all of my databases AGAIN. And an error message come out when I try to open my SQL 2000 Ent. Mgr:
Microsoft Management Console
Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
I'm pretty sure the co-existence of SQL 2000 and SQL 2005 (any edition) is very poor.. I can't do any project against my SQL 2000 nor SQL 20005 db now. :(
Thanks,
|||Sorry but you are wrong, SQL 2000 and 2005 work just fine together, it is unfortunate that you are having problems, btu I run 4 machiens with this config and they all work fine, as do 10,000s of others.
What do you mean the databases are broken, is there an error message?
To fix the mmc problem try and run regsvr32 on sqlmmc.dll
|||I'm also running a few servers running both SQL2k and 2005 without problems. Servers did not upgrade the existing sql2k 's. I installed the 2005's as new instances.
Eralper
http://www.kodyaz.com
|||Euan Garden wrote:
To fix the mmc problem try and run regsvr32 on sqlmmc.dll
I have more than 1 machine with SQL 2005 installed, and nearly 50% of them are facing various problem. I understand that there're 10000s installation successed, but you should be aware that there're also have another 10000s installation failed and looking for help here or other NGs everyday. This is a pain. Even 1 installation failed, this product is buggy.
The databases can't be attached or re-used anymore if SQL 2005 "touch" the db from SQL 2000, that's a famous bug since beta age.
The regsvr32 sqlmmc.dll and sqldmo.dll are no good...
Thanks,
|||Sorry peter but there are not 1000's of installations which are failed.
Correct once a DB is touched by 2005 it can not be moved back, this is not a bug this is by design, we have to upgrade the format to be able to use the new features of SQL 2000, this has been this way for the last 4 versions of SQL Server and I believe is also true for other DB vendors.
When you say that sqlmmc and sqldmo are no good are you saying that you tried to re-reg and ti did not work?
|||We have to buy all license and upgrade to SQL 2005 immmediately and we don't have any backward compatiability if we're using SQL 2000? Compatiability and stability are two of the key factors for any DB system.
re-reg doesn't help, SQL 2000 Enterprise Manager still messed up.
(Please see this MSDN blog for more info about this bug)
Thanks,
|||First of all you don't have to buy new licenses and upgrade, SQL 2000 is a fine product and will be support for several years to come. However if you want to upgrade and get the new version you can.
Your definition of Back Compat differs from mine (and that of the SQL Server Team), apps that work against SQL2000/7 should work against SQL 2005 without changes, thats significant. SQL 2005 can used SQL 2000 as a linked server, it can consume backups, database files and data via OLE DB and ADO.Net. All of that is pretty impressive.
Your issue is that you have taken a SQL Server 2000 DB, and attached it to SQL 2005, obviously wanting to leverage the new features of SQL 2005, to do that means changes to the file format, making it impossible to use the file on 2000(but you can script out) just as if you had used one of the new T-SQL commands in your app, that would not run against a 2000 server. Just like a Winforms 2.0 app will not run against .Net 1.1, just as a etc etc. I'm sorry you disagree with the principle, but this decision/position does not imply anything related to the stability of the system, which is just fine.
I am very familiar with the "bug" and the blog entry you reference(hint take a look at the author of the entry), this is not the same bug. Re-registering DMO fixes the CTP bug you reference.
Now back to the original problem of EM not working. When you re-reg sqlmmc and sqldmo to they both register correctly or is there an error message? If they register correctly has the error message you get when trying to start EM the same or has it changed from the original post? Have you tried applying SP4 of SQL 2000 again?
|||Sorry to post this bug here, but this is the problem after my installation of SQL 2005. I don't want to upgrade my SQL 2000 database to SQL 2005 in fact, if you read my posts above.The wizard drive me to this stage and now, the database can't be rollbacked to its original state, which is a backward process. Sorry that you have another definition of backward compatibility, but please be considerate when you reading post here, even you have your own definition in your own mind. You can't force others to follow your own definion and you don't have problem in the installation doesn't mean other 10000 people are as lucky as you.
The problem and error messages and all service pack installed. Please read the posts but don't just focus on what you like to see but ignore others' opinion.
Thanks,
|||
If you are playing around with
various versions or instances of MS SQL Server
you may hit the problem of
Microsoft Management Console
Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
To fix the problem,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
Good Luck,
Alfred.
|||well. . . unfortunately we have the same problem as well.
after having both SQL 2000 and 2005 we tried to uninstall 2005.
buggered the sql 2000 as noted
running the fix outlined above (as well as the othere method outlined in the kb article the above was copied from) we get:
DllRegisterServer in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll failed.
Return code was: 0x8011041a
We need to get back to a totally clean state so we can do an install of just SQL 2000.
And dont just copy that other kb article 290991 as that didnt work either
Co-exist of SQL 2000 and 2005
Dear all,1. Click Start, click Run, type regedit, and then click OK. 2. Locate the following registry subkey: 3. Right-click the ClientSetup registry subkey, point to New, and then click String Value. 4. Rename the registry subkey that you just created SqlPath, and then press ENTER. 5. Double-click the SqlPath registry subkey, and then type C:\Program Files\Microsoft SQL Server\80\Tools in the Value data box. 6. Click OK, and then close Registry Editor. 7. Click Start, click Run, type regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll", and then click OK.
I have SQL 2000 Enterprise mgr and Query Analyzer in my desktop, and I install SQL2005 Express Edition + Mgm Studio today, however, my SQL 2000 Enterprise mgr disappear and all my registered servers disappear too when I open up the SQL 2005 Mgm Studio. Is it mean that I no longer able to use SQL 2000? How can I rollback the installation of SQL 2005 because I still have to use Enterprise mgr to manage my existing database and clients. (Yes, I give up SQL 2005 now)
Thanks,The 2 should work side by side just fine, there is no need to uninstall. What do you mean Enterprise Manager disapears? Did you do a sidebyside install or an upgrade?|||
You say 'when I open up 2005 Management Studio..'
If you did a sidebyside install, all your 2000 stuff is till there, but you still use 2000 EM for them, they aren't in Management Studio. Your old EM should still be available from the programs menu, and 2005 have it's own start menu.
/Kenneth
|||Hi Ken,Thanks for your advice, Maybe I select "upgrade" SQL 2000 to SQL 2005 in the installation process (is there an option like this?)... so everything gone and I can't see my SQL 2000 Enterprice Manaager and Query Analyzer (you know, these 2 tools are the best friend of DBA ;)
I tried to uninstall SQL 2005 and select "side by side" installation (is there a option like this?) but it create another problem: here.
My original database in SQL 2000 disappear even I re-install SQL 2000 EM, but the MDF and LDF are still in my machine, so I want to "attach" these database immediately, but I can't do so.. do you know why?
(Installation of SQL 2005 is quite problematic... and bring some other problems as well in my opinion.. I can use my EM and QA smoothly over the last few years)
Thanks!|||So it sounds like you did an upgrade to SQL 2005. For the tools you can uninstall 2005 and then re-install 2000 and it should work, HOWEVER for your data this is a problem. Once a database has been touched by a 2005 server it can not be attached or restored on a 2000 machine. Your only choice is to export the data from a 2005 running copy of the database, out to csv/text etc and then load that on on the other side.|||
Oh my god... I just un-install the SQL 2005 and re-install SQL 2000, that's fine. I want to make everything back to normal first.
Next, I install a lite version of SQL 2005 Express instead (Yes, I'm afraid the SQL 2005 now), and I specifically choose NOT upgrade my existing database (Well, the upgrade option is disabled by default due to the limitation of SQL 2005 Express anyway, so I'm pretty sure it WON'T affect my existing SQL 2000 DBs and Enterprise Mgr, alright?)
However, the result is - It break all of my databases AGAIN. And an error message come out when I try to open my SQL 2000 Ent. Mgr:
Microsoft Management Console
Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
I'm pretty sure the co-existence of SQL 2000 and SQL 2005 (any edition) is very poor.. I can't do any project against my SQL 2000 nor SQL 20005 db now. :(
Thanks,
|||Sorry but you are wrong, SQL 2000 and 2005 work just fine together, it is unfortunate that you are having problems, btu I run 4 machiens with this config and they all work fine, as do 10,000s of others.
What do you mean the databases are broken, is there an error message?
To fix the mmc problem try and run regsvr32 on sqlmmc.dll
|||I'm also running a few servers running both SQL2k and 2005 without problems. Servers did not upgrade the existing sql2k 's. I installed the 2005's as new instances.
Eralper
http://www.kodyaz.com
|||
Euan Garden wrote:
To fix the mmc problem try and run regsvr32 on sqlmmc.dll
I have more than 1 machine with SQL 2005 installed, and nearly 50% of them are facing various problem. I understand that there're 10000s installation successed, but you should be aware that there're also have another 10000s installation failed and looking for help here or other NGs everyday. This is a pain. Even 1 installation failed, this product is buggy.
The databases can't be attached or re-used anymore if SQL 2005 "touch" the db from SQL 2000, that's a famous bug since beta age.
The regsvr32 sqlmmc.dll and sqldmo.dll are no good...
Thanks,
|||Sorry peter but there are not 1000's of installations which are failed.
Correct once a DB is touched by 2005 it can not be moved back, this is not a bug this is by design, we have to upgrade the format to be able to use the new features of SQL 2000, this has been this way for the last 4 versions of SQL Server and I believe is also true for other DB vendors.
When you say that sqlmmc and sqldmo are no good are you saying that you tried to re-reg and ti did not work?
|||We have to buy all license and upgrade to SQL 2005 immmediately and we don't have any backward compatiability if we're using SQL 2000? Compatiability and stability are two of the key factors for any DB system.
re-reg doesn't help, SQL 2000 Enterprise Manager still messed up.
(Please see this MSDN blog for more info about this bug)
Thanks,
|||First of all you don't have to buy new licenses and upgrade, SQL 2000 is a fine product and will be support for several years to come. However if you want to upgrade and get the new version you can.
Your definition of Back Compat differs from mine (and that of the SQL Server Team), apps that work against SQL2000/7 should work against SQL 2005 without changes, thats significant. SQL 2005 can used SQL 2000 as a linked server, it can consume backups, database files and data via OLE DB and ADO.Net. All of that is pretty impressive.
Your issue is that you have taken a SQL Server 2000 DB, and attached it to SQL 2005, obviously wanting to leverage the new features of SQL 2005, to do that means changes to the file format, making it impossible to use the file on 2000(but you can script out) just as if you had used one of the new T-SQL commands in your app, that would not run against a 2000 server. Just like a Winforms 2.0 app will not run against .Net 1.1, just as a etc etc. I'm sorry you disagree with the principle, but this decision/position does not imply anything related to the stability of the system, which is just fine.
I am very familiar with the "bug" and the blog entry you reference(hint take a look at the author of the entry), this is not the same bug. Re-registering DMO fixes the CTP bug you reference.
Now back to the original problem of EM not working. When you re-reg sqlmmc and sqldmo to they both register correctly or is there an error message? If they register correctly has the error message you get when trying to start EM the same or has it changed from the original post? Have you tried applying SP4 of SQL 2000 again?
|||Sorry to post this bug here, but this is the problem after my installation of SQL 2005. I don't want to upgrade my SQL 2000 database to SQL 2005 in fact, if you read my posts above.The wizard drive me to this stage and now, the database can't be rollbacked to its original state, which is a backward process. Sorry that you have another definition of backward compatibility, but please be considerate when you reading post here, even you have your own definition in your own mind. You can't force others to follow your own definion and you don't have problem in the installation doesn't mean other 10000 people are as lucky as you.
The problem and error messages and all service pack installed. Please read the posts but don't just focus on what you like to see but ignore others' opinion.
Thanks,|||
If you are playing around with
various versions or instances of MS SQL Server
you may hit the problem of
Microsoft Management Console
Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
To fix the problem,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
Good Luck,
Alfred.
|||well. . . unfortunately we have the same problem as well.
after having both SQL 2000 and 2005 we tried to uninstall 2005.
buggered the sql 2000 as noted
running the fix outlined above (as well as the othere method outlined in the kb article the above was copied from) we get:
DllRegisterServer in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll failed.
Return code was: 0x8011041a
We need to get back to a totally clean state so we can do an install of just SQL 2000.
And dont just copy that other kb article 290991 as that didnt work either
Co-exist of SQL 2000 and 2005
Dear all,1. Click Start, click Run, type regedit, and then click OK. 2. Locate the following registry subkey: 3. Right-click the ClientSetup registry subkey, point to New, and then click String Value. 4. Rename the registry subkey that you just created SqlPath, and then press ENTER. 5. Double-click the SqlPath registry subkey, and then type C:\Program Files\Microsoft SQL Server\80\Tools in the Value data box. 6. Click OK, and then close Registry Editor. 7. Click Start, click Run, type regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll", and then click OK.
I have SQL 2000 Enterprise mgr and Query Analyzer in my desktop, and I install SQL2005 Express Edition + Mgm Studio today, however, my SQL 2000 Enterprise mgr disappear and all my registered servers disappear too when I open up the SQL 2005 Mgm Studio. Is it mean that I no longer able to use SQL 2000? How can I rollback the installation of SQL 2005 because I still have to use Enterprise mgr to manage my existing database and clients. (Yes, I give up SQL 2005 now)
Thanks,The 2 should work side by side just fine, there is no need to uninstall. What do you mean Enterprise Manager disapears? Did you do a sidebyside install or an upgrade?|||
You say 'when I open up 2005 Management Studio..'
If you did a sidebyside install, all your 2000 stuff is till there, but you still use 2000 EM for them, they aren't in Management Studio. Your old EM should still be available from the programs menu, and 2005 have it's own start menu.
/Kenneth
|||Hi Ken,Thanks for your advice, Maybe I select "upgrade" SQL 2000 to SQL 2005 in the installation process (is there an option like this?)... so everything gone and I can't see my SQL 2000 Enterprice Manaager and Query Analyzer (you know, these 2 tools are the best friend of DBA ;)
I tried to uninstall SQL 2005 and select "side by side" installation (is there a option like this?) but it create another problem: here.
My original database in SQL 2000 disappear even I re-install SQL 2000 EM, but the MDF and LDF are still in my machine, so I want to "attach" these database immediately, but I can't do so.. do you know why?
(Installation of SQL 2005 is quite problematic... and bring some other problems as well in my opinion.. I can use my EM and QA smoothly over the last few years)
Thanks!|||So it sounds like you did an upgrade to SQL 2005. For the tools you can uninstall 2005 and then re-install 2000 and it should work, HOWEVER for your data this is a problem. Once a database has been touched by a 2005 server it can not be attached or restored on a 2000 machine. Your only choice is to export the data from a 2005 running copy of the database, out to csv/text etc and then load that on on the other side.|||
Oh my god... I just un-install the SQL 2005 and re-install SQL 2000, that's fine. I want to make everything back to normal first.
Next, I install a lite version of SQL 2005 Express instead (Yes, I'm afraid the SQL 2005 now), and I specifically choose NOT upgrade my existing database (Well, the upgrade option is disabled by default due to the limitation of SQL 2005 Express anyway, so I'm pretty sure it WON'T affect my existing SQL 2000 DBs and Enterprise Mgr, alright?)
However, the result is - It break all of my databases AGAIN. And an error message come out when I try to open my SQL 2000 Ent. Mgr:
Microsoft Management Console
Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
I'm pretty sure the co-existence of SQL 2000 and SQL 2005 (any edition) is very poor.. I can't do any project against my SQL 2000 nor SQL 20005 db now. :(
Thanks,
|||Sorry but you are wrong, SQL 2000 and 2005 work just fine together, it is unfortunate that you are having problems, btu I run 4 machiens with this config and they all work fine, as do 10,000s of others.
What do you mean the databases are broken, is there an error message?
To fix the mmc problem try and run regsvr32 on sqlmmc.dll
|||I'm also running a few servers running both SQL2k and 2005 without problems. Servers did not upgrade the existing sql2k 's. I installed the 2005's as new instances.
Eralper
http://www.kodyaz.com
|||
To fix the mmc problem try and run regsvr32 on sqlmmc.dll
I have more than 1 machine with SQL 2005 installed, and nearly 50% of them are facing various problem. I understand that there're 10000s installation successed, but you should be aware that there're also have another 10000s installation failed and looking for help here or other NGs everyday. This is a pain. Even 1 installation failed, this product is buggy.
The databases can't be attached or re-used anymore if SQL 2005 "touch" the db from SQL 2000, that's a famous bug since beta age.
The regsvr32 sqlmmc.dll and sqldmo.dll are no good...
Thanks,
|||Sorry peter but there are not 1000's of installations which are failed.
Correct once a DB is touched by 2005 it can not be moved back, this is not a bug this is by design, we have to upgrade the format to be able to use the new features of SQL 2000, this has been this way for the last 4 versions of SQL Server and I believe is also true for other DB vendors.
When you say that sqlmmc and sqldmo are no good are you saying that you tried to re-reg and ti did not work?
|||We have to buy all license and upgrade to SQL 2005 immmediately and we don't have any backward compatiability if we're using SQL 2000? Compatiability and stability are two of the key factors for any DB system.
re-reg doesn't help, SQL 2000 Enterprise Manager still messed up.
(Please see this MSDN blog for more info about this bug)
Thanks,
|||First of all you don't have to buy new licenses and upgrade, SQL 2000 is a fine product and will be support for several years to come. However if you want to upgrade and get the new version you can.
Your definition of Back Compat differs from mine (and that of the SQL Server Team), apps that work against SQL2000/7 should work against SQL 2005 without changes, thats significant. SQL 2005 can used SQL 2000 as a linked server, it can consume backups, database files and data via OLE DB and ADO.Net. All of that is pretty impressive.
Your issue is that you have taken a SQL Server 2000 DB, and attached it to SQL 2005, obviously wanting to leverage the new features of SQL 2005, to do that means changes to the file format, making it impossible to use the file on 2000(but you can script out) just as if you had used one of the new T-SQL commands in your app, that would not run against a 2000 server. Just like a Winforms 2.0 app will not run against .Net 1.1, just as a etc etc. I'm sorry you disagree with the principle, but this decision/position does not imply anything related to the stability of the system, which is just fine.
I am very familiar with the "bug" and the blog entry you reference(hint take a look at the author of the entry), this is not the same bug. Re-registering DMO fixes the CTP bug you reference.
Now back to the original problem of EM not working. When you re-reg sqlmmc and sqldmo to they both register correctly or is there an error message? If they register correctly has the error message you get when trying to start EM the same or has it changed from the original post? Have you tried applying SP4 of SQL 2000 again?
|||Sorry to post this bug here, but this is the problem after my installation of SQL 2005. I don't want to upgrade my SQL 2000 database to SQL 2005 in fact, if you read my posts above.The wizard drive me to this stage and now, the database can't be rollbacked to its original state, which is a backward process. Sorry that you have another definition of backward compatibility, but please be considerate when you reading post here, even you have your own definition in your own mind. You can't force others to follow your own definion and you don't have problem in the installation doesn't mean other 10000 people are as lucky as you.
The problem and error messages and all service pack installed. Please read the posts but don't just focus on what you like to see but ignore others' opinion.
Thanks,|||
If you are playing around with
various versions or instances of MS SQL Server
you may hit the problem of
Microsoft Management Console
Snap-in failed to initialize.
Name:<unknown>
CLSID:{00100100-1816-11D0-8EF5-00AA0062C58F}
To fix the problem,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
Good Luck,
Alfred.
|||well. . . unfortunately we have the same problem as well.
after having both SQL 2000 and 2005 we tried to uninstall 2005.
buggered the sql 2000 as noted
running the fix outlined above (as well as the othere method outlined in the kb article the above was copied from) we get:
DllRegisterServer in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmmc.dll failed.
Return code was: 0x8011041a
We need to get back to a totally clean state so we can do an install of just SQL 2000.
And dont just copy that other kb article 290991 as that didnt work either
Wednesday, March 7, 2012
Cocurrency
In a web application I am receiving following error message while trying to do the same database query from multiple sessions simultaneouslly. "The Connection is already open (state = connecting)." This only happens for simultaneous hits. Is it a common scenario of cocurrency or I am getting something unusual here? If its a common issue what should be the workaround?
TIA
Hemangyou should NEVER be trying to share an established connection.
each user/request should create its own connection object for accessing the db.|||Hi,
I already took care of not sharring the connection.
Anyways I found where the error was. It was just a programming error somewhere along the lines, it was nothing related to concurrency but i found it while testing it and so i missunderstood that. I fixed that up. Thanks for your interest and prompt reply.
Hemang
cobol -> SQL2005
I'm not "cobol person" but now, I have to bind a cobol application (from mainframe) to query SQL 2005
Have somebody had this task?No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.
-PatP|||No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.
-PatP
Thanks Pat
I try to find communication between Mainframe and PC db throgh ODBC :).
This cobol-programm never have been running to bind to the PC db - it is working with mainframe DB2. This is tamporary deal (we are going from DB2-to SQL2005) I could write this from PC using VS C# for example and hit mainframe, but I have to save even interface of this "stupid" programm :).
thanks again|||how about "Connect Direct" or "Direct Connect" ?|||wath do you mean?
Actually, now I'm studing how to do this for IBM CICS environment that uses EXEC SQL CONNECT to DB2 to change to SQL2005
Have somebody had this task?No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.
-PatP|||No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.
-PatP
Thanks Pat
I try to find communication between Mainframe and PC db throgh ODBC :).
This cobol-programm never have been running to bind to the PC db - it is working with mainframe DB2. This is tamporary deal (we are going from DB2-to SQL2005) I could write this from PC using VS C# for example and hit mainframe, but I have to save even interface of this "stupid" programm :).
thanks again|||how about "Connect Direct" or "Direct Connect" ?|||wath do you mean?
Actually, now I'm studing how to do this for IBM CICS environment that uses EXEC SQL CONNECT to DB2 to change to SQL2005
Coalescing data from more than 2 tables
I have a query, below, -Can anyone tell me how I could modify this to
add data from third and fourth tables. Many Thanks
----
select
coalesce (x.NHPART, y.TPROD)
, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
, isnull(y.TQTY,0) as "TQTY (ITH)"
from
(
select
NHPART
, isnull(sum (NCQNTY),0) NCQNTY
from
NCM
WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
='5220'
group by
NHPART
)
x full join
(
select
TPROD
, isnull(sum (TQTY),0) TQTY
from
ITH
WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
group by
TPROD
) y on x.NHPART = y.TPROD
ORDER BY coalesce (x.NHPART, y.TPROD)
----By the time I get to a three way (or worse, four way) full outer join
I find all the equality tests start to get overwhelming - to the point
I have found it hard to be sure my code is written properly.
Eventually I found an alternative way to code them that I, at least,
find much simpler.
I start by writing a query that UNIONs together just the keys - which
is to say the columns used for the IN clause comparisons - from all
the data sources.
select NHPART
from NCM
where NHSET ='INSP'
and NHCDAT between 20060201
and 20060228
and HVNDR >='5220'
UNION
select TPROD
from ITH
WHERE TTDTE between 20060201
and 20060228
and TVEND ='5220'
UNION
select OTHERPROD
from OTHERTBL
WHERE OTHERCOLUMN = 'test value'
Note that UNION performs a DISTINCT, which we need.
Now, with that working, I use that as a derived table, and make it the
root table in a LEFT outer join rather than a FULL outer join:
select K.EitherPart,
coalesce(X.NCQNTY,0) as "NCQNTY (NCM)",
coalesce(Y.TQTY,0) as "TQTY (ITH)",
coalesce(Z.SOMENUMBER) as OtherQty
from (<query from above> ) as K
LEFT OUTER
JOIN (<your first derived table> ) as X
ON K.EitherPart = X.NHPART
LEFT OUTER
JOIN (<your second derived table> ) as Y
ON K.EitherPart = Y.TPROD
LEFT OUTER
JOIN (<yet another derived table> ) as Z
ON K.EitherPart = Z.OTHERPROD
I hope this helps.
Roy Harvey
Beacon Falls, CT
On 7 Mar 2006 03:16:37 -0800, "philipbennett25" <pbennett@.xyratex.com>
wrote:
>I have a query, below, -Can anyone tell me how I could modify this to
>add data from third and fourth tables. Many Thanks
>----
>select
> coalesce (x.NHPART, y.TPROD)
>, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
>, isnull(y.TQTY,0) as "TQTY (ITH)"
>from
>(
>select
> NHPART
>, isnull(sum (NCQNTY),0) NCQNTY
>from
> NCM
>WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
>='5220'
>group by
> NHPART
> )
>x full join
>(
>select
> TPROD
>, isnull(sum (TQTY),0) TQTY
>from
> ITH
>WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
>group by
> TPROD
> ) y on x.NHPART = y.TPROD
>ORDER BY coalesce (x.NHPART, y.TPROD)
>----
add data from third and fourth tables. Many Thanks
----
select
coalesce (x.NHPART, y.TPROD)
, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
, isnull(y.TQTY,0) as "TQTY (ITH)"
from
(
select
NHPART
, isnull(sum (NCQNTY),0) NCQNTY
from
NCM
WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
='5220'
group by
NHPART
)
x full join
(
select
TPROD
, isnull(sum (TQTY),0) TQTY
from
ITH
WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
group by
TPROD
) y on x.NHPART = y.TPROD
ORDER BY coalesce (x.NHPART, y.TPROD)
----By the time I get to a three way (or worse, four way) full outer join
I find all the equality tests start to get overwhelming - to the point
I have found it hard to be sure my code is written properly.
Eventually I found an alternative way to code them that I, at least,
find much simpler.
I start by writing a query that UNIONs together just the keys - which
is to say the columns used for the IN clause comparisons - from all
the data sources.
select NHPART
from NCM
where NHSET ='INSP'
and NHCDAT between 20060201
and 20060228
and HVNDR >='5220'
UNION
select TPROD
from ITH
WHERE TTDTE between 20060201
and 20060228
and TVEND ='5220'
UNION
select OTHERPROD
from OTHERTBL
WHERE OTHERCOLUMN = 'test value'
Note that UNION performs a DISTINCT, which we need.
Now, with that working, I use that as a derived table, and make it the
root table in a LEFT outer join rather than a FULL outer join:
select K.EitherPart,
coalesce(X.NCQNTY,0) as "NCQNTY (NCM)",
coalesce(Y.TQTY,0) as "TQTY (ITH)",
coalesce(Z.SOMENUMBER) as OtherQty
from (<query from above> ) as K
LEFT OUTER
JOIN (<your first derived table> ) as X
ON K.EitherPart = X.NHPART
LEFT OUTER
JOIN (<your second derived table> ) as Y
ON K.EitherPart = Y.TPROD
LEFT OUTER
JOIN (<yet another derived table> ) as Z
ON K.EitherPart = Z.OTHERPROD
I hope this helps.
Roy Harvey
Beacon Falls, CT
On 7 Mar 2006 03:16:37 -0800, "philipbennett25" <pbennett@.xyratex.com>
wrote:
>I have a query, below, -Can anyone tell me how I could modify this to
>add data from third and fourth tables. Many Thanks
>----
>select
> coalesce (x.NHPART, y.TPROD)
>, isnull(x.NCQNTY,0) as "NCQNTY (NCM)"
>, isnull(y.TQTY,0) as "TQTY (ITH)"
>from
>(
>select
> NHPART
>, isnull(sum (NCQNTY),0) NCQNTY
>from
> NCM
>WHERE NHSET ='INSP' and NHCDAT between 20060201 and 20060228 and NHVNDR
>='5220'
>group by
> NHPART
> )
>x full join
>(
>select
> TPROD
>, isnull(sum (TQTY),0) TQTY
>from
> ITH
>WHERE TTDTE between 20060201 and 20060228 and TVEND ='5220'
>group by
> TPROD
> ) y on x.NHPART = y.TPROD
>ORDER BY coalesce (x.NHPART, y.TPROD)
>----
Coalesce increasing performance but Why?
This past w
end I tried out a tool called Toad for Sql and it had a query
optimizer in it. I tossed in my slow performing query, walked away and 2
hours later had 50+ alternative queries.
One of the results had a 99.83% improvement. Logical Reads went from
62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
now took 35 seconds.
There were 5 left joins in the query. It simple replaced two of them (see
below), everything else stayed the same. im very happy with the results but
Id like to understand why COALESCE would make the query run almost 100%
faster.
LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
ac.boardID)
and
LEFT OUTER JOIN question_std_answer qsa
ON (qsa.cobrandID=ac.cobrandID
AND qsa.masterID=ac.masterID
AND qsa.accountID=ac.accountID
AND qsa.positionID=ac.positionID
AND qsa.jsrUserID=ac.jsrUserID
AND qsa.questionID=10)
with
LEFT OUTER JOIN question_std_answer qsa
ON qsa.cobrandID = ac.cobrandID
AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
AND qsa.accountID = ac.accountID
AND qsa.positionID = ac.positionID
AND qsa.jsrUserID = ac.jsrUserID
AND qsa.questionID = 10I'm not sure but I am guessing that the use of coalesce negated the indexes
that were previously being used, allowing different indexes to be used.
Have you tried recreating statistics and comparing the two queries again?
It sounds like SQL server is making a mistake in its chosen plan, but using
this function is removing an option and making it default to a muich better
plan.
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past wend I tried out a tool called Toad for Sql and it had a
query
> optimizer in it. I tossed in my slow performing query, walked away and 2
> hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
but
> Id like to understand why COALESCE would make the query run almost 100%
> faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||I ran many times testing it against different date ranges always starting wi
th:
CHECKPOINT -- write dirty pages from data cache to disk
DBCC DROPCLEANBUFFERS -- clear the data cache
DBCC FREEPROCCACHE -- clear the procedure cache
Everytime is performed essentially the same give or take a second or two.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message news:etU6gJ7OGHA.916@.T
K2MSFTNGP10.phx.gbl...
> I'm not sure but I am guessing that the use of coalesce negated the indexe
s
> that were previously being used, allowing different indexes to be used.
> Have you tried recreating statistics and comparing the two queries again?
> It sounds like SQL server is making a mistake in its chosen plan, but usin
g
> this function is removing an option and making it default to a muich bette
r
> plan.
>
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> query
> but
>
>|||What does the Execution Plan tell you?
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past wend I tried out a tool called Toad for Sql and it had a
> query optimizer in it. I tossed in my slow performing query, walked away
> and 2 hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
> but Id like to understand why COALESCE would make the query run almost
> 100% faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||Thanks for replying JT ... Im by no means a DBA .. what should I focus on
when looking at the Execution Plan?
Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> What does the Execution Plan tell you?
> http://msdn.microsoft.com/library/d...>
n_1_5pde.asp
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server does not execute SQL. It interprets SQL, compiles an execution
plan, and then uses that plan to perform a sequence of physical operations
such as index scans, joins, spooling, etc. You can deterministically compare
the design merits of one query versus another similar query by studying
their execution plan. For example, does one query perform a full table scan
while an alternative query perform a more efficient index scan?
Below are a few articles describing in more detail how to gain useful
information from execution plans:
SQL Tuning Tutorial - Understanding a Database Execution Plan (1)
http://www.codeproject.com/cs/datab...-tutorial-1.asp
How to Select Indexes for Your SQL Server Tables
http://www.sql-server-performance.com/mr_indexing.asp
SQL Server Query Execution Plan Analysis
http://www.sql-server-performance.c...an_analysis.asp
"Brian" <brian@.nospam.com> wrote in message
news:eZz$s18OGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks for replying JT ... Im by no means a DBA .. what should I focus on
> when looking at the Execution Plan?
> Thanks!
> "JT" <someone@.microsoft.com> wrote in message
> news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I would guess that this is based on the use of
coalesce to force the use of an index.This is
simply a *trick* that was put forward by
'Umachandar Jayachandran' then an MVP.The idea is
that an index can be forced for any data type
by using the datatype boundries.Although a boundry
is not being used here the construct is probably
enough to force the use of an index.
I don't think this trick is talked about in a kb
for obvious reasons:)
I refer the interested reader to these threads:
http://tinyurl.com/rpa2x
http://tinyurl.com/n2oql
http://tinyurl.com/ns9aq
http://tinyurl.com/n5v4y
$.02 from
www.rac4sql.net|||No, this is a different trick. This trick will have the result that the
optimizer will not consider certain (underperforming) access paths. The
trick that Umachandar introduced is not used for joins, but for
filtering expressions (i.e. the WHERE clause).
Gert-Jan
05ponyGT wrote:
> I would guess that this is based on the use of
> coalesce to force the use of an index.This is
> simply a *trick* that was put forward by
> 'Umachandar Jayachandran' then an MVP.The idea is
> that an index can be forced for any data type
> by using the datatype boundries.Although a boundry
> is not being used here the construct is probably
> enough to force the use of an index.
> I don't think this trick is talked about in a kb
> for obvious reasons:)
> I refer the interested reader to these threads:
> http://tinyurl.com/rpa2x
> http://tinyurl.com/n2oql
> http://tinyurl.com/ns9aq
> http://tinyurl.com/n5v4y
> $.02 from
> www.rac4sql.net|||This comes as a shock as I'm so rarely wrong:(
Wonder why we use the term *trick* and not *kludge* :)
Anyway this is just another illustration of turning *what* not *how*
on its head.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:440499B7.72531652@.toomuchspamalready.nl...
> No, this is a different trick. This trick will have the result that the
> optimizer will not consider certain (underperforming) access paths. The
> trick that Umachandar introduced is not used for joins, but for
> filtering expressions (i.e. the WHERE clause).
> Gert-Jan
>
> 05ponyGT wrote:
end I tried out a tool called Toad for Sql and it had a queryoptimizer in it. I tossed in my slow performing query, walked away and 2
hours later had 50+ alternative queries.
One of the results had a 99.83% improvement. Logical Reads went from
62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
now took 35 seconds.
There were 5 left joins in the query. It simple replaced two of them (see
below), everything else stayed the same. im very happy with the results but
Id like to understand why COALESCE would make the query run almost 100%
faster.
LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
ac.boardID)
and
LEFT OUTER JOIN question_std_answer qsa
ON (qsa.cobrandID=ac.cobrandID
AND qsa.masterID=ac.masterID
AND qsa.accountID=ac.accountID
AND qsa.positionID=ac.positionID
AND qsa.jsrUserID=ac.jsrUserID
AND qsa.questionID=10)
with
LEFT OUTER JOIN question_std_answer qsa
ON qsa.cobrandID = ac.cobrandID
AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
AND qsa.accountID = ac.accountID
AND qsa.positionID = ac.positionID
AND qsa.jsrUserID = ac.jsrUserID
AND qsa.questionID = 10I'm not sure but I am guessing that the use of coalesce negated the indexes
that were previously being used, allowing different indexes to be used.
Have you tried recreating statistics and comparing the two queries again?
It sounds like SQL server is making a mistake in its chosen plan, but using
this function is removing an option and making it default to a muich better
plan.
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past w
end I tried out a tool called Toad for Sql and it had aquery
> optimizer in it. I tossed in my slow performing query, walked away and 2
> hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
but
> Id like to understand why COALESCE would make the query run almost 100%
> faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||I ran many times testing it against different date ranges always starting wi
th:
CHECKPOINT -- write dirty pages from data cache to disk
DBCC DROPCLEANBUFFERS -- clear the data cache
DBCC FREEPROCCACHE -- clear the procedure cache
Everytime is performed essentially the same give or take a second or two.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message news:etU6gJ7OGHA.916@.T
K2MSFTNGP10.phx.gbl...
> I'm not sure but I am guessing that the use of coalesce negated the indexe
s
> that were previously being used, allowing different indexes to be used.
> Have you tried recreating statistics and comparing the two queries again?
> It sounds like SQL server is making a mistake in its chosen plan, but usin
g
> this function is removing an option and making it default to a muich bette
r
> plan.
>
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> query
> but
>
>|||What does the Execution Plan tell you?
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past w
end I tried out a tool called Toad for Sql and it had a> query optimizer in it. I tossed in my slow performing query, walked away
> and 2 hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
> but Id like to understand why COALESCE would make the query run almost
> 100% faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||Thanks for replying JT ... Im by no means a DBA .. what should I focus on
when looking at the Execution Plan?
Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> What does the Execution Plan tell you?
> http://msdn.microsoft.com/library/d...>
n_1_5pde.asp
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server does not execute SQL. It interprets SQL, compiles an execution
plan, and then uses that plan to perform a sequence of physical operations
such as index scans, joins, spooling, etc. You can deterministically compare
the design merits of one query versus another similar query by studying
their execution plan. For example, does one query perform a full table scan
while an alternative query perform a more efficient index scan?
Below are a few articles describing in more detail how to gain useful
information from execution plans:
SQL Tuning Tutorial - Understanding a Database Execution Plan (1)
http://www.codeproject.com/cs/datab...-tutorial-1.asp
How to Select Indexes for Your SQL Server Tables
http://www.sql-server-performance.com/mr_indexing.asp
SQL Server Query Execution Plan Analysis
http://www.sql-server-performance.c...an_analysis.asp
"Brian" <brian@.nospam.com> wrote in message
news:eZz$s18OGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks for replying JT ... Im by no means a DBA .. what should I focus on
> when looking at the Execution Plan?
> Thanks!
> "JT" <someone@.microsoft.com> wrote in message
> news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I would guess that this is based on the use of
coalesce to force the use of an index.This is
simply a *trick* that was put forward by
'Umachandar Jayachandran' then an MVP.The idea is
that an index can be forced for any data type
by using the datatype boundries.Although a boundry
is not being used here the construct is probably
enough to force the use of an index.
I don't think this trick is talked about in a kb
for obvious reasons:)
I refer the interested reader to these threads:
http://tinyurl.com/rpa2x
http://tinyurl.com/n2oql
http://tinyurl.com/ns9aq
http://tinyurl.com/n5v4y
$.02 from
www.rac4sql.net|||No, this is a different trick. This trick will have the result that the
optimizer will not consider certain (underperforming) access paths. The
trick that Umachandar introduced is not used for joins, but for
filtering expressions (i.e. the WHERE clause).
Gert-Jan
05ponyGT wrote:
> I would guess that this is based on the use of
> coalesce to force the use of an index.This is
> simply a *trick* that was put forward by
> 'Umachandar Jayachandran' then an MVP.The idea is
> that an index can be forced for any data type
> by using the datatype boundries.Although a boundry
> is not being used here the construct is probably
> enough to force the use of an index.
> I don't think this trick is talked about in a kb
> for obvious reasons:)
> I refer the interested reader to these threads:
> http://tinyurl.com/rpa2x
> http://tinyurl.com/n2oql
> http://tinyurl.com/ns9aq
> http://tinyurl.com/n5v4y
> $.02 from
> www.rac4sql.net|||This comes as a shock as I'm so rarely wrong:(
Wonder why we use the term *trick* and not *kludge* :)
Anyway this is just another illustration of turning *what* not *how*
on its head.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:440499B7.72531652@.toomuchspamalready.nl...
> No, this is a different trick. This trick will have the result that the
> optimizer will not consider certain (underperforming) access paths. The
> trick that Umachandar introduced is not used for joins, but for
> filtering expressions (i.e. the WHERE clause).
> Gert-Jan
>
> 05ponyGT wrote:
COALESCE and subquery
Hello,
I want to use a subquery within my main query but only dependant on a
parameter's value. I can do this with dynamic SQL but want to try to avoid
this
Here is mySQL without the parameter
SELECT Id
FROM tblAccounts a
WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
Now if I have a parameter called @.chk_account and it evaluates to NULL - I
don't want to run the subquery at all.
Using dynamic sql would go something like:
DECLARE @.SQL varchar(1000)
SET @.SQL = 'SELECT Id FROM tblAccounts a'
IF @.chk_account IS NOT NULL
BEGIN
SET @.SQL = @.SQL +
' WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
END
EXEC(@.SQL)
I thought maybe I could use the COALESCE function in some way?
DanWhy did you give the same attribute different names? There is no such
thing as an"id" -- it has to be some particular kind of identifier.
And stop using those redudant "tbl-" prefixes; there is one and only
one data strucutre in SQL; this is not an OO language. Read ISO-11179
for details:
SELECT account_id
FROM Accounts AS A
WHERE EXISTS
(SELECT * FROM Sales AS S
WHERE a.account_id = S.account_id)
OR @.chk_account IS NULL;|||Dan,
Depending on where this is used, one or another
solution may be more efficient. Here are a couple alternatives
to Joe's suggestion:
select Id
from tblAccounts a
where @.chk_account is not null
and exists (
select * from tblSales s
where a.Id = s.AccountId
)
union all
select Id
from tblAccounts a
where @.chk_account is null
--
if @.chk_account is null
select Id from tblAccounts a
else
select Id from tblAccounts a
where exists (...)
Steve Kass
Drew University
dan-cat wrote:
>Hello,
>I want to use a subquery within my main query but only dependant on a
>parameter's value. I can do this with dynamic SQL but want to try to avoid
>this
>Here is mySQL without the parameter
>SELECT Id
>FROM tblAccounts a
>WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>Now if I have a parameter called @.chk_account and it evaluates to NULL - I
>don't want to run the subquery at all.
>Using dynamic sql would go something like:
>DECLARE @.SQL varchar(1000)
>SET @.SQL = 'SELECT Id FROM tblAccounts a'
>IF @.chk_account IS NOT NULL
>BEGIN
>SET @.SQL = @.SQL +
>' WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>END
>EXEC(@.SQL)
>I thought maybe I could use the COALESCE function in some way?
>Dan
>
>
>
>
>|||Thanks CELKO for pointing out my naming conventions - you're right I come
from an OO language background and need to shake out of the habit.
Steve: I understand in your first selection how you are using @.chk_account
IS NOT NULL to filter the records correctly. However is the query still
processing the sub-query when it doesn't need to?
To get round this in your second solution you use if... else... to define
the SQL before beforehand
Are you saying its a toss-up between processing the if...else... arguments
or including the sub-query in the SQL and filtering down the result with
@.chk_account.
Dan
"Steve Kass" wrote:
> Dan,
> Depending on where this is used, one or another
> solution may be more efficient. Here are a couple alternatives
> to Joe's suggestion:
> select Id
> from tblAccounts a
> where @.chk_account is not null
> and exists (
> select * from tblSales s
> where a.Id = s.AccountId
> )
> union all
> select Id
> from tblAccounts a
> where @.chk_account is null
> --
> if @.chk_account is null
> select Id from tblAccounts a
> else
> select Id from tblAccounts a
> where exists (...)
> Steve Kass
> Drew University
>
> dan-cat wrote:
>
>|||Dan,
I can't say whether or not the query processor will recognize that it does
not have to process the exists clause when @.chk_account is null, so the
only thing you can do is try and see what happens. The if - then choice
may be good for an ad hoc query, but I believe that if you use it in a
stored procedure, the optimizer will try to generate a single query plan
that includes both queries. Depending on the first actual parameter it
receives, it may not produce the best plan.
I wish I could say more, but it's not cut and dried, as far as I know.
SK
dan-cat wrote:
>Thanks CELKO for pointing out my naming conventions - you're right I come
>from an OO language background and need to shake out of the habit.
>Steve: I understand in your first selection how you are using @.chk_account
>IS NOT NULL to filter the records correctly. However is the query still
>processing the sub-query when it doesn't need to?
>To get round this in your second solution you use if... else... to define
>the SQL before beforehand
>Are you saying its a toss-up between processing the if...else... arguments
>or including the sub-query in the SQL and filtering down the result with
>@.chk_account.
>Dan
>
>"Steve Kass" wrote:
>
>
I want to use a subquery within my main query but only dependant on a
parameter's value. I can do this with dynamic SQL but want to try to avoid
this
Here is mySQL without the parameter
SELECT Id
FROM tblAccounts a
WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
Now if I have a parameter called @.chk_account and it evaluates to NULL - I
don't want to run the subquery at all.
Using dynamic sql would go something like:
DECLARE @.SQL varchar(1000)
SET @.SQL = 'SELECT Id FROM tblAccounts a'
IF @.chk_account IS NOT NULL
BEGIN
SET @.SQL = @.SQL +
' WHERE EXISTS
(SELECT NULL FROM tblSales s
WHERE a.Id = s.AccountId)
END
EXEC(@.SQL)
I thought maybe I could use the COALESCE function in some way?
DanWhy did you give the same attribute different names? There is no such
thing as an"id" -- it has to be some particular kind of identifier.
And stop using those redudant "tbl-" prefixes; there is one and only
one data strucutre in SQL; this is not an OO language. Read ISO-11179
for details:
SELECT account_id
FROM Accounts AS A
WHERE EXISTS
(SELECT * FROM Sales AS S
WHERE a.account_id = S.account_id)
OR @.chk_account IS NULL;|||Dan,
Depending on where this is used, one or another
solution may be more efficient. Here are a couple alternatives
to Joe's suggestion:
select Id
from tblAccounts a
where @.chk_account is not null
and exists (
select * from tblSales s
where a.Id = s.AccountId
)
union all
select Id
from tblAccounts a
where @.chk_account is null
--
if @.chk_account is null
select Id from tblAccounts a
else
select Id from tblAccounts a
where exists (...)
Steve Kass
Drew University
dan-cat wrote:
>Hello,
>I want to use a subquery within my main query but only dependant on a
>parameter's value. I can do this with dynamic SQL but want to try to avoid
>this
>Here is mySQL without the parameter
>SELECT Id
>FROM tblAccounts a
>WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>Now if I have a parameter called @.chk_account and it evaluates to NULL - I
>don't want to run the subquery at all.
>Using dynamic sql would go something like:
>DECLARE @.SQL varchar(1000)
>SET @.SQL = 'SELECT Id FROM tblAccounts a'
>IF @.chk_account IS NOT NULL
>BEGIN
>SET @.SQL = @.SQL +
>' WHERE EXISTS
>(SELECT NULL FROM tblSales s
>WHERE a.Id = s.AccountId)
>END
>EXEC(@.SQL)
>I thought maybe I could use the COALESCE function in some way?
>Dan
>
>
>
>
>|||Thanks CELKO for pointing out my naming conventions - you're right I come
from an OO language background and need to shake out of the habit.
Steve: I understand in your first selection how you are using @.chk_account
IS NOT NULL to filter the records correctly. However is the query still
processing the sub-query when it doesn't need to?
To get round this in your second solution you use if... else... to define
the SQL before beforehand
Are you saying its a toss-up between processing the if...else... arguments
or including the sub-query in the SQL and filtering down the result with
@.chk_account.
Dan
"Steve Kass" wrote:
> Dan,
> Depending on where this is used, one or another
> solution may be more efficient. Here are a couple alternatives
> to Joe's suggestion:
> select Id
> from tblAccounts a
> where @.chk_account is not null
> and exists (
> select * from tblSales s
> where a.Id = s.AccountId
> )
> union all
> select Id
> from tblAccounts a
> where @.chk_account is null
> --
> if @.chk_account is null
> select Id from tblAccounts a
> else
> select Id from tblAccounts a
> where exists (...)
> Steve Kass
> Drew University
>
> dan-cat wrote:
>
>|||Dan,
I can't say whether or not the query processor will recognize that it does
not have to process the exists clause when @.chk_account is null, so the
only thing you can do is try and see what happens. The if - then choice
may be good for an ad hoc query, but I believe that if you use it in a
stored procedure, the optimizer will try to generate a single query plan
that includes both queries. Depending on the first actual parameter it
receives, it may not produce the best plan.
I wish I could say more, but it's not cut and dried, as far as I know.
SK
dan-cat wrote:
>Thanks CELKO for pointing out my naming conventions - you're right I come
>from an OO language background and need to shake out of the habit.
>Steve: I understand in your first selection how you are using @.chk_account
>IS NOT NULL to filter the records correctly. However is the query still
>processing the sub-query when it doesn't need to?
>To get round this in your second solution you use if... else... to define
>the SQL before beforehand
>Are you saying its a toss-up between processing the if...else... arguments
>or including the sub-query in the SQL and filtering down the result with
>@.chk_account.
>Dan
>
>"Steve Kass" wrote:
>
>
Saturday, February 25, 2012
Coalesce / Comma Delimitted List
I'm trying to return multiple column, one of which is a column + a comma delimitted list of values. Below is a simplified, non working, query:
(assume @.MyBit, @.MyVarChar, and @.MyValue are all declared)
SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2,
FROM TableMain
I get an ADO error: Incorrect syntax near '='
Thoughts?
Thanks(select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue)
use "[ ]" brackets instead of "( )" around the above select statment.
(and remove the last comma before the FROM stmnt)|||I now have:
SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + [select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue] ELSE '' END AS Column2
FROM TableMain
and I get:
ADO error: Invalid column name 'select @.MyVarChar = COALESCE(Column3 + ',', '') from TableSub where Value = @.MyValue'
Thanks|||my apologies - here is the correct query
SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2
FROM TableMain
put the "( )" back on and remove the @.MyVarChar = (that is where the err is)
you just need select coalesce...
cmd.ExecuteNonQuery() - max length
what the max length that I can run query to sql Server?
example:
Very large. I don't believe I've tried over 10 megabytes. However, I believe that queries up to at least a gigabyte should work.
You might also think about changing your query so that it follows the syntax:
INSERT INTO [tblEntentKey] ([acceptID],[enterKey])
SELECT 1,'12345678-1234-1234-124322432432'
UNION ALL
SELECT 1,'...'
UNION ALL
...
I believe you will find it will run much faster than your current query with individual insert statements. Each insert statement will start and commit a transaction, fire off any triggers, etc. While the single insert with union all'ed selects will do so under a single transaction/commit/trigger firing. That is unless of course certain records may fail, and you want the non-failing records to still be inserted.
|||Thanks!
Thursday, February 16, 2012
Clustered versus Nonclustered
Hi,
I have one easy question for u all:
1) If I run a below query on table with clustered index on Productid column
Select productid from products where productid between 10 and 20 order by
Productid
2) If I run a below query on table with nonclustered index on Productid
column and no other index on table
Select productid from products where productid between 10 and 20 order by
Productid
Thn, which will run faster and why?
Thanks in advance
Manu Jaidka
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
I doubt there would be much difference at all. If you have a second column
though, like ProductName, that's not part of either index and you run a
query like this:
Select productid, productname from products where productid between 10 and
20 order by Productid
You'll probably get better performance out of the clustered index since it
eliminates the bookmark lookups. Why not try it for yourself and look at
the query execution plans?
|||In this particular case, the query would run faster with a nonclustered
index on the productid column, because the index "covers" the query, so
there is no need to retrieve the data row. If your select list contained
other columns that aren't covered by the index, then a clustered index would
probably be faster.
--Rob Roberts
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
> Thanks in advance
> Manu Jaidka
|||manu
CI is best for range queries
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
> Thanks in advance
> Manu Jaidka
|||Hi,
Here is what I found after doing a lot of R&D on this:
Logical reads are less in case of clustered index as compared to non
clustered index.
Please let me know what can be inferred from above results?
Thanks
Manu Jaidka
"Greg Linwood" wrote:
> Non-Clustered indexes (assuming they cover the query such as this case) are
> faster than Clustered Indexes for range scans, other than in a few
> relatively obscure cases. This is because fewer pages need to be scanned,
> read from memory & locked with a NCIX vs a CIX.
> I've blogged about this here:
> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/11/365.aspx
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eEM8y4BTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
>
|||On Feb 9, 1:18 pm, manu <m...@.discussions.microsoft.com> wrote:
> Hi,
> Here is what I found after doing a lot of R&D on this:
> Logical reads are less in case of clustered index as compared to non
> clustered index.
> Please let me know what can be inferred from above results?
> Thanks
> Manu Jaidka
>
> "Greg Linwood" wrote:
>
>
>
>
>
>
> - Show quoted text -
What can be inferred from above results? First of all, your conclusion
is not correct. If you post how you came to this conclusion, we can
help you find your errors. Have you read Greg Linwood's blog? Also
read about index covering here:
www.devx.com/dbzone/Article/29530
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
|||If the clustered keys are part of the set of columns you are accessing, they
are already part of the ncix, and there is no 'extra' space required.
In addition, Gert-Jan asked about seeks, not scans. :-)
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:em4AnDiTHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Gert
> NCIXs have the burden of carrying RowIds or Cluster Keys. Range scans
> within queries which access all (or sometimes nearly all) columns in a
> table will be more efficient (less reads / locks) if the filter matches a
> CIX than a NCIX as the rows will be packed more densely into the CIX than
> the NCIX. The CIX doesn't carry the extra RowId / Cluster Key so you get
> more rows per IO & therefore less IOs are required.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:45CF780A.CA908D8C@.toomuchspamalready.nl...
>
|||Hi Greg
I did some testing on table with 1 mln rows and it showed that IO for CI was
a little bit less . I think it very true as Kalen said that[vbcol=seagreen]
SELECT IDENTITY(INT) "Prodid" INTO Products
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE UNIQUE CLUSTERED INDEX my_CI_Products ON Products(Prodid)
SET STATISTICS IO ON
SELECT Prodid FROM Products WHERE Prodid BETWEEN 500 AND 15000
SET STATISTICS IO OFF
--Table 'Products'. Scan count 1, logical reads 27, physical reads 0,
read-ahead reads 0.
DBCC DROPCLEANBUFFERS
DROP INDEX Products.my_CI_Products
CREATE UNIQUE NONCLUSTERED INDEX IX_Products ON dbo.Products
(
Prodid
) ON [PRIMARY]
--Table 'Products'. Scan count 1, logical reads 30, physical reads 0,
read-ahead reads 0.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23Hg0PaiTHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sorry - RowIds are the issue rather than cluster keys. Gert did mention
> seeks, but he was also refering to my previous post in which I
> specifically mentioned range scans so it seems like confusion reigns in
> this thread (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23sN7WPiTHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Greg
Again we talk about CI Seek not Scans , right? In your reply to me you said
the followng
>Non-Clustered indexes (assuming they cover the query such as this case) are
>faster than Clustered Indexes for range scans, other than in a few
>relatively obscure cases.
And this one means the opposite , or can you elaborate a liitle bit?
> This confirms my point that queries which access all columns are more
> efficient when range scanning via CIX than NCIX (less IO from the CIX than
> from the NCIX).
So you say that NCI seeks are faster than CI seeks for range queries , do I
understand you properly?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u$gsRdnTHHA.4956@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> This confirms my point that queries which access all columns are more
> efficient when range scanning via CIX than NCIX (less IO from the CIX than
> from the NCIX).
> It would be a mistake to conclude that a range scan against CIXs is always
> more efficient based on this superficial example though (one which is very
> commonly made). Most real world queries are more complex than selecting a
> single column from a single column table & in most (though not all) cases
> where a query is accessing a subset of the columns in a table, NCIXs will
> out-perform CIXs for range-scans.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ulaH8$mTHHA.480@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Greg
I'm sorry but I meant for "range query" using BETWEEN or AND operators as
prodid BETWEEN 10 AND 100 for example
So SQL Server does Clustered Index Seek and NOT Clustered Index Scan
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5yJVvnTHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Hi Uri
> Earlier in this thread you said "CI is best for range queries".
> Range queries require scans, not seeks so I've been talking about scans,
> not seeks.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23v5h7inTHHA.4764@.TK2MSFTNGP05.phx.gbl...
>
I have one easy question for u all:
1) If I run a below query on table with clustered index on Productid column
Select productid from products where productid between 10 and 20 order by
Productid
2) If I run a below query on table with nonclustered index on Productid
column and no other index on table
Select productid from products where productid between 10 and 20 order by
Productid
Thn, which will run faster and why?
Thanks in advance
Manu Jaidka
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
I doubt there would be much difference at all. If you have a second column
though, like ProductName, that's not part of either index and you run a
query like this:
Select productid, productname from products where productid between 10 and
20 order by Productid
You'll probably get better performance out of the clustered index since it
eliminates the bookmark lookups. Why not try it for yourself and look at
the query execution plans?
|||In this particular case, the query would run faster with a nonclustered
index on the productid column, because the index "covers" the query, so
there is no need to retrieve the data row. If your select list contained
other columns that aren't covered by the index, then a clustered index would
probably be faster.
--Rob Roberts
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
> Thanks in advance
> Manu Jaidka
|||manu
CI is best for range queries
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3A145ACB-71C3-4A73-AAAA-17D98C2D39D0@.microsoft.com...
> Hi,
> I have one easy question for u all:
> 1) If I run a below query on table with clustered index on Productid
> column
> Select productid from products where productid between 10 and 20 order by
> Productid
>
> 2) If I run a below query on table with nonclustered index on Productid
> column and no other index on table
> Select productid from products where productid between 10 and 20 order by
> Productid
> Thn, which will run faster and why?
> Thanks in advance
> Manu Jaidka
|||Hi,
Here is what I found after doing a lot of R&D on this:
Logical reads are less in case of clustered index as compared to non
clustered index.
Please let me know what can be inferred from above results?
Thanks
Manu Jaidka
"Greg Linwood" wrote:
> Non-Clustered indexes (assuming they cover the query such as this case) are
> faster than Clustered Indexes for range scans, other than in a few
> relatively obscure cases. This is because fewer pages need to be scanned,
> read from memory & locked with a NCIX vs a CIX.
> I've blogged about this here:
> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/11/365.aspx
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eEM8y4BTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
>
|||On Feb 9, 1:18 pm, manu <m...@.discussions.microsoft.com> wrote:
> Hi,
> Here is what I found after doing a lot of R&D on this:
> Logical reads are less in case of clustered index as compared to non
> clustered index.
> Please let me know what can be inferred from above results?
> Thanks
> Manu Jaidka
>
> "Greg Linwood" wrote:
>
>
>
>
>
>
> - Show quoted text -
What can be inferred from above results? First of all, your conclusion
is not correct. If you post how you came to this conclusion, we can
help you find your errors. Have you read Greg Linwood's blog? Also
read about index covering here:
www.devx.com/dbzone/Article/29530
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
|||If the clustered keys are part of the set of columns you are accessing, they
are already part of the ncix, and there is no 'extra' space required.
In addition, Gert-Jan asked about seeks, not scans. :-)
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:em4AnDiTHHA.1552@.TK2MSFTNGP05.phx.gbl...
> Hi Gert
> NCIXs have the burden of carrying RowIds or Cluster Keys. Range scans
> within queries which access all (or sometimes nearly all) columns in a
> table will be more efficient (less reads / locks) if the filter matches a
> CIX than a NCIX as the rows will be packed more densely into the CIX than
> the NCIX. The CIX doesn't carry the extra RowId / Cluster Key so you get
> more rows per IO & therefore less IOs are required.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:45CF780A.CA908D8C@.toomuchspamalready.nl...
>
|||Hi Greg
I did some testing on table with 1 mln rows and it showed that IO for CI was
a little bit less . I think it very true as Kalen said that[vbcol=seagreen]
SELECT IDENTITY(INT) "Prodid" INTO Products
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
CREATE UNIQUE CLUSTERED INDEX my_CI_Products ON Products(Prodid)
SET STATISTICS IO ON
SELECT Prodid FROM Products WHERE Prodid BETWEEN 500 AND 15000
SET STATISTICS IO OFF
--Table 'Products'. Scan count 1, logical reads 27, physical reads 0,
read-ahead reads 0.
DBCC DROPCLEANBUFFERS
DROP INDEX Products.my_CI_Products
CREATE UNIQUE NONCLUSTERED INDEX IX_Products ON dbo.Products
(
Prodid
) ON [PRIMARY]
--Table 'Products'. Scan count 1, logical reads 30, physical reads 0,
read-ahead reads 0.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23Hg0PaiTHHA.4668@.TK2MSFTNGP04.phx.gbl...
> Sorry - RowIds are the issue rather than cluster keys. Gert did mention
> seeks, but he was also refering to my previous post in which I
> specifically mentioned range scans so it seems like confusion reigns in
> this thread (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23sN7WPiTHHA.4872@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Greg
Again we talk about CI Seek not Scans , right? In your reply to me you said
the followng
>Non-Clustered indexes (assuming they cover the query such as this case) are
>faster than Clustered Indexes for range scans, other than in a few
>relatively obscure cases.
And this one means the opposite , or can you elaborate a liitle bit?
> This confirms my point that queries which access all columns are more
> efficient when range scanning via CIX than NCIX (less IO from the CIX than
> from the NCIX).
So you say that NCI seeks are faster than CI seeks for range queries , do I
understand you properly?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u$gsRdnTHHA.4956@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> This confirms my point that queries which access all columns are more
> efficient when range scanning via CIX than NCIX (less IO from the CIX than
> from the NCIX).
> It would be a mistake to conclude that a range scan against CIXs is always
> more efficient based on this superficial example though (one which is very
> commonly made). Most real world queries are more complex than selecting a
> single column from a single column table & in most (though not all) cases
> where a query is accessing a subset of the columns in a table, NCIXs will
> out-perform CIXs for range-scans.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ulaH8$mTHHA.480@.TK2MSFTNGP02.phx.gbl...
>
|||Hi Greg
I'm sorry but I meant for "range query" using BETWEEN or AND operators as
prodid BETWEEN 10 AND 100 for example
So SQL Server does Clustered Index Seek and NOT Clustered Index Scan
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u5yJVvnTHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Hi Uri
> Earlier in this thread you said "CI is best for range queries".
> Range queries require scans, not seeks so I've been talking about scans,
> not seeks.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23v5h7inTHHA.4764@.TK2MSFTNGP05.phx.gbl...
>
Subscribe to:
Comments (Atom)