What should be considered before creating indexes?.

Question

What should be considered before creating indexes?

Rajanikant Hawaldar · Answer

Hello Vinay, You should consider the following guidelines when planning your indexing strategy: 1) For tables that are heavily updated, use as few columns as possible in the index, and don’t over-index the tables. 2) If a table contains a lot of data but data modifications are low, use as many indexes as necessary to improve query performance. However, use indexes judiciously on small tables because the query engine might take longer to navigate the index than to perform a table scan. 3) For clustered indexes, try to keep the length of the indexed columns as short as possible. Ideally, try to implement your clustered indexes on unique columns that do not permit null values. This is why the primary key is often used for the table’s clustered index, although query considerations should also be taken into account when determining which columns should participate in the clustered index. 4) The uniqueness of values in a column affects index performance. In general, the more duplicate values you have in a column, the more poorly the index performs. On the other hand, the more unique each value, the better the performance. When possible, implement unique indexes. 5) For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = ‘Charlie’) should be listed first. Subsequent columns should be listed based on the uniqueness of their values, with the most unique listed first. 6) You can also index computed columns if they meet certain requirements. For example, the expression used to generate the values must be deterministic (which means it always returns the same result for a specified set of inputs). For more details you can search Creating Indexes on Computed Columns.

Shiv · Answer

If a particular column is searched frequently, you can achieve performance benefits by creating an index on that column.

Laxmidhar Sahoo · Answer

Hi One thing which field you used in where condition frequently that field should be indexised Thnks and regards

What should be considered before creating indexes?.

Answers (3)