DF063: The table does not have a clustered index.

Last modified: June 12, 2025

The topic describes the DF063 T-SQL code analysis rule.

Category

PERFORMANCE

Message

The table does not have a clustered index.

Description

Consider creating a clustered index for the table to improve data access and retrieval.

Additional information

The table is stored as a heap, which can lead to slower performance, inefficient I/O, and fragmentation. While heaps are acceptable for temporary or staging tables, most production scenarios benefit from a clustered index, as it defines the physical order of rows and improves query efficiency. It’s recommended to add a clustered index on columns frequently used in queries, such as a primary key or date, to enhance performance and maintainability.

Noncompliant code example

CREATE TABLE dbo.DemoTable
(
    Id INT NOT NULL,
    Code VARCHAR(128) NOT NULL
);
GO

Compliant solution

CREATE TABLE dbo.DemoTable
(
  Id INT NOT NULL,
  Code VARCHAR(128) NOT NULL,
  CONSTRAINT pk PRIMARY KEY CLUSTERED (Id)
);
GO
 
or
 
CREATE TABLE dbo.DemoTable
(
  Id INT NOT NULL,
  Code VARCHAR(128) NOT NULL
);
GO
CREATE CLUSTERED INDEX CL
  ON dbo.DemoTable(Id);
GO