Creating Indexes

Imagine a catalogue without an index. It'd take ages to find the exact pair of chrome and ivory toenail curlers you were looking for. A table of view without any index is much the same, with all the data just plonked into a heap

Index types

Unique Indexes - A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique.

Clustered Indexes - With few exceptions, every table should have one clustered index (and only one). Besides improving query performance, a clustered index can be rebuilt or reorganized on demand to control table fragmentation. A clustered index can also be created on a view.

Nonclustered Indexes - You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index.

How to create an index

Here we create a clustered index on the ID column of the Exployees table

CREATE CLUSTERED INDEX Index_Name_Clstd ON Exployees(ID);

Here we create a nonclustered index on the Salary column of the Exployees table

CREATE NONCLUSTERED INDEX Index_Name_NonClstd ON Exployees(Salary);

Here we create a unique index on the StartDate column of the Exployees table

CREATE UNIQUE INDEX Index_Name_Unique ON Exployees(StartDate);

Best Practice

• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-clustered index
• Clustered index does not have to be on the Primary Key (but it is by default)
• Clustered index should be on one column to be used most frequently in queries
• Consider fill factor when creating indexes
• Consider Index Tuning Wizard for recommendations and DETA for more recommendations based on usage
• Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause