Id (indentity) is increments on faults.

[Flare]

Hi,

When i eg. manually ad entries to a table and, cancels the insert Ms SQL
increment the counter on the ID anyway. Is there a way to avoid this
behavior?

Regards
Anders

[Erland Sommarskog]

Re: Id (indentity) is increments on faults.

Flare (dct_flare@hotm ail.com) writes:[color=blue]
> When i eg. manually ad entries to a table and, cancels the insert Ms SQL
> increment the counter on the ID anyway. Is there a way to avoid this
> behavior?[/color]

Yes, don't use the IDENTITY property, but roll your own. IDENTITY works
that way by design. By grabbing one number which never has to be
rolled back, insertions into tables with IDENTITY columns can scale
better.

One way to get a key on your on is:

BEGIN TRANSACTION

SELECT @id = coalesce(MAX(id ), 0) + 1 FROM tbl (UPDLOCK)

INSERT tbl (id, col1, col2, ....)
VALUES (@id, @par1, @par2, ...)

COMMIT TRANSACTION

The UPDLOCK is required to avoid that two processes grab the
same id.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp