---

Search posts under SQL Server:

---

Creating Indexes in SQL Server

How to create clustered and non clustered indexes in the Sql Server database table?

Creating Indexes in the database table columns helps us to search the data on that particular easily and quickly. The overall performance of the searching becomes much better.

When we create a primary key in the table, by default it creates clustered index on the primary key column.

• Clustered Index: Clustered index sorts and stores the data rows in the table based on their key values when we insert the record. In general, there is only one clustered index in a single table.
• Nonclustered Index: A nonclustered index contains index key values and each key value entry has a pointer to the data row that contains the key value in the database table.

          It is generally created on columns other than primary key on which search needs to be done to improve the search           performance.

So to create a clustered index, we do not need to do anything. Just creating a primary key in the table does the job.

To create nonclustered Index, right click the column in the design view and select Indexes/Keys…

This shows Indexes/Keys dialog box as displayed below

Now click on Add button and change the (Name) value to some meaningful name to make this name unique. Now click on … button that shows Index Columns dialog box.

Select Column name on which we need to create nonclustered index and sort order. Click on OK and then Close on the Indexes/Keys dialog box. Now click on Save icon to save the index created.

There can be more than one nonclustered index in a single table. However it is suggested not to create unnecessary indexes as these indexes also has an overhead and instead of increasing performance it may decrease the search performance.