confusion over indexs via primary key and clustered index

[codefragment]

Hi
I thought that given a table with an index, primary key and
clustered index any non clustered index look ups would go via the
clustered index and the primary key is irrelevant?
(sql server 2000). A colleague has said that the primary key should be
the clustered index
because all index lookups will go via the primary key.
Is this right? I thought the primary key was nothing more than a
constraint on what data can be entered into the table.
Does it matter if the primary key is the same as the clustered
index?

ta

[Thomas R. Hummel]

On May 23, 6:00*am, [e-mail] wrote:

Hi
* I thought that given a table with an index, primary key and
clustered index any non clustered index look ups would go via the
clustered index and the primary key is irrelevant?
(sql server 2000). A colleague has said that the primary key should be
the clustered index
because all index lookups will go via the primary key.
* Is this right? I thought the primary key was nothing more than a
constraint on what data can be entered into the table.
* Does it matter if the primary key is the same as the clustered
index?

ta

You are correct. Indexes will be based on the clustered index of the
table, which may or may not be the primary key. If the clustered index
is not unique then a row identifier is added to the primary key to
point into the table from the index. I don't recall the exact
structure of that row identifier, but you can probably find a better
description out there somewhere. Try a Google on "SQL Server index
architecture" or something similar. The "Insiders Guide to..." series
of books used to have sections on the inner architectures of SQL
Server I believe. I don't see a version of it for SQL 2005 though, so
maybe that series was discontinued? Or maybe my memory is just
wrong. :)

-Tom.

[Erland Sommarskog]

Thomas R. Hummel (to********@hotmail.com) writes:

You are correct. Indexes will be based on the clustered index of the
table, which may or may not be the primary key. If the clustered index
is not unique then a row identifier is added to the primary key to
point into the table from the index. I don't recall the exact
structure of that row identifier,

It's called uniquifier, and it's a 32-bit integer which is only added
if the key is in fact non-unique.

The "Insiders Guide to..." series of books used to have sections on the
inner architectures of SQL Server I believe. I don't see a version of it
for SQL 2005 though, so maybe that series was discontinued? Or maybe my
memory is just wrong. :)

You are probably thinking of "Inside SQL Server" which definitely is
not discontinued for SQL 2005. It consists of four books, of which the
third "The Storage Engine" discusses things like uniquifiers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[codefragment]

* Is this right? I thought the primary key was nothing more than a

constraint on what data can be entered into the table.

Look-ups often go through the PK, since the reference is through the
PK

I was fine right up until that statement. What does - goes through the
pk- mean? You go through an index, get to
some data via that index, that index may then go through the clustered
index to get to the page. How, mechanically,
does the primary key feature in the lookup?

[Gert-Jan Strik]

co**********@googlemail.com wrote:

>

Is this right? I thought the primary key was nothing more than a
constraint on what data can be entered into the table.

Look-ups often go through the PK, since the reference is through the
PK

I was fine right up until that statement. What does - goes through the
pk- mean? You go through an index, get to
some data via that index, that index may then go through the clustered
index to get to the page. How, mechanically,
does the primary key feature in the lookup?

Erland meant that because of the join relation, the index of the primary
key is often used in joins.

You are correct in stating that when the used index is a noncovering
nonclustered index, then the rows in the table are looked up through the
clustered index, regardless of whether this clustered index enforces the
primary key.

--
Gert-Jan
SQL Server MVP