Best Practices For Sql Server Indexes

Most Sql Server professionals allege that creating indexes is a double-edged sword. In other words, if only we use best practices to create and design indexes then we will achieve performance improvement. So how can we create the best-desired indexes for our tables?

First of all, you need to learn the ins and outs of your database design on which the SQL Server index will be created. If your database is based on Online Transaction Processing (OLTP) then you have to be careful with creating large number of indexes. If you are constantly Inserting, Updating, or Deleting records of a table, having many indexes on the table will affect your performance. I know some developers would disable their indexes before using these DML statements and then they would enable them to overcome the performance penalty. So, the best practice here is to create the minimum number of indexes in the OLTP tables with the least possible number of columns participating in the index’s key.

On the other hand, if your database is based on Online Analytical Processing (OLAP), Selecting data and data retrieval should be as fast as possible to retrieve a large amount of analytical data for analysis or reporting purposes. In this case, I highly recommend using column-stored indexes. It achieves high data compression and a significant improvement in query performance on large data warehousing facts. If you have analytics queries with multiple aggregation functions, the column-stored index is for you.

Another important fact that needs to be considered is table size. It is best practice not to use indexes for small tables, since there would be no performance enhancement. Your table is small and by default, Sql Server Query Optimizer will prefer scanning the whole table rather than using the index and performing index seek operation.

Probably one of the most important things you need to analyze before creating indexes is your frequent queries! I mean in the most frequent queries that I execute, what are the most targeted columns that need to be retrieved? What columns do I use more in Predicates, JOIN conditions, LIKE, and the ORDER BY clause? You need to know that not all data types should be considered key indexes. The best candidate data type for the SQL Server index is the integer column due to its small size. On the other hand, columns with text, ntext, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot participate in the index key. If a column is frequently used in the aforementioned parts and its data type is an integer and also it is with unique and not null values, then we have a winner here to be part of our Index key. Of course, we do not live in a perfect world where you can find all of these characteristics in a column. But look for most of its characteristics in your columns and if you can't, you should have designed your tables meticulously!

After that, you need to consider the order of columns in the index key. For instance, if you use col A before using col B in your queries, then the order of your index key should be as (Col A, Col B). Also, when creating an index, you have the option to specify whether the index is created in ascending or descending order.?This can be done simply by using the keyword?ASC?or?DESC. You need to know that creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.

Please be really strict about choosing your clustered index key columns. For crying out loud, do not blindly use your Identity column as your clustered index key in the creating table wizard!

Another vital matter is where your indexes reside in your storage. It is best practice to create non-clustered indexes in a separate filegroup on a different disk drive where the main table is created.

In this article, I tried to cover the most important factors that need to be taken into consideration when defining an index. Thank you for reading it.