sql server - Auto Increment in update statement resulting in duplicate values on large table -
i had need renumber rows in table using following code you've been haacked blog post:
declare @counter int set @counter = 0 update #mytable set @counter = id = @counter + 1
this works fine few records several thousand, when run on sql server 2012 (64-bit) instance on table 250k+ records, end many duplicates (id #1 appears 12 times). in all, there 27k records duplicate values. strange thing 19k have 12 dupes (the same number of processors on machine).
what cause of duplicates?
this not guaranteed work i'm sure phil haack calls out. throw code away.
the reason parallelism. variable might hammered on multiple threads.
the strange thing 19k have 12 dupes (the same number of processors on machine).
that fits explanation.
use row_number
generate guaranteed work ids:
update t set id = r ( select *, row_number() on (order something) r t ) t
just simple sane.
Comments
Post a Comment