Posted by Erland Sommarskog on 07/15/07 21:51

raylopez99 (raylopez99@yahoo.com) writes:
> How can you avoid such gaps by not using the Identity property? How
> will your rows be autonumbered? Right now my AuthorID is simply a int
> value that is autonumbered using the IDENTITY property.

Without IDENTITY, no auto-number. You will have to roll your own. Which
is not very difficult at all:

BEGIN TRANSACTION

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

INSERT tbl (id, col1, ....)
VALUES (@id, @val1, ....)

COMMIT TRANSACTION

Typically you do this in a stored procedure.

The transaction and UPDLOCK is needed to avoid that two processes gets the
same number, and one of them dies with a PK violation. This solution works
fine, as long you don't have a high degree on concurrency.

I also like to repeat that while system-generated ids are sometimes
necessary, there are many tables for which there is a usable natural
key, in which case you should use that. And, in the cases, you use a
system-generated id, you should still strive to identify a set of columns
in the table that uniquely identify a row.
--
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

[Back to original message]