SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns

The topic describes the SA0050B analysis rule.

Message

Do not create clustered index on UNIQUEIDENTIFIER columns

Description

The rule checks the SQL script for CREATE/ALTER TABLE or INDEX statements which create clustered index on column of type UNIQUEIDENTIFIER.

Consider moving the clustered index to a different column or consider using NewSequentialId() system function for generating sequential unique identifiers. The native uniqueidentifier data type is not suitable for clustered indexing, because causes terrible page splits because its value is completely random.

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1CREATE TABLE cust
 2(
 3 CustomerID uniqueidentifier PRIMARY KEY DEFAULT newid(),
 4 Company varchar(30) NOT NULL,
 5 ContactName varchar(60) NOT NULL, 
 6 Address varchar(30) NOT NULL, 
 7 City varchar(30) NOT NULL,
 8 StateProvince varchar(10) NULL,
 9 PostalCode varchar(10) NOT NULL, 
10 CountryRegion varchar(20) NOT NULL, 
11 Telephone varchar(15) NOT NULL,
12 Fax varchar(15) NULL
13)
14
15CREATE TABLE cust2
16(
17 CustomerID uniqueidentifier DEFAULT newid(),
18 Company varchar(30) NOT NULL,
19 ContactName varchar(60) NOT NULL, 
20 Address varchar(30) NOT NULL, 
21 City varchar(30) NOT NULL,
22 StateProvince varchar(10) NULL,
23 PostalCode varchar(10) NOT NULL, 
24 CountryRegion varchar(20) NOT NULL, 
25 Telephone varchar(15) NOT NULL,
26 Fax varchar(15) NULL
27)
28
29CREATE 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
See Also

Other Resources