SA0050 : Do not create clustered index on UNIQUEIDENTIFIER columns
Introduction
Section titled “Introduction”Avoid using a clustered index on a UNIQUEIDENTIFIER column to prevent performance issues caused by fragmentation.
Description
Section titled “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:
-- Problematic example of a clustered index on UNIQUEIDENTIFIERCREATE TABLE Customers ( CustomerID UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED, Name NVARCHAR(100));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
Section titled “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:
-- Example of a corrected query using an integer column for clustered indexCREATE TABLE Customers ( CustomerID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(), CustomerNumber INT PRIMARY KEY CLUSTERED, Name NVARCHAR(100));The rule has a ContextOnly scope and is applied only on current server and database schema.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.