Unexpected update results on heap using SET @Variable = Field= @Variable + 1, fixed with clustered index

I'm just looking to understand why this is happening, and my Google searches were failing me. We are on SQL Server 2016 SP1.

This is the situation: Vendor table that manages IDs by keeping track of current values for each table. A function can be called to return a block of IDs if you are doing an insert.

So we set up a temp table by selecting from the real table using select into (we are cloning a set of data to be reinserted with a different property set).

Then we call the function and get new ids for the number of records (it just returns the max ID, so we do some math to get the next id).

Then we update the table as such:

update #temp set @nextId = Id = @nextId + 1

with the expectation that it will increment by one for each record and set the ids.

Instead, the same ID was set for every 4 records, then it would increment and the next 4 get the next id, etc. Why every 4 records? What went wrong?

Even more fun, if we put a clustered index on the table, everything works correctly.

I'm sure it has to do with the table being a heap...but not sure why.

Link to plan.

The documentation for the UPDATE statement says (emphasis added):

Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record.

Some people have tried to make this "quirky update" technique work reliably for multiple rows by applying ever-greater constraints on its use. The fact remains that this relies on observed effects and undocumented behaviour, so you should not expect it to work in general, or to keep 'working' in future.

I can't say exactly what went 'wrong' in your case, without a repro script or an execution plan, but ultimately it doesn't really matter. If forced to guess, I would say your update ran at DOP 4 and four threads read the same variable value concurrently.

You would be better advised to use a reliable solution instead, like ROW_NUMBER (docs), the IDENTITY function, or a sequence.

StackExchange Database Administrators Q#295462, answer score: 14