In TSQL, what is the best way to create a bunch of records in sequential order on an identity field?

Question

I want to be able to create a group of records at one time and guarantee that the identity field is continuous for the group (no breaks due to somebody else coming in and creating a record while this is in process). I'm assuming some kind of table lock would work, but I'm no sql guru so any advice would be appreciated (what type of lock? any possible issues? etc).

For a little background, the table structure is pretty simple...

TABLE PropertyCode
(
    Code INT IDENTITY,
    UserID INT
)

The property code is assigned to a property in the property table. Property codes can be re-used (they are printed on signs and the signs can be rotated amongst multiple properties). It is significantly cheaper to print a continuous sequence of numbers than random numbers on signs.

Answer 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT * FROM PropertyCode
INSERT INTO PropertyCode ..... 
COMMIT TRAN

The serializable transaction isolation mode prevents inserts/updates/deletes that will effect any selected data, hence the SELECT * FROM PropertyCode.

The main issue is that no other processes can update/insert/delete until the transaction is committed.

Answer 2

I'm not sure if this is a good answer or not, but it seems to work...

CREATE PROCEDURE ReservePropertyCodes
    @Count INT,
    @UserID INT
AS

DECLARE @i INT

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

-- Locks the table
SELECT TOP 1 * FROM PropertyCode WITH (TABLOCK)

SET @i = 0
WHILE @i < @Count
BEGIN
    INSERT INTO PropertyCode (UserID) VALUES (@UserID)
    SET @i = @i + 1 
END
COMMIT TRANSACTION

I can still do selects against the table, but new inserts are locked until my sproc completes.

Answer 3

I think Chris's answer can be improved upon. Currently it will select and lock the whole table contents.

If you use the following (and assuming you have an index on Code) it should just take a RangeLock preventing other transactions adding new records with codes greater than the highest one at the start of the transaction but not blocking concurrent updates on existing records.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT Code FROM PropertyCode WHERE Code > IDENT_CURRENT('PropertyCode')
INSERT INTO PropertyCode ..... 
COMMIT TRAN