SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns

In SQL Server, a clustered index organizes the rows of a table based on the index key. Creating a clustered index on a UNIQUEIDENTIFIER column may cause performance degradation because the GUIDs are randomly generated, leading to fragmented data storage, increased page splits, and potentially decreased I/O efficiency.

For example:

1-- Example of a problematic clustered index
2CREATE TABLE ExampleTable (
3    Id UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
4    Name NVARCHAR(100)
5);

This example might cause issues because the Id column as a clustered index can lead to data fragmentation due to its random nature, affecting overall query performance and storage efficiency.

• Increased page splits due to random inserts can degrade performance.

• The storage layout may become fragmented, requiring more frequent maintenance operations such as index defragmentation.

This section provides strategies to resolve performance issues caused by creating a clustered index on a UNIQUEIDENTIFIER column.

Follow these steps to address the issue:

1. Evaluate your data model to consider whether a different column might be better suited for the clustered index, such as an integer-based column that increments sequentially.

2. If you must use a GUID, modify the data generation strategy for the UNIQUEIDENTIFIER column by using the NewSequentialId() function to create sequential identifiers instead of random ones, which can help reduce fragmentation.

3. Recreate the clustered index on the chosen column to optimize the storage and improve query performance.

For example, using the NewSequentialId() function:

1CREATE TABLE ExampleTable (
2    Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED,
3    Name NVARCHAR(100)
4);

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Design Rules, Bugs

There is no additional info for this rule.

Other Resources