October 18, 2010 at 5:06 am
This question sounds the wrong way round but, assuming I have a clustered primary key, can it ever be a performance DIS-advantage to add the primary key columns to the end of an index to make it a unique index? For example, when would idx1 be better than idx2 in the following:
CREATE TABLE #myTest (
myPK INT PRIMARY KEY,
myNonUniqueCol INT NOT NULL,
lotsOfOtherData CHAR(1000) NOT NULL)
CREATE INDEX idx1 ON #myTest (myNonUniqueCol)
CREATE UNIQUE INDEX idx2 ON #myTest (myNonUniqueCol, myPK)
I can see a few cases, such as where some columns from the PK would be in the index and reordering the columns would be a problem, but in most of my tables the PK is a single column. I have tables with effectively both indexes, idx1 to be narrow and idx2 to be covering, both end up being heavily (and about equally) used, usually by small queries (making it hard to spot performance differences) and I'm sure one is redundant. I assumed it might be that idx1 is smaller on disk by a recent blog post by Gail http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/ suggested there was little real difference to size:
If the columns that need to be indexed aren’t unique themselves, we can always add the primary key column(s) to the unique constraint so that the combination is always unique. If the primary key is enforced by the clustered index, this does not make the index any wider than it would be were it defined as a non-unique index with Create Index, as a non-unique non-clustered index always gets the clustering key added to the key columns.
Many thanks in advance!
Paul
October 18, 2010 at 5:46 am
No, because SQL will always add the clustering key as an invisible key column to a nonclustered index that's not declared unique. You're just doing explicitly what SQL would do implicitly.
Note that in the blog post you reference I was talking about table variables where the only indexes you can add are pk or unique constraints.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply