Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to tune a stored procedure which has a temp table used for matching (only retrieval, no updates or inserts). We're considering applying a clustered key to a temp table. We're also considering applying a primary key (clustered).

Aside from the unique/non-null constraint of a primary key, are there any differences performance wise between clustered indexes and primary keys in regards to looking up values in the temp table?

Thanks.

share|improve this question

migrated from stackoverflow.com Nov 29 '12 at 7:11

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 5 down vote accepted

A clustered index will dictate the order of the data in the pages of your table. When your temp table is initially populated, the inserted data will be sorted or, if you're doing multiple inserts to populate, it's possible you will end up with some page fragmentation as the pages must split to accommodate additional data being written.

Assuming your temp table is populated in a single insert, and you have no page fragmentation, the next issue that may or may not affect your performance is the presence of a 4 byte "uniqueifier." Because the clustered key is used as a pointer to each row in the table, it must be unique for that purpose. So if you're using a non-unique clustered index, every nonclustered index (and your clustered index) is going to be 4 bytes wider than you've defined which will affect page density and IO required to perform the query. This will likely only be of very marginal impact as the uniquifier will only be added to duplicates.

The other factor that you need to consider is, how are you joining to this temp table. SQL Server supports 3 physical join operators; Merge, Loop, and Hash. Merge can yield the best performance in many situations.

Unlike the nested loops join where the total cost may be proportional to the product of the number of rows in the input tables, with a merge join each table is read at most once and the total cost is proportional to the sum of the number of rows in the inputs. Thus, merge join is often a better choice for larger inputs.

It is unlikely SQL will chose a merge operator unless both datasets being joined are sorted by the join criteria. There are other factors as well, of course, but if you can optimize your table for specific, common usage patters, all the better.

The second thing to consider is any other indexes you are going to place on that table since each nonclustered index will contain the keys specified (in the order specified) and the pointer to the clustered index to facilitate the lookup and retrieval of any fields not covered by the index. Again, a narrow clustered key will yield better density in your non-clustered indexes and thus better i/o performance for index scans and seeks. If your nonclustered indexes require many lookups to the clustered index either consider including those columns in your index (sacrificing page density for reduced lookups) or consider clustering on that criteria (if this is your primary usage pattern)

The biggest factor that would steer me towards using a primary key over a unique (or non-unique clustered index) would be the ability to create the index inline in the create table statement. Historically, I have found that the optimizer is less apt to use indexes that I created inside my stored procedure after the create table statement. It's easier to create a primary key inline with the create table statement. I'm not sure if it's possible to do this with a non-constraining index but you can create a unique clustered index inline, as SQL Kiwi pointed out below.

share|improve this answer
    
+1 excellent write-up ! –  marc_s Nov 29 '12 at 11:30
2  
Uniqueifier bytes are only added as required. If there are no rows with duplicate clustering keys, there will be no uniqueifiers (that's why the normally fixed-length integer type of the uniqueifier appears in the variable-length portion of a FixedVar format row). Merge is not 'the most optimal' physical join operator; each has strengths and weaknesses making them 'optimal' in different scenarios. It would be clearer to note that a UNIQUE CLUSTERED constraint can be declared in a CREATE TABLE statement. The comment re: the optimizer is misconceived. –  Paul White Dec 1 '12 at 4:15
    
All good points. I did not know they uniquifier is only added when necessary, thank you. My comment regarding the optimizer was based on my past observations and specific tests but this may be different in the more recent version of SQL Server. –  Code Magician Dec 1 '12 at 6:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.