Updating an indexed column performance impact in SQL server

Question

I am trying to understand is there any performance impact if I am trying to update a column which is the key column of a non-clustered index in SQL server 2008? For e,g

I have a column IsActive in the table TestTable. I have created a non clustered index on column IsActive. Now I am trying to update the value of the column IsActive through an update query like

UPDATE TestTable SET IsActive= 1 WHERE IsActive = 0

I need to understand will this perform better or not having the index on it will be better. I am asking his as reindexing takes place when index vales are updated. So will this reindexing have a impact on the query performance? Note : The table has excess of 100 million rows.

Answer 1

Modifying the value of a field used in an index will certainly take more time than modifying the value of a field not involved in an index. Having said that, the WHERE clause in your UPDATE statement could be used to speed the search for matching rows when the field in question is part of an index. In this example, where you have a boolean field present in 100 million rows means the selectivity of the field is so poor that a table scan will likely be required anyway. I would estimate that updating this particular field would be quicker if there was no index on it.

However, to answer your question of whether or not there will be performance impact on your system is really only answerable by you since there are a many other variables to consider such as storage speed, RAM availability, etc.

Answer 2

Yes

Updating a indexed column will take more time compare to an non indexed column because

• Index have its own cost like storage
• while you are updates or delete or insert on indexed column by default index must be updated first.

Here your table having millions of records that are being updated means millions of records will be updated first in INDEX as well. So it is going to harm the performance of update query.

"Index is a Database object That make search operation faster if it is build in well manner "

Thanks