Basics Of Clustered And Non-Clustered Index In SQL Server


What is an Index ?

Index is database object. It works the same way as book index do. It can be created on one or more columns (16 Max column combination). It will improve the performance of data retrieval, but it slows down data modification operations such as Insert, Update.

1. Clustered Index:

They are physical stored. Therefore a table can have only one clustered index. When a primary key is created on the table, a clustered index is automatically created. A clustered index is particularly efficient on columns that are often searched. Clustered indexes are not a good choice for columns that undergo frequent changes.

Following is the T-sql syntax of creating Primary key Clustured index:

ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [IDTableName] ASC
)

2. Non Clustered Index:

They are logical ordering of records. A table can have more than one Non-Clustered index. Non clustered indexes can be defined on a table or view with a clustered index. Non-Clustered column always depends on the Clustered column on the database.

Following is the T-sql syntax of creating Non Clustured index:

CREATE NONCLUSTERED INDEX index_nonClustered ON TableName ColumnName

Advertisements

One thought on “Basics Of Clustered And Non-Clustered Index In SQL Server

  1. Pingback: Index in MS SQL Server | Satish Ratnaparkhi's blog

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