confusion over indexs via primary key and clustered index

Thomas R. Hummel ( ) 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, esquel RemoveThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: confusion over indexs via primary key and clustered index 

(codefragment@googlemail.com) writes:
> 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?

Look-ups often go through the PK, since the reference is through the
PK. If you have a query like:

SELECT O.OrderDate, O.OrderID, C.CompanyName
FROM Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE O.EmployeeID = 1

The access to the Customers table is like to use the PK, no matter whether
this index is clustered or not.

Sometimes it is a very good idea to have the PK as the clustered index.
Sometimes the PK is a poor choice for the clustered index. For instance,
for an Orders table the order id is not likely to be a good choice.
How often do you run queries like "give me all orders with order
id between 11000 and 12000?". The order date, the customer id and
the employee id are all better choices.

On the other hand, for an OrderDetails table, it make sense to cluster
on the PK, because you will often read all details for one order.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: confusion over indexs via primary key and clustered index 

> >   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?
 >> Stay informed about: confusion over indexs via primary key and clustered index 

codefragment.TakeThisOut@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
 >> Stay informed about: confusion over indexs via primary key and clustered index 

Erland Sommarskog wrote:

> Sometimes it is a very good idea to have the PK as the clustered index.
> Sometimes the PK is a poor choice for the clustered index. For instance,
> for an Orders table the order id is not likely to be a good choice.
> How often do you run queries like "give me all orders with order
> id between 11000 and 12000?". The order date, the customer id and
> the employee id are all better choices.

At least one of my clients runs queries like that regularly, to guard
against misplaced paperwork. (The database is part of a stock software
package; I'm not sure what clustered index its designers chose.)
 >> Stay informed about: confusion over indexs via primary key and clustered index