SA0050 : Do not create clustered index on UNIQUEIDENTIFIER columns

Avoid using a clustered index on a UNIQUEIDENTIFIER column to prevent performance issues caused by fragmentation.

Description

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:

SQL
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.

How to fix

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:

SQL
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);

Scope

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

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

See Also

Other Resources