Clustered DB Server: operation

Showing posts with label operation. Show all posts

Thursday, March 22, 2012

collation conflict for concatenation operation

Hi,
I'm facing the error as the subject stated , "Cannot resolve collation
conflict for concatenation operation".
Base on running "print cast(
databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
I want to do searching on records in tables with partial matching on the
keywords.
the sql statement is :
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? + '%' ) ) )
order by co.active_from_date desc
as I will run this statement in java, the "?" will be filled into string and
both fields are nvarchar.
how should I do to solve this problm? thank you.Have you tried converting the data to unicode before
concatination ?
Peter
"I favor the Civil Rights Act of 1964 and it must be
enforced at gunpoint if necessary."
Ronald Reagan
>--Original Message--
>Hi,
>I'm facing the error as the subject stated , "Cannot
resolve collation
>conflict for concatenation operation".
>Base on running "print cast(
>databasepropertyex( 'master', 'collation' ) as varchar
(128) )", the
>collation of sql2000 database
is "Chinese_Taiwan_Stroke_CI_AS" .
>I want to do searching on records in tables with partial
matching on the
>keywords.
>the sql statement is :
>select co.cms_content_id, co.title_en,
ca.cms_category_id,
>ca.category_name_en
>from cms_content co, cms_sub_category sc, cms_category ca
>where co.cms_sub_category_id = sc.cms_sub_category_id
>AND sc.cms_category_id = ca.cms_category_id
>AND co.status = 'active' collate
Chinese_Taiwan_Stroke_CI_AS
>AND(((title_ch like '%' + ? + '%' ) OR (title_en
like '%' + ? + '%' ) ) )
>order by co.active_from_date desc
>as I will run this statement in java, the "?" will be
filled into string and
>both fields are nvarchar.
>how should I do to solve this problm? thank you.
>.
>|||Try adding collation designators to the LIKE predicates:
select co.cms_content_id, co.title_en, ca.cms_category_id,
ca.category_name_en
from cms_content co, cms_sub_category sc, cms_category ca
where co.cms_sub_category_id = sc.cms_sub_category_id
AND sc.cms_category_id = ca.cms_category_id
AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
OR (title_en like '%' + ? + '%' collate
inese_Taiwan_Stroke_CI_AS) ) )
order by co.active_from_date desc
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"little C" <little C@.discussions.microsoft.com> wrote in message
news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> Hi,
> I'm facing the error as the subject stated , "Cannot resolve collation
> conflict for concatenation operation".
> Base on running "print cast(
> databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
> collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
> I want to do searching on records in tables with partial matching on the
> keywords.
> the sql statement is :
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? +
' ) ) )
> order by co.active_from_date desc
> as I will run this statement in java, the "?" will be filled into string
and
> both fields are nvarchar.
> how should I do to solve this problm? thank you.
>|||thanks Adam,
the problem is solved, I need to put "collate Chinese_Taiwan_Stroke_CI_AS"
right next to each "?". thanks a lot.
Chris C
"Adam Machanic" wrote:
> Try adding collation designators to the LIKE predicates:
>
> select co.cms_content_id, co.title_en, ca.cms_category_id,
> ca.category_name_en
> from cms_content co, cms_sub_category sc, cms_category ca
> where co.cms_sub_category_id = sc.cms_sub_category_id
> AND sc.cms_category_id = ca.cms_category_id
> AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> AND(((title_ch like '%' + ? + '%' collate Chinese_Taiwan_Stroke_CI_AS)
> OR (title_en like '%' + ? + '%' collate
> inese_Taiwan_Stroke_CI_AS) ) )
> order by co.active_from_date desc
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "little C" <little C@.discussions.microsoft.com> wrote in message
> news:904CF870-C526-4BF5-AB89-3DAA75DC004D@.microsoft.com...
> > Hi,
> >
> > I'm facing the error as the subject stated , "Cannot resolve collation
> > conflict for concatenation operation".
> >
> > Base on running "print cast(
> > databasepropertyex( 'master', 'collation' ) as varchar(128) )", the
> > collation of sql2000 database is "Chinese_Taiwan_Stroke_CI_AS" .
> >
> > I want to do searching on records in tables with partial matching on the
> > keywords.
> >
> > the sql statement is :
> >
> > select co.cms_content_id, co.title_en, ca.cms_category_id,
> > ca.category_name_en
> > from cms_content co, cms_sub_category sc, cms_category ca
> > where co.cms_sub_category_id = sc.cms_sub_category_id
> > AND sc.cms_category_id = ca.cms_category_id
> > AND co.status = 'active' collate Chinese_Taiwan_Stroke_CI_AS
> > AND(((title_ch like '%' + ? + '%' ) OR (title_en like '%' + ? +
> ' ) ) )
> > order by co.active_from_date desc
> >
> > as I will run this statement in java, the "?" will be filled into string
> and
> > both fields are nvarchar.
> >
> > how should I do to solve this problm? thank you.
> >
>
>

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

Monday, March 19, 2012

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'
Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>
|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
>

COLLATE problem

Why do i get the error below:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
What should i do?
INSERT INTO tkl_proposals(
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, transfer_date, from_server)
SELECT
proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
front_page_tr, front_page_en, notes, owner_company_id, status,
proposal_date, getdate(), 'KIRMIZI'
FROM MD_F.MidasLocal.dbo.tkl_proposals b
WHERE
(NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
b.proposal_id=a.proposal_id AND
b.oc_initial=a.oc_initial AND
b.oc_place=a.oc_place)
)
AND b.oc_initial='MD'
AND b.oc_place='F'
AND b.proposal_date >= '1-1-2005'Zulu
You join two tables on varchar columns which have a different collation.
Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
needs)
That means Table2 has not the same collation as Table1 and by using above
hints I define to use this praticular collation.
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||> What should i do?
It's a good practice to provide DDL (CREATE TABLE statements) so that we
can better help you.
This error may be due do different collations on the joined columns. You
can explicitly specify the desired collation using a COLLATE clause in your
query. See the Bools Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"zulu" <zkendir@.simternet.com> wrote in message
news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Why do i get the error below:
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
> What should i do?
>
>
> INSERT INTO tkl_proposals(
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, transfer_date, from_server)
> SELECT
> proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> front_page_tr, front_page_en, notes, owner_company_id, status,
> proposal_date, getdate(), 'KIRMIZI'
> FROM MD_F.MidasLocal.dbo.tkl_proposals b
> WHERE
> (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> b.proposal_id=a.proposal_id AND
> b.oc_initial=a.oc_initial AND
> b.oc_place=a.oc_place)
> )
> AND b.oc_initial='MD'
> AND b.oc_place='F'
> AND b.proposal_date >= '1-1-2005'
>|||ok, got it, thank you
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O$CfLGfCFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Zulu
> You join two tables on varchar columns which have a different collation.
> Table1.LastName=Table2.surname COLLATE HEBREW_CI_AS (Change it for your
> needs)
> That means Table2 has not the same collation as Table1 and by using above
> hints I define to use this praticular collation.
>
> "zulu" <zkendir@.simternet.com> wrote in message
> news:O2ZXLyeCFHA.2876@.TK2MSFTNGP12.phx.gbl...
> > Why do i get the error below:
> >
> > Server: Msg 446, Level 16, State 9, Line 1
> > Cannot resolve collation conflict for equal to operation.
> >
> > What should i do?
> >
> >
> >
> >
> >
> > INSERT INTO tkl_proposals(
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, transfer_date, from_server)
> > SELECT
> > proposal_id, username, oc_group, oc_place, oc_initial, owner_name,
> > front_page_tr, front_page_en, notes, owner_company_id, status,
> > proposal_date, getdate(), 'KIRMIZI'
> > FROM MD_F.MidasLocal.dbo.tkl_proposals b
> > WHERE
> > (NOT EXISTS (SELECT NULL FROM tkl_proposals a WHERE
> > b.proposal_id=a.proposal_id AND
> > b.oc_initial=a.oc_initial AND
> > b.oc_place=a.oc_place)
> > )
> > AND b.oc_initial='MD'
> > AND b.oc_place='F'
> > AND b.proposal_date >= '1-1-2005'
> >
> >
>

Tuesday, February 14, 2012

Clustered index with a truncate table operation question.

I have a situation at here that appears once in a while:
The table in question has a single clustered index on ReferenceID. It's
populated by the following process: Truncatae table MyTable, run DTS to
populate the table.
This runs every xx minutes.
The table has roughly 50K records but the records are large.
The problem occurs during a query similar to the following:
select ...
from MyTable
LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
MyTable.AssociatedID = seconds.MasterID) as Calculated on
Calculated.ReferenceID = MyTable.ReferenceID
At one point this query, which normally takes 30 seconds or so, went to over
16 minutes to complete consistently.
Eventually we ran
dbcc dbreindex(MyTable)
and that seems to have fixed it.
My question is why: We clear the table before repopulating, how did the
index get so jacked up?
Thanks,
EricYour problem is likely caused by internal or external Index Fragmentation
(based on your reporting that dbreindex() fixed the problem... This is cause
d
by having a clusterd index on a field (or COmbination od fields) for which
inserts are randomly dispersed. So, if your CLustered Index is on
ReferenceID, try to ensure that DTS sorts it's data by ReferenceID before it
runs... Then the pages in the clustered Index will be as full as possible,
and both internal and external fragmentation will be at a minimum. If you
can;t do this, then you need t orun dbcc reindex after every DTS run, or
choose a different Clustered Index that you can sort the DTS import data on.
.
"Eric" wrote:

> I have a situation at here that appears once in a while:
> The table in question has a single clustered index on ReferenceID. It's
> populated by the following process: Truncatae table MyTable, run DTS to
> populate the table.
> This runs every xx minutes.
> The table has roughly 50K records but the records are large.
> The problem occurs during a query similar to the following:
> select ...
> from MyTable
> LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
> MyTable.AssociatedID = seconds.MasterID) as Calculated on
> Calculated.ReferenceID = MyTable.ReferenceID
>
> At one point this query, which normally takes 30 seconds or so, went to ov
er
> 16 minutes to complete consistently.
> Eventually we ran
> dbcc dbreindex(MyTable)
> and that seems to have fixed it.
> My question is why: We clear the table before repopulating, how did the
> index get so jacked up?
> Thanks,
> Eric
>
>

Clustered index vs. nonclustered index for GUID primary key

Hello,
Would someone out there understand and can explain to me why clustered index
for GUID primary key will result a faster select operation when we look up
one row by a primary key like below comparing with non-clustered index for
the primary key? I experimented this myself, so there is no doubt that
clustered index is better but I don't know why.
select *
from MyTable -- there is no other index in this table.
where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
I read through several documentations and the B-tree seems similar enough
not to make a difference. In fact, my common sense (not very reliable now)
tells me that the result should be the other way around. Help!!!
Thank you very much in advance,Regardless of the data type, a clustered index seek is more efficient than a
non-clustered seek because a bookmark lookup is not needed. Note that a
clustered index is a B-tree with the actual data pages as leaf nodes. A
non-clustered index is also a B-tree but the leaf nodes are 'pointers' (RID
or clustering key) to the data rows so additional i/o is needed for the
bookmark lookup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> Hello,
> Would someone out there understand and can explain to me why clustered
index
> for GUID primary key will result a faster select operation when we look up
> one row by a primary key like below comparing with non-clustered index for
> the primary key? I experimented this myself, so there is no doubt that
> clustered index is better but I don't know why.
> select *
> from MyTable -- there is no other index in this table.
> where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> I read through several documentations and the B-tree seems similar enough
> not to make a difference. In fact, my common sense (not very reliable now)
> tells me that the result should be the other way around. Help!!!
> Thank you very much in advance,
>|||Dan correctly answered your question but I wanted to make sure you need to
be aware of more than the effects of the Bookmark or not when comparing a
clustered index to Non-clustered on a GUID. Since Guid's are random by
nature you can get a tremendous amount of page splitting that can certainly
slow down inserts and increase the size of the table dramatically. The Guid
as a CI will also add an additional 16 bytes on to each row of every
non-clustered index as well. I am not trying to talk you out of using a
GUID as your CI but wanted to be sure you understood the other ramifications
of it. There has been a lot of discussion on this topic in these newsgroups
that you may want to google on before you decide one way or the other.
--
Andrew J. Kelly SQL MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> Hello,
> Would someone out there understand and can explain to me why clustered
index
> for GUID primary key will result a faster select operation when we look up
> one row by a primary key like below comparing with non-clustered index for
> the primary key? I experimented this myself, so there is no doubt that
> clustered index is better but I don't know why.
> select *
> from MyTable -- there is no other index in this table.
> where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> I read through several documentations and the B-tree seems similar enough
> not to make a difference. In fact, my common sense (not very reliable now)
> tells me that the result should be the other way around. Help!!!
> Thank you very much in advance,
>|||thanks but that is still very vague for me, you said the leaf nodes are
pointers, hm you mean leaf nodes contain bunch of pointers. Don't all index
nodes contain bunch of pointers to other index nodes or data pages? Why no
bookmark lookup needed when the acutal data pages are leaf nodes? Please
help...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23KLT4bwiEHA.2052@.TK2MSFTNGP15.phx.gbl...
> Regardless of the data type, a clustered index seek is more efficient than
a
> non-clustered seek because a bookmark lookup is not needed. Note that a
> clustered index is a B-tree with the actual data pages as leaf nodes. A
> non-clustered index is also a B-tree but the leaf nodes are 'pointers'
(RID
> or clustering key) to the data rows so additional i/o is needed for the
> bookmark lookup.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > Would someone out there understand and can explain to me why clustered
> index
> > for GUID primary key will result a faster select operation when we look
up
> > one row by a primary key like below comparing with non-clustered index
for
> > the primary key? I experimented this myself, so there is no doubt that
> > clustered index is better but I don't know why.
> >
> > select *
> > from MyTable -- there is no other index in this table.
> > where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> >
> > I read through several documentations and the B-tree seems similar
enough
> > not to make a difference. In fact, my common sense (not very reliable
now)
> > tells me that the result should be the other way around. Help!!!
> >
> > Thank you very much in advance,
> >
> >
>|||With the exception of clustered index leaf nodes, all index nodes contain 'a
bunch of' pointers'. Clustered index leaf nodes contain the actual data
rows instead of pointers so no additional data access are needed.
You might peruse Books Online topics Clustered Indexes
<architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
<architec.chm::/8_ar_da2_75mb.htm> for a thorough discussion on the
differences.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23EYAw0wiEHA.704@.TK2MSFTNGP09.phx.gbl...
> thanks but that is still very vague for me, you said the leaf nodes are
> pointers, hm you mean leaf nodes contain bunch of pointers. Don't all
index
> nodes contain bunch of pointers to other index nodes or data pages? Why
no
> bookmark lookup needed when the acutal data pages are leaf nodes? Please
> help...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23KLT4bwiEHA.2052@.TK2MSFTNGP15.phx.gbl...
> > Regardless of the data type, a clustered index seek is more efficient
than
> a
> > non-clustered seek because a bookmark lookup is not needed. Note that a
> > clustered index is a B-tree with the actual data pages as leaf nodes. A
> > non-clustered index is also a B-tree but the leaf nodes are 'pointers'
> (RID
> > or clustering key) to the data rows so additional i/o is needed for the
> > bookmark lookup.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Zeng" <zzy@.nonospam.com> wrote in message
> > news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > Would someone out there understand and can explain to me why clustered
> > index
> > > for GUID primary key will result a faster select operation when we
look
> up
> > > one row by a primary key like below comparing with non-clustered index
> for
> > > the primary key? I experimented this myself, so there is no doubt
that
> > > clustered index is better but I don't know why.
> > >
> > > select *
> > > from MyTable -- there is no other index in this table.
> > > where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> > >
> > > I read through several documentations and the B-tree seems similar
> enough
> > > not to make a difference. In fact, my common sense (not very reliable
> now)
> > > tells me that the result should be the other way around. Help!!!
> > >
> > > Thank you very much in advance,
> > >
> > >
> >
> >
>|||Just to add to what others have said...
If a clustered index has been created for a table, the clustered index *is*
the table - all the columns of the table (and all the rows) are present in
the clustered index. So when you find 'DAF02BF0...' in the clustered index
(which is fast, since it's an equality search on the key, just what indexes
are for), you have also arrived at the entire row of the table and no
further data retrieval is needed to get the columns SELECT * must return.
A non-clustered index is more like a complete index for a book, or a
concordance. A concordance contains every word in the book, and for each
word, the page number(s) where that word appears in the book. If there are
pictures, punctuation, or other things in the book, you'll have to go to the
page to find them - they won't be in the index. If you look up and find
'DAF02BF0...' in a nonclustered index, you won't also find the other values
from that row you need to return SELECT *. You will only find out where to
go to find them, and will need to do more work - the index tells you where
to go by storing either the physical page location of the row or the
clustered index key of the row (the former if the table is not a clustered
index, the latter if the table is a clustered index).
I think a lot of this becomes easier to understand once you realize that the
clustered index is the table. A nonclustered index is only some of the
columns of the table, with references to direct you to the values for the
missing columns.
Steve Kass
Drew University
"Zeng" <zzy@.nonospam.com> wrote in message
news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> Hello,
> Would someone out there understand and can explain to me why clustered
index
> for GUID primary key will result a faster select operation when we look up
> one row by a primary key like below comparing with non-clustered index for
> the primary key? I experimented this myself, so there is no doubt that
> clustered index is better but I don't know why.
> select *
> from MyTable -- there is no other index in this table.
> where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> I read through several documentations and the B-tree seems similar enough
> not to make a difference. In fact, my common sense (not very reliable now)
> tells me that the result should be the other way around. Help!!!
> Thank you very much in advance,
>|||Andrew J. Kelly wrote:
> Dan correctly answered your question but I wanted to make sure you
> need to be aware of more than the effects of the Bookmark or not when
> comparing a clustered index to Non-clustered on a GUID. Since Guid's
> are random by nature you can get a tremendous amount of page
> splitting that can certainly slow down inserts and increase the size
> of the table dramatically. The Guid as a CI will also add an
> additional 16 bytes on to each row of every non-clustered index as
> well. I am not trying to talk you out of using a GUID as your CI but
> wanted to be sure you understood the other ramifications of it.
> There has been a lot of discussion on this topic in these newsgroups
> that you may want to google on before you decide one way or the
> other.
>
I agree with Andrew. If you can, use an IDENTITY column as your PK
instead of a GUID, especially if you are going to use a clustered index.
Not only will you eliminate page splitting, but your clustered key will
be much smaller and this will translate to smaller non-clustered indexes
as well.
--
David G.|||On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
> Andrew J. Kelly wrote:
>> Dan correctly answered your question but I wanted to make sure you
>> need to be aware of more than the effects of the Bookmark or not when
>> comparing a clustered index to Non-clustered on a GUID. Since Guid's
>> are random by nature you can get a tremendous amount of page
>> splitting that can certainly slow down inserts and increase the size
>> of the table dramatically. The Guid as a CI will also add an
>> additional 16 bytes on to each row of every non-clustered index as
>> well. I am not trying to talk you out of using a GUID as your CI but
>> wanted to be sure you understood the other ramifications of it.
>> There has been a lot of discussion on this topic in these newsgroups
>> that you may want to google on before you decide one way or the
>> other.
> I agree with Andrew. If you can, use an IDENTITY column as your PK
> instead of a GUID, especially if you are going to use a clustered index.
> Not only will you eliminate page splitting, but your clustered key will
> be much smaller and this will translate to smaller non-clustered indexes
> as well.
What about rowguid columns used for replication? Replication wizzard adds
rowguid columns, and creates clustered index on them if I don't allready
have clustered index on that particular table. Is it then better to change
that rowguid index to non-clustered one?
Mike
--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr|||Mario Splivalo wrote:
> On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
>> Andrew J. Kelly wrote:
>> Dan correctly answered your question but I wanted to make sure you
>> need to be aware of more than the effects of the Bookmark or not
>> when comparing a clustered index to Non-clustered on a GUID. Since
>> Guid's are random by nature you can get a tremendous amount of page
>> splitting that can certainly slow down inserts and increase the size
>> of the table dramatically. The Guid as a CI will also add an
>> additional 16 bytes on to each row of every non-clustered index as
>> well. I am not trying to talk you out of using a GUID as your CI
>> but wanted to be sure you understood the other ramifications of it.
>> There has been a lot of discussion on this topic in these newsgroups
>> that you may want to google on before you decide one way or the
>> other.
>>
>> I agree with Andrew. If you can, use an IDENTITY column as your PK
>> instead of a GUID, especially if you are going to use a clustered
>> index. Not only will you eliminate page splitting, but your
>> clustered key will be much smaller and this will translate to
>> smaller non-clustered indexes as well.
> What about rowguid columns used for replication? Replication wizzard
> adds rowguid columns, and creates clustered index on them if I don't
> allready have clustered index on that particular table. Is it then
> better to change that rowguid index to non-clustered one?
> Mike
If the table experiences a lot of inserts (highly transactional), then
you will likely see page splits and the hard drive array may be writing
all over the place to get the row on the proper page. You can limit page
splits using a fill factor on the index and reindexing periodically to
keep the free page space available. But the table will be larger as a
result and the drive heads still may be moving around more than
necessary.
I'm not sure why SQL Server adds the index as clustered. Probably just a
default like it uses when adding a PK constraint.
Remember that clustered index keys are part of all non-clustered
indexes. So adding a 16-byte GUID CI adds an additional 16-bytes to each
key in a non-clustered index. If you have a non-clustered index on an
INT IDENTITY, then you'll be increasing storage from 4-bytes to 20-bytes
for each key, which is substantial (makes storage much more costly, not
to mention backups and restore operations, and writing).
I would suggest, as a matter of practice, you add a clustered index to
all tables. That way, SQL Server doesn't do it for you on a column that
probably shouldn't have one.
David G.|||I wasn't aware that the wizard made these clustered. The Guid used for
replication does not need to be the PK or clustered and often is not.
--
Andrew J. Kelly SQL MVP
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncir0tn.8sl.majk@.fly.srk.fer.hr...
> On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
> > Andrew J. Kelly wrote:
> >> Dan correctly answered your question but I wanted to make sure you
> >> need to be aware of more than the effects of the Bookmark or not when
> >> comparing a clustered index to Non-clustered on a GUID. Since Guid's
> >> are random by nature you can get a tremendous amount of page
> >> splitting that can certainly slow down inserts and increase the size
> >> of the table dramatically. The Guid as a CI will also add an
> >> additional 16 bytes on to each row of every non-clustered index as
> >> well. I am not trying to talk you out of using a GUID as your CI but
> >> wanted to be sure you understood the other ramifications of it.
> >> There has been a lot of discussion on this topic in these newsgroups
> >> that you may want to google on before you decide one way or the
> >> other.
> >>
> >
> > I agree with Andrew. If you can, use an IDENTITY column as your PK
> > instead of a GUID, especially if you are going to use a clustered index.
> > Not only will you eliminate page splitting, but your clustered key will
> > be much smaller and this will translate to smaller non-clustered indexes
> > as well.
> What about rowguid columns used for replication? Replication wizzard adds
> rowguid columns, and creates clustered index on them if I don't allready
> have clustered index on that particular table. Is it then better to change
> that rowguid index to non-clustered one?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr|||"David G." <david_nospam@.nospam.com> wrote in message
news:O5nSdgziEHA.396@.TK2MSFTNGP12.phx.gbl...
> Mario Splivalo wrote:
> > On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
> >> Andrew J. Kelly wrote:
> >> Dan correctly answered your question but I wanted to make sure you
> >> need to be aware of more than the effects of the Bookmark or not
> >> when comparing a clustered index to Non-clustered on a GUID. Since
> >> Guid's are random by nature you can get a tremendous amount of page
> >> splitting that can certainly slow down inserts and increase the size
> >> of the table dramatically. The Guid as a CI will also add an
> >> additional 16 bytes on to each row of every non-clustered index as
> >> well. I am not trying to talk you out of using a GUID as your CI
> >> but wanted to be sure you understood the other ramifications of it.
> >> There has been a lot of discussion on this topic in these newsgroups
> >> that you may want to google on before you decide one way or the
> >> other.
> >>
> >>
> >> I agree with Andrew. If you can, use an IDENTITY column as your PK
> >> instead of a GUID, especially if you are going to use a clustered
> >> index. Not only will you eliminate page splitting, but your
> >> clustered key will be much smaller and this will translate to
> >> smaller non-clustered indexes as well.
> >
> > What about rowguid columns used for replication? Replication wizzard
> > adds rowguid columns, and creates clustered index on them if I don't
> > allready have clustered index on that particular table. Is it then
> > better to change that rowguid index to non-clustered one?
> >
> > Mike
> If the table experiences a lot of inserts (highly transactional), then
> you will likely see page splits and the hard drive array may be writing
> all over the place to get the row on the proper page. You can limit page
> splits using a fill factor on the index and reindexing periodically to
> keep the free page space available. But the table will be larger as a
> result and the drive heads still may be moving around more than
> necessary.
> I'm not sure why SQL Server adds the index as clustered. Probably just a
> default like it uses when adding a PK constraint.
> Remember that clustered index keys are part of all non-clustered
> indexes. So adding a 16-byte GUID CI adds an additional 16-bytes to each
> key in a non-clustered index. If you have a non-clustered index on an
> INT IDENTITY, then you'll be increasing storage from 4-bytes to 20-bytes
> for each key, which is substantial (makes storage much more costly, not
> to mention backups and restore operations, and writing).
Not quite. An index on an integer column contains more than 4 bytes per row
regardless of whether there is a clustered index on the table. The index
must have some kind of reference to the location of the full table row to
begin with, in addition to the 4 bytes for the integer. Adding a GUID CI
adds fewer than 16 bytes to each non-clustered index. The addition of the
CI replaces a row locator (assuming no CI to begin with) with the new CI key
(or new CI key plus 4-byte uniquifier if the CI is not declared as unique).
So if there was no clustered index to begin with, I think the new 16-byte
reference replaces an existing 8-byte row locator, increasing the size of
each index row by 8 bytes, not 16.
SK
> I would suggest, as a matter of practice, you add a clustered index to
> all tables. That way, SQL Server doesn't do it for you on a column that
> probably shouldn't have one.
>
> --
> David G.
>|||Is this true? Basically, there are 2 differences between clustered and
nonclustered index:
1) Nonclustered index will typically require *one* extra disk reading
operations because the leaf nodes are not the data itself, AND
2) Nonclustered index will require a few more disk reading operations
because the data of each row is not stored together in one place. I have
looked at these references that Dan Clustered Indexes
<architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
<architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about this
point. And I wonder how come SqlServer doesn't always store each row
together by default' And is this the bookmark operation that the document
refering to?
Is there anything else?
"Steve Kass" <skass@.drew.edu> wrote in message
news:uLKdk8xiEHA.2808@.TK2MSFTNGP10.phx.gbl...
> Just to add to what others have said...
> If a clustered index has been created for a table, the clustered index
*is*
> the table - all the columns of the table (and all the rows) are present in
> the clustered index. So when you find 'DAF02BF0...' in the clustered
index
> (which is fast, since it's an equality search on the key, just what
indexes
> are for), you have also arrived at the entire row of the table and no
> further data retrieval is needed to get the columns SELECT * must return.
> A non-clustered index is more like a complete index for a book, or a
> concordance. A concordance contains every word in the book, and for each
> word, the page number(s) where that word appears in the book. If there
are
> pictures, punctuation, or other things in the book, you'll have to go to
the
> page to find them - they won't be in the index. If you look up and find
> 'DAF02BF0...' in a nonclustered index, you won't also find the other
values
> from that row you need to return SELECT *. You will only find out where
to
> go to find them, and will need to do more work - the index tells you where
> to go by storing either the physical page location of the row or the
> clustered index key of the row (the former if the table is not a clustered
> index, the latter if the table is a clustered index).
> I think a lot of this becomes easier to understand once you realize that
the
> clustered index is the table. A nonclustered index is only some of the
> columns of the table, with references to direct you to the values for the
> missing columns.
> Steve Kass
> Drew University
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > Would someone out there understand and can explain to me why clustered
> index
> > for GUID primary key will result a faster select operation when we look
up
> > one row by a primary key like below comparing with non-clustered index
for
> > the primary key? I experimented this myself, so there is no doubt that
> > clustered index is better but I don't know why.
> >
> > select *
> > from MyTable -- there is no other index in this table.
> > where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> >
> > I read through several documentations and the B-tree seems similar
enough
> > not to make a difference. In fact, my common sense (not very reliable
now)
> > tells me that the result should be the other way around. Help!!!
> >
> > Thank you very much in advance,
> >
> >
>
>|||Zeng wrote:
> Is this true? Basically, there are 2 differences between clustered and
> nonclustered index:
> 1) Nonclustered index will typically require *one* extra disk reading
> operations because the leaf nodes are not the data itself, AND
> 2) Nonclustered index will require a few more disk reading operations
> because the data of each row is not stored together in one place. I
> have looked at these references that Dan Clustered Indexes
> <architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
> <architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
> this point. And I wonder how come SqlServer doesn't always store each
> row together by default' And is this the bookmark operation that the
> document refering to?
>
I'm not sure what you mean by "how come SqlServer doesn't always store
each
> row together by default?". In a clustered index, the rows on a given
page are in order, although pages themselves may be out of sequence.
The clustered index is itself the table. That's why you can only have
one on a table. It would be impossible to sort the data two different
ways.
On a non-clustered index, as others have mentioned, you only have
pointers to the actual data. To get there, SQL Server performs a
bookmark lookup, which is a very fast operation, just not as fast as if
the query could use a clustered index. Unless the query can make use of
a covering index (all columns and parameters are in the index itself), a
bookmark lookup will occur.
David G.|||From Steve Cass comment, the data of one row in non-clustered index (for
table w/o clustered index) is not stored in one place, it's fragmented. If
I understand him correctly, if my table has 40 columns, then the data of
those 40 columns even for just one row might be scattered in different
places; so I'm was wondering how come SqlServer doesn't store data of all
columns belonging to one row in one place just like when there is clustered
index in the table.
"David G." <david_nospam@.nospam.com> wrote in message
news:OZawwB5iEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Zeng wrote:
> > Is this true? Basically, there are 2 differences between clustered and
> > nonclustered index:
> > 1) Nonclustered index will typically require *one* extra disk reading
> > operations because the leaf nodes are not the data itself, AND
> > 2) Nonclustered index will require a few more disk reading operations
> > because the data of each row is not stored together in one place. I
> > have looked at these references that Dan Clustered Indexes
> > <architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
> > <architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
> > this point. And I wonder how come SqlServer doesn't always store each
> > row together by default' And is this the bookmark operation that the
> > document refering to?
> >
> >
> I'm not sure what you mean by "how come SqlServer doesn't always store
> each
> > row together by default?". In a clustered index, the rows on a given
> page are in order, although pages themselves may be out of sequence.
> The clustered index is itself the table. That's why you can only have
> one on a table. It would be impossible to sort the data two different
> ways.
> On a non-clustered index, as others have mentioned, you only have
> pointers to the actual data. To get there, SQL Server performs a
> bookmark lookup, which is a very fast operation, just not as fast as if
> the query could use a clustered index. Unless the query can make use of
> a covering index (all columns and parameters are in the index itself), a
> bookmark lookup will occur.
>
> --
> David G.
>|||> If
> I understand him correctly, if my table has 40 columns, then the data of
> those 40 columns even for just one row might be scattered in different
> places;
No. one row is always stored together. In fact, one row always fit on one page. (With the exception of text
and image columns, of course.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zeng" <zzy@.nonospam.com> wrote in message news:uD%23bdn6iEHA.396@.TK2MSFTNGP12.phx.gbl...
> From Steve Cass comment, the data of one row in non-clustered index (for
> table w/o clustered index) is not stored in one place, it's fragmented. If
> I understand him correctly, if my table has 40 columns, then the data of
> those 40 columns even for just one row might be scattered in different
> places; so I'm was wondering how come SqlServer doesn't store data of all
> columns belonging to one row in one place just like when there is clustered
> index in the table.
>
> "David G." <david_nospam@.nospam.com> wrote in message
> news:OZawwB5iEHA.2848@.TK2MSFTNGP10.phx.gbl...
> > Zeng wrote:
> > > Is this true? Basically, there are 2 differences between clustered and
> > > nonclustered index:
> > > 1) Nonclustered index will typically require *one* extra disk reading
> > > operations because the leaf nodes are not the data itself, AND
> > > 2) Nonclustered index will require a few more disk reading operations
> > > because the data of each row is not stored together in one place. I
> > > have looked at these references that Dan Clustered Indexes
> > > <architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
> > > <architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
> > > this point. And I wonder how come SqlServer doesn't always store each
> > > row together by default' And is this the bookmark operation that the
> > > document refering to?
> > >
> > >
> >
> > I'm not sure what you mean by "how come SqlServer doesn't always store
> > each
> > > row together by default?". In a clustered index, the rows on a given
> > page are in order, although pages themselves may be out of sequence.
> >
> > The clustered index is itself the table. That's why you can only have
> > one on a table. It would be impossible to sort the data two different
> > ways.
> >
> > On a non-clustered index, as others have mentioned, you only have
> > pointers to the actual data. To get there, SQL Server performs a
> > bookmark lookup, which is a very fast operation, just not as fast as if
> > the query could use a clustered index. Unless the query can make use of
> > a covering index (all columns and parameters are in the index itself), a
> > bookmark lookup will occur.
> >
> >
> > --
> > David G.
> >
>|||Zeng,
The data in a non-clustered index is duplicate data. Suppose table T
has columns A, B, C, D, and E. The data is stored together - all 5
columns and all rows, either in the clustered index, if there is one, or
in what is called a heap. In either case, let's just call it the table.
The table, or the clustered index
A B C D E
A B C D E
A B C D E
...
A B C D E
If we now add a nonclustered index on column D, we will have a second
copy of column D stored away from the table. To make the connection
between these D values and the rest of the table, we need to store
additional information with each D value so we can find the A, B, C, and
E values in the same row as the D value. So the nonclustered index
looks like this (remember, this is stored in addition to the table - the
table also contains a copy of the D values)
The nonclustered index on D
D <location of this row in the table>
D <location of this row in the table>
D <location of this row in the table>
...
D <location of this row in the table>
So now the D values are stored in two places. Once with the whole
table, but probably not in an order that lets us search for a particular
D value, and a second time in the nonclustered index, in order by D
value, but without the rest of the column values - instead of that
information, a reference to the full table row is stored alongside each
D value. The way I think of it, the nonclustered index has "rows" just
like the table does, but they don't include as much information.
So to answer your question, SQL Server always stores the data of all
columns in one place. It may, however, store the data from some columns
in another place (a nonclustered index) *in addition*.to where all the
data is stored together.
SK
Zeng wrote:
>From Steve Cass comment, the data of one row in non-clustered index (for
>table w/o clustered index) is not stored in one place, it's fragmented. If
>I understand him correctly, if my table has 40 columns, then the data of
>those 40 columns even for just one row might be scattered in different
>places; so I'm was wondering how come SqlServer doesn't store data of all
>columns belonging to one row in one place just like when there is clustered
>index in the table.
>
>"David G." <david_nospam@.nospam.com> wrote in message
>news:OZawwB5iEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
>>Zeng wrote:
>>
>>Is this true? Basically, there are 2 differences between clustered and
>>nonclustered index:
>>1) Nonclustered index will typically require *one* extra disk reading
>>operations because the leaf nodes are not the data itself, AND
>>2) Nonclustered index will require a few more disk reading operations
>>because the data of each row is not stored together in one place. I
>>have looked at these references that Dan Clustered Indexes
>><architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
>><architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
>>this point. And I wonder how come SqlServer doesn't always store each
>>row together by default' And is this the bookmark operation that the
>>document refering to?
>>
>>
>>I'm not sure what you mean by "how come SqlServer doesn't always store
>>each
>>
>>row together by default?". In a clustered index, the rows on a given
>>
>>page are in order, although pages themselves may be out of sequence.
>>The clustered index is itself the table. That's why you can only have
>>one on a table. It would be impossible to sort the data two different
>>ways.
>>On a non-clustered index, as others have mentioned, you only have
>>pointers to the actual data. To get there, SQL Server performs a
>>bookmark lookup, which is a very fast operation, just not as fast as if
>>the query could use a clustered index. Unless the query can make use of
>>a covering index (all columns and parameters are in the index itself), a
>>bookmark lookup will occur.
>>
>>--
>>David G.
>>
>
>

Thursday, March 22, 2012

collation conflict for concatenation operation

collation conflict for concatenation operation

collation conflict for concatenation operation

Collation Conflict

Monday, March 19, 2012

COLLATE problem

COLLATE problem

COLLATE problem

Tuesday, February 14, 2012

Clustered index with a truncate table operation question.

Clustered index vs. nonclustered index for GUID primary key

Clustered index vs. nonclustered index for GUID primary key

Clustered index vs. nonclustered index for GUID primary key

Clustered DB Server

Blog Archive

About Me