Clustered Index. SQL

Clustered Index plays a crucial role in how SQL does query optimization.

Note, that creating a primary key automatically makes it a clustered index:

E.g.

[1]

CREATE TABLE [dbo].[T1] (
[id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[customerid] NVARCHAR (MAX) NULL,
[timeinserted] NVARCHAR (MAX) NULL,
[feature] NVARCHAR (MAX) NULL,
[requestcount] INT NULL
);

Execute sp_helpindex [T1]

Output: 

index_name index_description index_keys
PK__T1__3213E83F0D7CDAD3 clustered, unique, primary key id

Note the use of the IDENTITY keyword.  It starts at 1, and keeps incrementing by 1

[2]

If we don’t have a index se up, the stored procedure will tell us accordingly.

CREATE TABLE [dbo].[T2] (
[id] INT IDENTITY (1, 1) NOT NULL,
[customerid] NVARCHAR (MAX) NULL,
[timeinserted] NVARCHAR (MAX) NULL,
[feature] NVARCHAR (MAX) NULL,
[requestcount] INT NULL
);

Execute sp_helpindex [T2]

The object ‘T2’ does not have any indexes, or you do not have permissions.

[3]

So we add the PK constraint to make it a clustered index:

ALTER TABLE [dbo].[T2] ADD CONSTRAINT  PK_Table_id_3    PRIMARY KEY (id)

OR

ALTER TABLE [dbo].[T2] ADD CONSTRAINT  PK_Table_id_3    PRIMARY KEY CLUSTERED (id)

Execute sp_helpindex [T2]

index_name index_description index_keys
PK_Table_id_3 clustered, unique, primary key id

 

References:

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s