3

If your ID column on a table is a unique identifier (Guid), is there any point creating a clustered primary key on the ID column?

Given that they are globally unique, how would the sorting work?

Chris S
  • 64,770
  • 52
  • 221
  • 239

4 Answers4

5

I strongly advise against using clustered Guid key... We had big performance issues on SQL server because of such poor design a few years ago.

Also check out: Improving performance of cluster index GUID primary key

Community
  • 1
  • 1
Igor Brejc
  • 18,714
  • 13
  • 76
  • 95
5

GUIDs as they are are terrible for performance since they are effectively random values (this "breaks" clustered index), and they are awful for indexes, since less entries fit on a single page/extent (SQL Server terms). SQL Server 2005 introduces newsequentialid() which helps solving first problem.

Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
3

Putting a clustered index on a guid column is not such a good idea (unless you're making use of sequential guids).

The clustered index determines the physical order of how the records are stored.
This means that, if you put a clustered index on a column that does not sequentially grow, SQL Server will have some work making sure that the records are correctly ordered physically when you insert new records.

Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
1

The idea of having a sorted index is very good in itself as searching then becomes very efficient.

The problem however is that in case of a GUID one never searches with "WHERE GUID = xyz". So the whole concept is wasted. So I would suggest to rather have a clustered index on a column which is used most often as a SARG to increase the query efficiency.

Learning
  • 8,029
  • 3
  • 35
  • 46
  • 7
    I think "WHERE GUID = xyz" is used a lot, in selecting single records. But ab "ORDER BY guid" would be very rare. – H H Apr 03 '09 at 14:16