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:

SQL
Copy
 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)

How to fix

Create a clustered index for the reported table.

Scope

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

Parameters
Name Description Default Value
IgnoreTemporaryTables

Ignore temporary tables.

yes
Remarks

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

Effort To Fix
5 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 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
  Message Line Column
1 SA0049B : The table Table_WithOut_Pk is being created without a clustered index. 1 13
See Also

Other Resources

Analysis Rules