473,382 Members | 1,420 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

confusion over indexs via primary key and clustered index

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
Jun 27 '08 #1
4 3109
On May 23, 6:00*am, codefragm...@googlemail.com 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.
Jun 27 '08 #2
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
Jun 27 '08 #3
* 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?
Jun 27 '08 #4
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
Jun 27 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Anthony Robinson | last post by:
I have a question with regads to placement of data files/indexes on multiple filegroups. Here is the current scenario: I have a database comprised of two filegroups - PRIMARY and INDEX. The...
17
by: Philip Yale | last post by:
I'm probably going to get shot down with thousands of reasons for this, but I've never really heard or read a convincing explanation, so here goes ... Clustered indexes are more efficient at...
1
by: M Wells | last post by:
Hi All, I have a database that is serving a web site with reasonably high traffiic. We're getting errors at certain points where processes are being locked. In particular, one of our people...
3
by: Rodney King | last post by:
Hi, I am supporting an application that was converted from ACCESS to SQL Server 2000. My question focuses on two particuliar tables. The parent table has 14000 rows while child table has over...
4
by: serge | last post by:
I ran into a table that is used a lot. Well less than 100,000 records. Maybe not a lot of records but i believe this table is used often. The table has 26 fields, 9 indexes but no Primary Key at...
3
by: William D. Bartholomew | last post by:
I'm working on a system that is very address-centric and detection of duplicate addresses is very important. As a result we have broken addresses down into many parts (DDL below, but I've left out...
14
by: gilles27 | last post by:
I am currently undertaking a review of the primary keys in a SQL Server 2000 database with a view to improving performance of queries. I have heard that, in the case of compound primary keys, it...
5
by: Jerry | last post by:
Is that true that one table can only have one clustered index? The column with clustered index will be sorted physically, is that true? The column with non-clustere index will not be sorted...
115
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.