Best way to get the next id number without "identity"

Add another table with an identity column and use this new table and column to select/generate your identity values for the old table.

Update: Depending on the frequency of INSERTS (and the number of existing rows e) you could seed your new IDENTITY values at e+x where x is sufficiently large. Thhis would avoid conflict with the legacy inserts. A sad solution, an imperfect one for sure, but something to think about?

Create another table:

t_identity (id INT NOT NULL PRIMARY KEY CHECK (id = 1), value INT NOT NULL)

with a single row, lock this row, and increment value by one each time you need an IDENTITY.

To lock, increment, and return the new value in a single statement, use:

UPDATE  t_identity
SET     value = value + 1
OUTPUT  INSERTED.value

If you don't want to update, just lock, then issue:

SELECT  value
FROM    t_identity WITH (UPDLOCK, ROWLOCK)

This will lock the table until the end of the transaction.

If you always first lock t_identity before messing with ancient_table, you will never get a deadlock.

EDIT this is basically the method purposed by @Quassnoi, I just implement it in a loop so you can run it against multiple windows at the same time to see it works great.

set up:

create user's existing table:

create table Subscriber
(
recid  int not null primary key
)

create new table to keep track of the missing identity, you could add an extra column to keep track of a table if this is needed for multiple tables, but I hve not done that in this example:

CREATE TABLE SubscriberIDs
(
SubscriberID int
)
insert into SubscriberIDs values (0) --row must exist first

create test script, put this into multiple windows and run them at the same time:

declare @idtable table --will hold next ID to use
(
id int
)
declare @x  int
declare @y  int
set @x=0
while @x<5000 --set up loop
begin
    set @x=@x+1
    begin transaction
    --get the next ID to use, lock out other users
    UPDATE SubscriberIDs
        SET SubscriberID= SubscriberID+ 1
        OUTPUT  INSERTED.SubscriberID
        INTO @idtable
    --capture the next id from temp table variable
    select @y=id from @idtable
    --print @y
    --use the next id in the actual table
    insert into subscriber values (@y)

    commit
    --print @x
    waitfor delay '00:00:00.005'
end --while

---------------------------------------------------------------
EDIT here is my original attempt, which will eventually get some deadlocks when run in a loop and in multiple windows at the same time. The above method always works. I tried all combinations of transactions, with(holdlock), and set transaction isolation level serializable, etc. but could not get it to run as well as the above method.

set up:

create table subscriber
(
recid  int not null primary key
)

used to capture the id:

declare @idtable table
(
id int
)

the insert:

insert into subscriber
    OUTPUT INSERTED.recid
        recid
    INTO @idtable
    SELECT ISNULL(MAX(recid),0)+1 FROM subscriber

list the new id:

select * from @idtable

list all the ids:

select * from subscriber