Click or drag to resize

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
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found next to the source of violation.

IGNORE:SA0050

Remarks

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

Categories

Design Rules

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

 MessageLineColumn
1SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns.31
2SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns.2947
See Also

Other Resources