How to Create Effective Index in SQL Server

Indexes are the primary source of the databases. Terribly designed indexes is one of the major problem faced by the user. Designing it properly is the major task as it is used to achieve best application performance. Let us understand first, What are Indexes?

Indexes are basically used to speed up searching in the database. It is an on-disk structure which is associated with a table or view. An index contains keys which can be built from one or more columns. Selecting the right index is a complex task. Before choosing the right index for the database, you have to experiment with different designs. You can add, modify, drop indexes without affecting the schema of your database.

Let us now understand how can you design effective indexes so that you can meet your need of application.

Index Designing Guidelines

Consider the following guidelines for designing an index.

• If you design large number of indexes on a table, it affect the performance of Insert, Delete, merge statement . So try to avoid over-indexing with few number of column as possible.
• If you want to improve the query performance, use many indexes with less update operations.
• If the view contain joins, aggregations or combination of both, then indexes on view can give you the best performance gains.
• Try to avoid indexes on small tables as query optimizer take quite a long time to traverse the searching for data than to perform simple single table scanning.
• Avoid adding unnecessary column as adding them affect the performance of indexes.
• One statement query can exploit the maintenance of optimized index.
• For clustered indexes, the length of the index key must be short.
• Data type like ntext, image, varchar, nvarchar used in column are not specified as index key column. & Datatype having varchar(max). nvarchar(max),xml are considered as nonkey index columns.

Clustered Index Design Guidelines

Clustered indexes mainly store the data rows based on the key values. Let us understand its basic architecture first:

Indexes are organized in B-trees & the page in it is called index node. The top node is called as root node whereas the bottom node is called as leaf node. Level between the root & leaf node is called intermediate level. The leaf node in the clustered index contain the data page whereas the root & intermediate level nodes contain index pages. Each row of index contain the key value along with the pointer to either an intermediate level page or any data row.

By default, Clustered indexes has a single partition having one row in sys.partition with index_id=1 for every partition used by the index.

Before creating Clustered indexes, understand how your data works:

1. Return number of values by using operator Between,<,>,>=,<=
2. Use clauses GroupBy or OrderBy, Join
3. Return number of large result sets.
4. Try to define as few columns as possible in clustered index key.
5. Columns in clustered index should be accessed sequentially.

Note: Columns that undergo frequent changes & indexes containing Wide keys are not good attributes for he Clustered Indexes.

Non-Clustered Index Design Guidelines

Non-Clustered Index are those indexes in which logical order of the indexes are different from physical ordering of the indexes. It contains non-clustered index key values in which each has a entry of pointer of the data row. For storage location of tables data, the non clustered indexes contain key values & row locators.They are basically designed to improve the performance of the queries which are not listed by clustered indexes.

The structure of Non clustered indexes are same as clustered indexes. But in Non clustered indexes data rows are not sorted & stored according to keys. And the leaf layer is created by index pages.

Similar to clustered indexes, non-clustered indexes has one row in sys.partition having index_id>1 for every partition.

Let us discuss the basic guidelines one should follow while designing nonclustered indexes:

1. Database or table carrying large amount of data with low update operations improve query performance.
2. Databases having read-only data & DSS(Decision Support System) application benefits non-clustered indexes.
3. Try to avoid over-indexing on highly Update operation & database using OLTP application.

Before creating non-clustered indexes, one should understand the accessing of the data. Below are some points:

1. For queries, Use Clauses like JOIN or GROUP BY in non-clustered index. You can create multiple non clustered indexes that has these clauses.
2. When the index contain all the columns in the query, one can achieve the appropriate gain in the performance.
3. All data type can be used in non clustered indexes except text, ntext, image.
4. The column name must not be repeated in INCLUDE List
5. INCLUDE list & key column list must not be specify column names.
6. One key column must be defined under column size guideline. The maximum number is 1023
7. Try to avoid adding unnecessary columns. Adding them leads to poor performance issue like less cache efficiency, more disk space etc
8. Design your indexes with large number of index key size for easy searching of key columns.

Conclusion

The blog discusses the best index practice one should follow while designing the indexes. I have explained about basic Index guidelines(Clustered & Non Clustered) for designing indexes to resolve the complexity of Indexes.