Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does the identity column in sql server technically work?

Tags:

sql

sql-server

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?

like image 328
Jaco Avatar asked Oct 29 '25 11:10

Jaco


1 Answers

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.

like image 134
AdaTheDev Avatar answered Oct 31 '25 01:10

AdaTheDev