Click or drag to resize

SA0049B : The table is created without a clustered index

The topic describes the SA0049B analysis rule.

Message

The table is created without a clustered index

Description

The rule checks all the CREATE TABLE statements for not having a clustered index defined using a table constraint or separate CREATE INDEX statement.

Note Note

If the index for the table is created separately, the ALTER TABLE or CREATE INDEX statement which adds the index should reside in the same batch as the CREATE TABLE statement. This is required for the rule to be able to connect the index with the created table.

Example:

 1CREATE TABLE Example.Table1
 2(
 3 Id uniqueidentifier NOT NULL,
 4 AltKey datetime NOT NULL,
 5 Column1 varchar(30) NOT NULL,
 6 Column2 varchar(60) NOT NULL, 
 7)
 8
 9ALTER TABLE Example.Table1 ADD CONSTRAINT PK_Table1_Id primary key nonclustered (Id)
10
11ALTER TABLE Example.Table1 ADD CONSTRAINT UK_Table1_AltKey unique clustered (AltKey)

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:SA0049B

Remarks

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

Categories

Performance Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1CREATE TABLE Table_WithOut_Pk
 2(
 3 CustomerID int,
 4 Company varchar(30) NOT NULL,
 5 ContactName varchar(60) NOT NULL, 
 6)
 7
 8CREATE TABLE Table_With_PK
 9(
10 CustomerID int PRIMARY KEY,
11 Company varchar(30) NOT NULL,
12 ContactName varchar(60) NOT NULL, 
13)
14
15CREATE TABLE MySchema.Table_With_Added_Column_PK
16(
17 Company varchar(30) NOT NULL,
18 ContactName varchar(60) NOT NULL, 
19)
20
21ALTER TABLE MySchema.Table_With_Added_Column_PK ADD CustomerID Int not null not null constraint PK_Table_With_Added_PK primary key clustered;
22
23
24CREATE TABLE MySchema.Table_With_Added_PK
25(
26 CustomerID int NOT NULL,
27 Company varchar(30) NOT NULL,
28 ContactName varchar(60) NOT NULL, 
29)
30
31ALTER TABLE MySchema.Table_With_Added_PK ADD CONSTRAINT PK_Table_With_Added_PK primary key (CustomerID)

Analysis Results

 MessageLineColumn
1SA0049B : The table Table_WithOut_Pk is being created without a clustered index.113
See Also

Other Resources