AutoNum + Exception

Hi all,
I created a table with an autonum field, with a stored procedure to
insert new record to the table. however I found that the autonum will keep
increase when some unique constraints is voliated.
I tried to use Begin Transaction, and rollback when there's error during the
insert statement, but fail to do it. Any solution to solve it? or I have
missed out something?
Thanks a lot for helping.This behavior is by design.
Even if you rollback a transaction, the generated identity will not reset.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||(Windy) writes:
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
There are two main roads to create an surrogate id: 1) Roll your own. 2) Let
the database do it. This is not merely a question of convienence. The
IDENTITY function is designed to be scalable, so that many processes
can insert at the same time without blocking each other. For this reason,
the counter for the IDENTITY is not reset when a transaction rolled back.
Sometimes you have the business requirement that number must be contiguous,
this is typical for accounting applications. In this case, you must roll
your own. But you must then also be prepare to handled a higher degree
of blocking. To wit, process 1 gets a number, and uses it in a longer
transaction. Process 2 also needs a number, but it cannot get one until
Process 1 has completed, for the simple reason that Process 2 cannot
know which is the next number, as that depends on whether Process 1 will
commit or rollback.
The scheme for rolling your own is:
BEGIN TRANSACTION
SELECT @.nextid = coalesce(MAX(id), 0) + 1
FROM tbl WITH (HOLDLOCK, UPDLOCK)
INSERT tbl (id, ...
SELECT @.nextid...
-- More work
COMMIT TRANSACTION
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This behavior is by design, but isn't documented very well in BOL.
It's not a good practice to rely on the contiguity of IDENTITY values for
permanent or otherwise shared tables. A surrogate key value should not add
any meaning to the row for which it is a surrogate. This means that neither
its magnitude nor its relative position with respect to other rows'
surrogate key values should be relied upon in your data model. All that is
important is that that value be different for each row. Also, since
surrogate key values should not add anything, they also cannot be used to
guarantee entity integrity. A unique constraint must also exist whose
definition doesn't include the surrogate key column.
Many outside influences can affect the values generated by IDENTITY. For
example, the administrator may reset the IDENTITY seed if an overflow is
about to occur. The IDENTITY values on a table may need to be changed in
order to facilitate replication or consolidation with other databases.
Also, gaps can occur due to rollbacks or deletes.
I'm not saying that IDENTITY is a bad thing; on the contrary: it's a
valuable tool, but it's important that it be used correctly.
I have used the IDENTITY property on table variables and local temporary
tables to facilitate sequencing and ordering, but that occurs entirely
within the body of a procedure or trigger, and because the objects are local
to the connection, there cannot be any any interaction with other
transactions.
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||The December 2005 issue of SQL Server Magazine has an article by Itzik
Ben-Gan on creating a custom identity generating stored procedure.
http://www.windowsitpro.com/Article...8165/48165.html
You'll need a subscription to access the full article.
"Windy" wrote:

> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during t
he
> insert statement, but fail to do it. Any solution to solve it? or I have
> missed out something?
> Thanks a lot for helping.
>
>|||Many thanks to all of you guys.
<Windy> glsD:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>