0

i'm using this query on SQLServer 2017:

DECLARE @id int
SET @id = 0
UPDATE aicidich00fuff
SET @id = trasco_id = @id + 1

but for some reason it doesn't create an unique value. It is easily notable if I run the following:

select  COUNT(*) cont, trasco_id from aicidich00fuff group by trasco_id order by trasco_id

  cont trasco_id
    4   1
    4   2
    4   3
    4   4
    4   5
    4   6
    4   7
    4   8
    4   9

and so on. Fun fact is that most values are repeated four times, then three times, then twice. This query for autoincrementing a column has worked since a few weeks ago, but now every time i got this behaviour. Any tips? Thanks

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
Serphentelm
  • 97
  • 9
  • 1
    I think you are trying to get a new value for each row and not sure why sometimes you aren't? This is obviously because you have some duplicates in the data. If you want incrementing values you should use ROW_NUMBER. – Sean Lange Mar 09 '20 at 14:46
  • This approach to setting values is inherently unsafe because it's non-deterministic. The optimizer will not guarantee you get sequential values this way; the easiest way to see it fail is if a parallel plan is generated. Insidiously, this can "go well" for a long time until it doesn't. – Jeroen Mostert Mar 09 '20 at 14:49
  • 2
    I notice you are using the quirky update methodology here. Do you follow **ALL** of the caveats for this methodology to work? I can tell you from the snippet you posted that you don't mean **ALL** the critieria for this to work. You should either rewrite this or read this article [https://www.sqlservercentral.com/articles/solving-the-running-total-and-ordinal-rank-problems-rewritten](https://www.sqlservercentral.com/articles/solving-the-running-total-and-ordinal-rank-problems-rewritten) and follow all the criteria. MAXDOP and TABLOCKX are both missing in your query...maybe others. – Sean Lange Mar 09 '20 at 14:52
  • @SeanLange wuold you kindly explain me how to do an autoincrementing value with the row_number? – Serphentelm Mar 09 '20 at 15:04
  • 1
    https://stackoverflow.com/questions/19124662/how-to-use-row-number-in-update-clause/19124799 – Sean Lange Mar 09 '20 at 15:09

0 Answers0