|
|
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
Navigation:
[Reply to this message]
|