Performance hit with Indexes

One main reason for the index creation on field(s) of a table is to fetch the results quickly. There is a performance hit because of the index. This happens while loading the data (inserting the data). Sometimes, the data insert would take quite a long time if an index exists on a table. To over come this issue, disable the indexes on a table while inserting the data. Once the insert is done, re enable the index.

The following is the syntax to disable the index

ALTER INDEX [IndexName] ON Database.Schema.tablename DISABLE

The following is the syntax to enable the index

ALTER INDEX [IndexName] ON Database.Schema.tablename REBUILD

The main reasons for performance hit while inserting data on a table with indexes are

1. If a table has a clustered index, each and every record inserted has to be sorted. If the table has less records, it will not be an issue. If the table has huge amounts of data, imagine sorting of the whole table for each and every record insert (takes significant amount of time and resources).