Why SQL Indexes may not necessarily improve performance?
Amit A
Indexes in SQL database can be your best friend or your worst enemy. Set them up correctly, and your data will be queryable with maximum efficiency.
An index in SQL is created in a table to speed up the data search more quickly and efficiently. Moreover by default an index (Clustered) is already created in a table when one of the column in the table is declared as primary key which is physical storage of data. We can have only one clustered index and hence we are left with only option of creating non-clustered index which is logical storage of data.
A Physical storage of data in case of clustered index, sorts the data and then inserts into the table irrespective of the order in which they were inserted. In the example shown below, five records are inserted and the Column Id is marked as Primary key (Clustered Index). Although the records are inserted in different order but on executing Select command it shows in the order.
On the other hand, the non-clustered indexes is not a physical storage, they are logical storage which contains the pointers to the data. Similar to a textbook where the index is at the end.
We have to be very careful about implementing index in our table. Sometime implementing Index can backfire and it can eventually degrade the performance of a table. Let’s see when not to use INDEXES
When not to use INDEX?
• It should be avoided on small tables
• Tables having frequent DML operations – DML operations would become slow
• Should not be used on columns having high number of NULL values
• Do not apply indexes on the temp tables.
How to avoid using INDEXES?
• In the “where” clause of SQL statement make sure that the column used is primary column.
On which column the indexes should be applied?
In the “where” clause of SQL statement, if the column used is other than the primary column and is frequently used in multiple SQL statement, it would be wiser to consider the column to apply for indexing.
By incorporating these realities of SQL indexes into your database design, you can better ensure that the indexes you build will give you the greatest gains with the minimum overhead.
