SA0050 : Do not create clustered index on UNIQUEIDENTIFIER columns

A clustered index determines the physical order of data in a table. Using a UNIQUEIDENTIFIER column as a clustered index can lead to performance issues due to the nature of their values being random.

For example:

1-- Problematic example of a clustered index on UNIQUEIDENTIFIER
2CREATE TABLE Customers (
3    CustomerID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
4    Name NVARCHAR(100)
5);

In this example, the CustomerID column is a UNIQUEIDENTIFIER with a clustered index. This can lead to:

• Frequent page splits as new rows are inserted due to random ordering, causing fragmentation and inefficient disk I/O.

• Increased maintenance overhead for index rebuilding and defragmentation.

Optimize clustered index usage by choosing a column that promotes efficient data storage and retrieval.

Follow these steps to address the issue:

1. Evaluate the table design and determine a suitable candidate for the clustered index. Ideally, select a column with values that increase sequentially, such as an int or bigint.

2. If a sequential alternative for your UNIQUEIDENTIFIER column is necessary, use the NEWSEQUENTIALID() function when inserting data to generate sequential unique identifiers.

3. Update the table definition to modify or remove the clustered index from the UNIQUEIDENTIFIER column.

For example:

1-- Example of a corrected query using an integer column for clustered index
2CREATE TABLE Customers (
3    CustomerID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
4    CustomerNumber INT PRIMARY KEY CLUSTERED,
5    Name NVARCHAR(100)
6);

The rule has a ContextOnly scope and is applied only on current server and database schema.

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