what is the difference between auto increment and identity

_{Thursday, February 16, 2012 9:32 AM}

what is the difference between auto increment and identity?

_{Thursday, February 16, 2012 9:42 AM ✅Answered}

what is the difference between auto increment and identity?

You could say that "Autoincrement" is the general term and "IDETINTY" is an implementation. In SQL 2012, there is a second implementation, sequences. (Which is in many regards is a better implementation.)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Thursday, February 16, 2012 9:49 AM ✅Answered}

In SQL Server the IDENTITY propoerty in a table is used for auto increment a column value and only one IDENTITY column is allowed in a table. Please have a look at http://msdn.microsoft.com/en-us/library/ms186775.aspx

IDENTITY property is synonymous to auto increment in SQL Server.

- Krishnakumar S

_{Thursday, February 16, 2012 9:59 AM}

does it mean in 2012 version replace identity with sequences ?

_{Thursday, February 16, 2012 10:49 AM}

No.. Identity is not replaced by sequences. Identity and sequences has a difference. Like identity will auto generate identity number after DML execution whereas sequences create unique number for each transaction before DML execution taking place.

_{Thursday, February 16, 2012 11:41 AM}

does it mean in 2012 version replace identity with sequences ?

No. You can use IDENTITY in SQL 2012 as well. But sequences is an alternative way to implment autoincrement columns. As a sequence is an independent object, there is one more thing to set up. But on the flip side, you are not faced with all restrictions there are with IDENTITY column. When you use sequence you say:

CREATE TABLE (myautoid int NOT NULL DEFAULT NEXT VALUE FOR my_sequence)

and then you can work with that column as with any other column. That is, you can still updated, insert explicit values etc.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se