A collegue of mine asked me some questions about a clustered index. He was preparing for a SQL Exam and he had trouble understaning some concepts. He had trouble with the concepts not because they were too difficult for him, but they were completely opposite to what he experienced in everyday programming life !!!!
To make a long story short.
A lot of programmers have a GUID as a Primary key.
In the Books online that come with SQL Server there is the following statement :
PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint
So if there is no other index on a table, the primary key will be clustered. (so far nothing wrong) But if you have a GUID as a PK then let's have a look at what the books online say about the UniqueIdentifier.........
The uniqueidentifier data type has several disadvantages:
- The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.
- The values are random and cannot accept any patterns that may make them more meaningful to users.
- There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.
- At 16 bytes, the uniqueidentifier data type is relatively large compared to other data types such as 4-byte integers. This means indexes built using uniqueidentifier keys may be relatively slower than implementing the indexes using an int key. Consider using the IDENTITY property when global uniqueness is not necessary, or when having a serially incrementing key is desirable.
Besides That. There is also another Disadvantage on having a GUID as the primary key. The data type is relatively large......(also from the books online)
Wide keys , The key values from the clustered index are used by all nonclustered indexes as lookup keys and therefore are stored in each nonclustered index leaf entry.
So every record refers to it's position in the table with the GUID. Since a guid is 16 bytes and an ID is 4 the index with the GUID is much larger and will there therefore be slower.....
Hope this is usefull for someone......