Hi

I have a table with an identity where I need to insert a set amount of
rows. However I need to inser a full block with no gaps in the id's.
If this sql was run by 2 people simulataneously then I could not get
back a full block. I was thinking that a table lock would help here but
do not know how to write it correctly

Can any one help?

Thanks
Rippo

set nocount on

declare @noRecs  int
set @noRecs = 5

while @noRecs > 0
begin
    insert into Incidents (IncidentRef) values ('New blank incident')
    set @noRecs = @noRecs - 1
end

    select @@identity - @noRecs As StartID

set nocount off


--Code to create table
CREATE TABLE dbo.Incidents
    (
    IncidentID int NOT NULL IDENTITY (1, 1),
    IncidentRef varchar(50) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Incidents ADD CONSTRAINT
    PK_Table1 PRIMARY KEY CLUSTERED
    (
    IncidentID
    ) ON [PRIMARY]

GO

IDENTITY doesn't guarantee that there won't be any gaps.  Using IDENTITY in
this way is a common newbie mistake.  If you really cannot have gaps, then
don't use IDENTITY.  You could use something like:

DECLARE @nextIncidentID INT
BEGIN TRANSACTION
SELECT @nextIncidentID = ISNULL(MAX(IncidentID), 0) + 1 FROM Incidents
WITH(UPDLOCK, HOLDLOCK)
WHILE @noRecs > 0
BEGIN
    INSERT Incidents (IncidentID,  IncidentRef) VALUES (@nextIncidentID,
'New blank incident')
    SELECT @noRecs = @noRecs - 1, @nextIncidentID = @nextIncidentID + 1
END
COMMIT TRANSACTION

WARNING!!!!  The transaction in the above is necessary, as is WITH(UPDLOCK,
HOLDLOCK).  WITH(UPDLOCK, HOLDLOCK) applies an update range-lock.  UPDLOCK
is necessary because only one transaction can obtain an update lock on a
resource at the same time, and HOLDLOCK is necessary because it prevents
another transaction from inserting rows at the end of the table.  Both are
needed to apply an update range-lock.  Failing to use WITH(UPDLOCK,
HOLDLOCK) or failing to enclose everything between the SELECT MAX and the
final INSERT within a transaction will lead to unique constraint violations
or deadlocks or both.

Incorrect use of SELECT MAX is also a common newbie mistake, so you should
probably add a comment to the procedure so that the transaction and locking
hints aren't removed by another developer.

Another option would be to use a separate table to store the next key value,
but you will run into similar problems avoiding gaps.

Show quoteHide quote

Thanks for this.

How will gaps appear between my block of new ids if the table is locked
and I perform say 5 inserts? I am not bothered if there is gaps between
multiple blocks just bothered if there ar any gaps within the blocks
themselves. Can this happen in this scenario.

Please can you advise me
Thanks
Rippo

If you just begin a transaction, insert your 5 rows and then commit the
transaction, then it is extremely likely, but not guaranteed, that the block
of rows will have sequential IDs. There is no mechanism to guarantee this
without acquiring an exclusive lock on the table, but this could lead to
concurrency problem for other users. The concept behind an identity value is
that you really should not care exactly what number is assigned by the
system. If you need to somehow relate a group of rows, then perhaps some
combination of columns?

Show quoteHide quote

I'm tempted to say no, but I've never tried it because it's really bad form
to use IDENTITY in this fashion, except with a local temporary table or a
table variable.  In any case, I would still apply an update range-lock
instead of a table lock, because a table lock is, in my opinion, overkill.
Table locks should be avoided like the plague.  A table lock would prevent
any updates to existing rows as well as inserts.  Since you have a clustered
index on IncidentID, SELECT @dummy = MAX(IncidentID) FROM Incidents
WITH(UPDLOCK, HOLDLOCK) will perform adequately, and will only prevent other
transactions from inserting new rows and from updating the last row.

Show quoteHide quote

Thanks for your effort.

Are you saying that the following code will be adequate?

DECLARE @Dummy INT
BEGIN TRANSACTION
SELECT @Dummy = MAX(IncidentID) FROM Incidents
WITH(UPDLOCK, HOLDLOCK)
WHILE @noRecs > 0
BEGIN
    INSERT Incidents (IncidentRef) VALUES ('New blank incident')
    SELECT @noRecs = @noRecs - 1
END
COMMIT TRANSACTION

Yes, notwithstanding my previous admonition.  One other thing you should
consider, however: SQL Server likes set-based inserts better than row-based
inserts, at least from a performance standpoint.  Triggers are fired only
once, writes to the log are minimized, and index maintenance is optimized.
You may want to cache the updates in a table variable and commit them to the
normal table with a single set-based insert.  It doesn't eliminate the need
for the transaction or WITH(UPDLOCK, HOLDLOCK), but it may minimize the
amount of time that the locks are held, which will improve concurrency.

Show quoteHide quote

Trouble with SP
SQLDataAdapter.FillSchema() hangs with select on view
how to rename a temp table
How can I do this without a cursor??
Somewhat complicated GROUP behavior that has me stumped