Does the number of indexes effect the modification of table?

Question

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 ?

Answer 1

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).

Answer 2

The number of indexes will affect the inserts and and updates you do on your table. If you add more columns there is not going to be an inversely decreasing performance due to the current 20 indexes. Just the extra effort needed to populate the new columns.

If you remove the unwanted indexes, it will improve the read/write performance on your table. Do you need more indexes that the number of columns in your table?