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:

    [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

One comment

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s