Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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