Sql-server – Does the number of indexes effect the modification of table

indexperformancesql-server-2005

First of all Good morning !

Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !!

Now come to the point,

Suppose I create 20 indexes ( obviously non-clustered) on one table (which have id,name,username,password). It will work fine.

Now the point is if I modify my table as (id,name,username,password,status,varification_code) and indexes are remains 20. Will it effect the performance ?

And what will be if indexes increase or decreases and table remains same ?

And what will be if indexes increase or decreases and table modified ?

Best Answer

Every time you update a record in the table, all indexes whose entries have changed because of that also need to be updated. So there more indexes you have, the longer the update will take.

Usually that is not a concern, but if you bulk-load a lot of table, it might be a good idea to disable all indexes (and constraints) and re-enable them after the load is done (as index rebuilding is faster than continuous updates).

Related Question