1

Update operations in SQL Server occur in clustered index order?

I want to set the sequential position in a table, but SQL Server don't let me use ORDER BY in an update operation.

I tested and the updates occur in the clustered index which is the position column, so everything is fine, but can I trust it will always work like that?

DECLARE @Position BIGINT  = 0

UPDATE
  Paginations
SET
  @Position = Position = @Position + 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fraga
  • 1,361
  • 2
  • 15
  • 47
  • See: http://stackoverflow.com/questions/3439110/sql-server-update-a-table-by-using-order-by – Blorgbeard Jun 06 '13 at 20:59
  • 3
    Oh no! Quirky update! Undocumented, unsupported, and not guaranteed to work in any order no matter what you do. Why don't you calculate ROW_NUMBER() in a CTE and then use that to update? Or better yet, not bother storing this redundant information that you can get from a query at runtime anyway? – Aaron Bertrand Jun 06 '13 at 21:03
  • can you create an example using row_number in a cte please? cause I did one and is really slow! i only have 50k rows – Fraga Jun 08 '13 at 02:59

0 Answers0