6

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?

2
  • 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? Nov 17, 2011 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, 2011 at 17:23

1 Answer 1

5

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

2
  • do you mean add a index to temp table?
    – VDMT
    Nov 18, 2011 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 Nov 18, 2011 at 11:51

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.