April 7, 2003 at 3:49 am
Are there any performance differences when selecting rows on a table with a clustered primary key unique constraint compared to a table with a clustered index:-
ie.
ALTER TABLE [dbo].[MyTable] WITH NOCHECK
ADD CONSTRAINT [PK_ID]
PRIMARY KEY CLUSTERED ([ID])
WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE CLUSTERED INDEX [IX_ID]
ON [dbo].[MyTable] ([ID])
WITH FILLFACTOR = 90 ON [PRIMARY]
I have always thought that it is best to create a clustered primary key on a table, adding indexes to other fields where appropriate (even when the table is static ie. no new rows?).
April 7, 2003 at 5:18 am
Primary keys biggest bennifit is to prevent insertion of duplicates. However if the majority of your queries do not use the PK column you will see better query performance for all queries by clustering the most utilized column. As this physicall orders the data on the pages it will mean faster accessing. In addition all non-clustered indexes use the clustered index to find the data on the pages so you elminate some page jumps required for the query to obtain its data. The more often you can eliminate the faster your overall performance. As for if a duplicate is inserted the contraint will scan only it's index so the PK does not lose any bennifit in that area it just would not be your best query canidate.
Now another concern you may need to watch for is index density. Since the PK is denser and thus has a better statisitcal value than say your other column if you opt for another it may choose the PK index instead. Test and use index hints as needed.
You will here folks say that using the most unique column for your index is best. That is generally so but no always so you should compare the performance gains overall for either.
April 8, 2003 at 3:08 am
Thanks for your help
April 8, 2003 at 10:18 am
According to Inside SQL Server 2000 by Kalen Delaney (p 410): "In terms of storage and maintenance of indexes, there is no difference between unique indexes created using the Create Index command and indexes created to support constraints."
April 9, 2003 at 3:51 am
In that statement it is true
quote:
In terms of storage and maintenance of indexes
beign the key. However, there are performance difference between both situations.
See the following thread for additional information http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=10360&FORUM_ID=9&CAT_ID=1&Topic_Title=clustered+constraint+vs+clustered+index&Forum_Title=General
However, your point does not fit into this thread as it is the difference between having the Primary Key clustered and andother index clustered. There are differences in the storage of clustered and non-clustered indexes.
1) The clustered index is the physical order of the table.
2) Clustered index is the only index that contains, within it Root/Leaf pages, the page number information where the data pieces are found.
3) All non-clustered indexes are internally sorted by their data values, in their Root/Leaf pages.
4) The non-clustered index contains data value with related clustered index value (this means you laways use the Clustered index to ultimately find your data).
5) A clustered index does not have to be unique as well.
This is very basic list of the differences. You should because the fact non-clustered indexes have the related clustered index values in them be carefully with using a composite index (one with more than one column as the indexed values) when used a s a clustered index as they will impact the size of all non-clustered indexes. Also, uniqueness is used in the overall choice of index picked by the query manager so you have to be sure your choice for the clustered index gives the best performance.
Hope that helps.
May 14, 2003 at 6:10 pm
Definitions:
Primary Key = A unique key, disallowing Nulls. It is a concept from relational theory, where a Primary Key is one type of Constraint (Unique constraint is another, very similar), and in principle has nothing to do with indexes; but in practice, enforcement would be too inefficient without an index, so SQLServer always creates an index for a primary key.
Clustered index = a sortorder imposed over the physical data pages ("physical order of the table" is OK too).
The index SQLServer automatically creates for a Primary Key defaults to a Clustered index, but you can make it nonclustered, and create a Clustered index separately.
I think fuzziness about these terms is a common source of confusion.
May 15, 2003 at 8:23 pm
I certainly don't claim to be an expert, but I do have some firm opinions on the choice of clustered indexes. Here's what I generally go by:
1. All tables should have a clustered index for space management
2. The PK is usually a poor candidate as a clustered index
3. When deciding on a field to use as a clustered index, I consider what will be most commonly used as criteria when retrieving a range of records. A classic example would be Order Detail lines. In all likelyhood the best candidate will be the OrderID column.
The reason the clustered index is important for multiple record retrieval, is because the records are physically stored in the order of the clustered index, when SQL does it's first physical read, the page it reads is going to be packed with records it's interested in (hopefully multiple records per page - that's why short record length is good for performance). Then if it still needs more records, it just needs to do sequential page reads from the same general area of the disk.
Hope this helps,
Tim Elley
May 17, 2003 at 2:16 am
with regard to Antares686's comment:
"4) The non-clustered index contains data value with related clustered index value (this means you always use the Clustered index to ultimately find your data)."
What if there is no clustered index/PK (just a non-clustered index) - does SQL server then simply ignore the non-clustered index and revert to a a table scan?
That would imply that a non_clustered index is redundant if not used in conjunction with a PK/clustered Index.
I am sure that's not right, so what am i missing?
thanks
May 18, 2003 at 8:00 am
pls igore previous posting - i have found the answer in the following excellent article:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply