2
Vote

Do not use CLUSTERED when creating PK's on GUIDs

description

CLUSTERED means it will physically sort the data on the hard disc in the order of the PK. This gives the fastest lookup performance. However, the GUIDs (which are random) for inserts means that it will not be added to the end of the database, but somewhere in the middle. This means a re-sort upon insertion, and because its CLUSTERED it has to resort the data on the hard disc meaning slow inserts.
 
CLUSTERED indexes are ideally suited to integer IDENTITY columns only.
You should simply remove the CLUSTERED keyword from the database schema creation SQL against PK's that are using GUIDs (uniqueidentifier).

comments