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.