In my search for a method to create an unique increment series of numbers. I considered the identity column. I couldn't use it for my purpose, but it lead to my current question;
How does the identity technically work in Sql server?
I am not looking for an answer on how to use it, I know it increments the number on each insert, leaving gaps on a delete. But I couldn't find any documentation how it generates it's number. Is this a table in sql server? Does it use a row lock? Or some other kind of lock? Is it locking? How does it prevent duplicate numbers?
There's some useful information on the internals in these questions/articles:
https://dba.stackexchange.com/questions/1635/why-are-denali-sequences-supposed-to-perform-better-than-identity-columns
http://www.sqlmag.com/article/sql-server/Sequences-Part-2-129205
They are talking about SEQUENCEs which were introduced in SQL 2012, but includes some info on IDENTITY, the differences, and the implementations. Not sure if that answers fully, but IMHO are worth a read.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With