SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns

Introduction

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

Description

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:

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

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.

How to fix

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:

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

Scope

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

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

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE cust
(
 CustomerID uniqueidentifier PRIMARY KEY DEFAULT newid(),
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
 Address varchar(30) NOT NULL,
 City varchar(30) NOT NULL,
 StateProvince varchar(10) NULL,
 PostalCode varchar(10) NOT NULL,
 CountryRegion varchar(20) NOT NULL,
 Telephone varchar(15) NOT NULL,
 Fax varchar(15) NULL
)

CREATE TABLE cust2
(
 CustomerID uniqueidentifier DEFAULT newid(),
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
 Address varchar(30) NOT NULL,
 City varchar(30) NOT NULL,
 StateProvince varchar(10) NULL,
 PostalCode varchar(10) NOT NULL,
 CountryRegion varchar(20) NOT NULL,
 Telephone varchar(15) NOT NULL,
 Fax varchar(15) NULL
)

CREATE CLUSTERED INDEX IX_CustomerID ON cust2 (CustomerID)

Analysis Results

	Message	Line	Column
1	SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns.	3	1
2	SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns.	29	47

Analysis Rules