CODEWITHSUNDEEP
sqlsql-server
Dean Kuga
Dean Kuga

Reputation: 12119

Does it make any difference if columns used only in UPDATE queries are indexed

Let's say there are two columns of type int in a SQL Server. Values in these two columns are frequently updated for a single row using the ROWLOCK hint and the unique row Id which is a PK.

For example 

UPDATE SomeTable WITH(ROWLOCK) 
SET SuccessCount = {0}, ErrorCount = {1} 
WHERE RecordId = {2}

If SuccessCount and ErrorCount are not used in any SELECT queries, performance-wise does it make any difference if these two columns are added to a non-clustered index of SomeTable?

Upvotes: 0

Views: 48

Answers (1)

xQbert
xQbert

Reputation: 35333

An index is meta data about a table.

Including an index on data that changes which is never directly searched by (read where index is never used) DOES add overhead as the engine has to maintain the index. If these two fields are NEVER searched by directly, then the index with these columns in it is pointless overhead.

Indexes should be added only if the benefits from reduced search time (frequency and time savings) outweigh the cost of maintaining the index (overhead any insert,update or delete)

In this case there will never be a benefit as they are never directly searched upon. Thus, pure cost; no savings... don't do it.

Upvotes: 2

Related Questions