Sql-server – Will a clustered index improve performance of updates performed sequentially on that clustered index

indexsql server

I know indexes reduce data modification performance, but I have a task where (almost) all updates are done on items in a sequential order. Would a clustered index improve or reduce performance in updating these rows?

The clustered index would be on column id where id is an IDENTITY column. id is thus never changed and will be sequential (in addition rows should never be deleted).
This is the format of my update statements:

UPDATE [table] SET value = 1 
WHERE  id IN (1,2,3,4...)

The values are not necessarily continous however. A sequence of IN (1,2,4,5) is also possible if id=3 is not set to be updated, but they will always be in order.

When all updates are in sequential order, will a clustered index improve or reduce performance?

Best Answer

The IN like this is a set of discrete values. SQL Server won't bother working out it is a range or checking ascending values.

That is, x IN (1,2,4,5) is parsed out to x=1 OR x=2 OR x=4 OR x=5. OR is non-SARGable in most cases and result in scans rather than seeks.

If you put the data into a temp table with an index and did x IN (SELECT foo FROM #bar) then it becomes a semi-join and will more likely use an index