SA0049B : The table is created without a clustered index

Introduction

Define a clustered index on tables to improve data organization and query performance.

Description

In SQL Server, failing to define a clustered index directly within a CREATE TABLE statement can lead to performance issues. A clustered index arranges data rows in the table based on the index key, optimizing query performance. When a table is created without specifying a clustered index, or if it’s added in a separate batch, the connection between the index and the table might be overlooked, which can degrade performance.

For example:

-- Example of creating a table without an immediate clustered index
CREATE TABLE Example.Table1
(
    Id uniqueidentifier NOT NULL,
    AltKey datetime NOT NULL,
    Column1 varchar(30) NOT NULL,
    Column2 varchar(60) NOT NULL
);
ALTER TABLE Example.Table1 ADD CONSTRAINT PK_Table1_Id PRIMARY KEY NONCLUSTERED (Id);
ALTER TABLE Example.Table1 ADD CONSTRAINT UK_Table1_AltKey UNIQUE CLUSTERED (AltKey);

Creating a table and then adding a clustered index in a separate batch might prevent the rule from associating the two effectively. Ideally, include the creation of the clustered index within the same batch as the table creation for better clarity and performance.

Lack of clustered index can result in inefficient data retrieval, impacting performance.
Delayed index creation in separate batches may cause oversight or misconfiguration issues.

How to fix

Create a clustered index during the table creation to optimize query performance and ensure proper association.

Follow these steps to address the issue:

1.Define a clustered index directly when using the CREATE TABLE statement to optimize data arrangement and retrieval.

2.Specify the clustered index key within the CREATE TABLE statement, ensuring it is included as part of the table’s definition.

3.Avoid creating clustered indexes in separate batches to prevent potential oversight and maintain clarity in index-table associations.

For example:

-- Example of creating a table with an immediate clustered index
CREATE TABLE Example.Table1
(
    Id uniqueidentifier NOT NULL,
    AltKey datetime NOT NULL,
    Column1 varchar(30) NOT NULL,
    Column2 varchar(60) NOT NULL,
    CONSTRAINT PK_Table1_AltKey PRIMARY KEY CLUSTERED (AltKey)
);

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 Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE Table_WithOut_Pk
(
 CustomerID int,
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

CREATE TABLE Table_With_PK
(
 CustomerID int PRIMARY KEY,
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

CREATE TABLE MySchema.Table_With_Added_Column_PK
(
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

ALTER TABLE MySchema.Table_With_Added_Column_PK ADD CustomerID Int not null not null constraint PK_Table_With_Added_PK primary key clustered;


CREATE TABLE MySchema.Table_With_Added_PK
(
 CustomerID int NOT NULL,
 Company varchar(30) NOT NULL,
 ContactName varchar(60) NOT NULL,
)

ALTER 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

Analysis Rules