Posted by Erland Sommarskog on 03/13/06 01:19

Doug (drmiller100@hotmail.com) writes:
> Finally, I don't know of any requirement that identity needs to be
> unique. In fact, I think it is pretty much written somewhere in the BOL
> they don't have to be, ESPECIALLY if a transaction is rolled back, or a
> row is deleted.

IDENTITY itself does not guarantee uniqueness. However, the only way
an the same IDENTITY value could be generated twice for the same table,
is if you play with the RESED option of DBCC CHECKIDENT.

In case of a rollback, the identity value that was used is "lost"; the
sequnce is not rolled back.

Usually, you have a PRIMARY KEY or UNIQUE constraint on IDENTITY columns,
so it's not much of an issue anyway.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Naming syntax in 2005
• Prev in forum: Re: UPDATE Statement Involving Tables in Different Databases
• Thread view: Re: Getting list of recently added IDENTITY items

[Reply to this message]