I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute.

While tuning my database to improve performance, I keep coming across this situation that seems to contradict that rule logically for me, and nowhere can I find anyone say or explain in any way otherwise.

In SQL Server, and I believe/presume most other DBMS, your indexes are created based on specific columns you specify. Inserts and deletes will always affect an entire row, so there is no way they will not affect the index, but updates seem a bit more unique, they can specifically affect only certain columns.

If I have columns that are not included on any index and I update them, are they slowed down just because I have an index on other columns in that table?

As an example, say in my User table I have one or two indexes, the primary key which is an Identity/Auto Increment column, and possibly another on some foreign key column.
If I update a column without the index directly on it, like say their phone number or address, is this update slowed down because I have indexes on this table on other columns in either situation? The columns I am updating are not in indexes, so logically, the indexes should not be updated, shouldn't they? If anything, I would think they are sped up if i use the indexes in the WHERE clause.


4 Answers 4


You are correct that updating an non-indexed column will not cause changes to the indexes. In a simple case, there would also be no overall impact on the table.

If a query can use the Index to look up data, it may speed up the lookup, but the exact behavior (depending on your SQL brand) may differ from other brands of SQL. (I use Microsoft SQL Server primarily.)

Of course, updating a column with a significantly greater volume of data could cause some moving of rows to different pages, et cetera.


For a relatively fast modern system the addition of a single index to an OLTP table will probably be virtually undetectable from a performance standpoint for the vast majority of systems. That said, you should not create needless indexes, and you probably should not create single-column indexes for every column in a table.

You are correct in the assumption that for many queries the presence of useful indexes will result in a very noticeable speed improvement.

Although your question appears to be around performance, there are several other potential issues around adding indexes, including but not limited to:

  1. Time required to create the index may result in blocking while the index is added to the table. The lock is very short lived, and most likely won't create a big problem.

  2. Index changes result in execution plans being invalidated for any plans that reference the underlying table. When those execution plans are recompiled, performance may change negatively for some queries.

  3. Index modifications may result in queries returning errors where none were previously returned. Take the case of a filtered index that was used to return dates contained in a varchar field; if the filter eliminated any rows that weren't dates, and that filter is subsequently changed, queries that relied upon that index may now fail when attempting to convert non-date data.

  4. A new index may cause the order of execution to change resulting in possible deadlocks occurring where non occurred before.

Microsoft's Index Design Guidance is a good resource regarding the points above.


Updates to non-indexed columns don’t modify the index, even if the data row is moved as a consequence of the update, since a forwarding record is left in place.

This is done to avoid having to update any non-clustered index records that point back directly to the original physical location of the record.

  • For a clustered table, the cluster keys are not changing, so again the n/c index is not updated.

So there is no penalty to the update.


If the update operation targets a non-indexed column of fixed size (like an integer), it shouldn't be slow generally speaking, but compared to a select statement, the update has to eventually be written on the slow disk as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.