Any valid reason to use a unique column in composite index?

Question

Any valid reason to use a unique column in composite index?

MadAdmin

SSChampion

Points: 11261
More actions
September 28, 2011 at 12:58 am

#243789

Does anybody know a good reason why we would build a composite index using RID where RID is a PK?
index = [RID,Col2,Col3]
My not so Common sense dictates that if RID is a PK (unique), then by querying on RID, you will get a unique record, and no further condition should be needed to refine the results. So then a composite should only contain non unique columns.
But maybe there is a valid reason I am not aware of since a DBA I am not.
I cannot find any info regarding this issue, but all the examples always have non unique columns in the composite key.
Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Like that there's seldom a reason, especially if you're always doing equality matches on the column. If the columns were in another order, there could be a use.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MadAdmin SSChampion Points: 11261 More actions · Answer 2

Thanks Gail.

Would you even consider this as a way to do avoid a lookup to the table for col2,and 3?

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Not unless the primary key is enforced by a nonclustered index.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MadAdmin SSChampion Points: 11261 More actions · Answer 4

Thanks Gail

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

I have used a unique value in a compound index before, but not one that was already indexed, let alone the PK on the table and, I assume, the clustered index.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

gofrancesc Hall of Fame Points: 3269 More actions · Answer 6

I usually try to avoid non-unique indexes, adding the primary key after the key fields; in this case, an index (Col2, Col3, RID) has sense for me. It is a common practice?

Francesc

palash.gorai Mr or Mrs. 500 Points: 567 More actions · Answer 7

Hi,

Composite Index is used when you want to have only the Unique Records in a combination of more than one column..

For Instance, in Email DB, you want to have unique email id from a particular Competition webpage then Create Composite Key on SourceID(refering to Particular Competion) and Email ID. This is will capture the email id once from a particular competion page.

Regards

Palash Gorai

MadAdmin SSChampion Points: 11261 More actions · Answer 8

frfernan (9/29/2011)
I usually try to avoid non-unique indexes, adding the primary key after the key fields; in this case, an index (Col2, Col3, RID) has sense for me. It is a common practice?
Francesc

I think it is common practice if your table is a heap since if you exclude RID from the index, the query will need a lookup from the index to the table to retrieve the rid if it was not included.

But then the first question is, why is the table a heap?

But if the table is clustered on RID, my guess is that the RID is implicitly included in the non clustered index as part of a pointer to the table record so it can satisfy a query which outputs an RID with the index by itself without io to the table.

So an index on [col2]when table clustered on RID should work better than an index with [col2,RID] since the index is smaller but should work the same.

Of course of this I am not 100% so hopefully we can have more participation. So I'll start a new topic.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

paul.jones SSC Eights! Points: 854 More actions · Answer 9

One possible use of an index on (RID, Col2, Col3) would be as a covering index for some query - if your table's columns are RID, Col2, Col3, BigVarcharCol4, BigVarcharCol5, BigVarcharCol6, ..., and all you need is Col2 and Col3 for a range of RIDs, a scan on the smaller index could be more efficient than the corresponding clustered index scan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

frfernan (9/29/2011)
I usually try to avoid non-unique indexes, adding the primary key after the key fields; in this case, an index (Col2, Col3, RID) has sense for me. It is a common practice?

No. A column should be added to an index only if it's needed in that index. If it's not needed, it should not be specified. Conversely, if a column is needed it should be specified and SQL's behaviour regarding the clustered key not replied upon.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass