CODEWITHSUNDEEP
sql-serverindexinguniqueidentifier
deckerdev
deckerdev

Reputation: 2792

Clustered/non-clustered index on unique identifier column in SQL Server

I have read the various questions/answers here that basically indicate that having a clustered index on a uniqueidentifier column is a poor choice for performance reasons. Regardless, I need to use a uniqueidentifier as my primary key, and I do NOT want to use newsequentialid() because the generated values are too similar to one another (I need more random IDs so users can't [reasonably] 'guess' another ID).

So, what is the best way to index this PK? Even though a clustered index on this column is not ideal, is it better than a "unique, non-clustered" index?

I'm using SQL Server 2005.

Upvotes: 4

Views: 4474

Answers (1)

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421978

First of all, if you are trying to use NEWID() to achieve randomness (more than just looking random), you're in trouble.

It's usually less than ideal choice because other indexes will reference the clustered index key and a long clustered index key will degrade the performance of all indexes. You could create an IDENTITY integer column and make that the clustered index and just create a non-clustered unique index on the uniqueidentifier column.

Upvotes: 10

Related Questions