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?

  • Is the choice just between no indexes and one clustered index on id? Do you have any other indexes on that table? Is id an IDENTITY column? – Nick Chammas Nov 17 '11 at 17:10
  • @NickChammas No indexes yet and I don't have any known/suggested indexes to make. I can make any indexes I want on the table, and id is an IDENTITY. – Ben Brocka Nov 17 '11 at 17:23

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

  • do you mean add a index to temp table? – VDMT Nov 18 '11 at 1:57
  • 1
    For an IN list on an indexed column you will typically get multiple seeks until the number of elements is around 60 then it will move to a scan with a join against a table of constants. This is discussed in the comments here – Martin Smith Nov 18 '11 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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