Best Way to Get the Next Id Number Without "Identity"

Best way to get the next id number without identity

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.

How to get the next ID from DB Table without an IDENTITY or GUID Column

For SQL Server 2000 - 2008 R2, using INT IDENTITY is by far the easiest and most reliable way to do it.

Trying to do this yourself is like reinventing the wheel and carries a lot of ways in which it can go wrong - so why bother?? You need to (a) make sure this mechanism is concurrency safe (and just doing SELECT MAX(ID) + 1 is NOT safe!), and you (b) need to make sure your mechanism doesn't become a major bottleneck in system performance, either...

What's your problem with using INT IDENTITY as your primary key?? Doesn't seem very rational...

For SQL Server 2012 and newer, you could also consider using a SEQUENCE (which is basically an IDENTITY that's not specifically coupled to a single table)

Update: that other SO question that you mention in your post is doing basically a "do-it-yourself" simulation of a SEQUENCE:

• a table holds pairs of (sequencename, currentvalue)
• when you ask for a new value for a given sequence, the new value is determined by using just an UPDATE statement coupled with the OUTPUT clause

Why so complicated? This is the easiest and most efficient way (other than using an IDENTITY) to safely handle concurrent requests. The UPDATE statement will always exclusively lock that one row in the "sequence table" for the sequence you want to get a new ID from - thus preventing any other transactions to grab the same ID and get duplicated values - and it returns the new ID using the OUTPUT clause back to the caller.

Getting the next ID without inserting a row

Edit:

After spending a number of hours comparing entire page dumps, I realised there is an easier way and I should of stayed on the DMVs.

The value survives a backup / restore, which is a clear indication that it is stored - I dumped all the pages in the DB and couldn't find the location / alteration for when
a record was added. Comparing 200k line dumps of pages isn't fun.

I had used the dedicated admin console I took a dump of every single internal table exposed inserted a row and then took a further dump of the system tables. Both of the dumps were identical, which indicates that whilst it survived, and therefore must be stored, it is not exposed even at that level.

So after going around in a circle I realised the DMV did have the answer.

create table foo (MyID int identity not null, MyField char(10))
insert into foo values ('test')
go 10

-- Inserted 10 rows
select Convert(varchar(8),increment_value) as IncrementValue,
   Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'

-- insert another row
insert into foo values ('test')

-- check the values again
select Convert(varchar(8),increment_value) as IncrementValue,
   Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'

-- delete the rows
delete from foo

-- check the DMV again
select Convert(varchar(8),increment_value) as IncrementValue,
   Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'

-- value is currently 11 and increment is 1, so the next insert gets 12
insert into foo values ('test')
select * from foo

Result:
MyID        MyField
----------- ----------
12          test      

(1 row(s) affected)

Just because the rows got removed, the last value was not reset, so the last value + increment should be the right answer.

Also going to write up the episode on my blog.

Oh, and the short cut to it all:

select ident_current('foo') + ident_incr('foo')

So it actually turns out to be easy - but this all assumes no one else has used your ID whilst you got it back. Fine for investigation, but I wouldn't want to use it in code.

Generating the Next Id when Id is non-AutoNumber

Using MAX(id) + 1 is a bad idea both performance and concurrency wise.

Instead you should resort to sequences which were design specifically for this kind of problem.

CREATE SEQUENCE EmpIdSeq AS bigint
  START WITH 1
  INCREMENT BY 1;

And to generate the next id use:

SELECT NEXT VALUE FOR EmpIdSeq;

You can use the generated value in a insert statement:

INSERT Emp (EmpId, X, Y)
VALUES (NEXT VALUE FOR EmpIdSeq, 'x', 'y');

And even use it as default for your column:

CREATE TABLE Emp 
(
 EmpId bigint PRIMARY KEY CLUSTERED 
    DEFAULT (NEXT VALUE FOR EmpIdSeq),
 X nvarchar(255) NULL,
 Y nvarchar(255) NULL
);

---

Update: The above solution is only applicable to SQL Server 2012+. For older versions you can simulate the sequence behavior using dummy tables with identity fields:

CREATE TABLE EmpIdSeq (
 SeqID bigint IDENTITY PRIMARY KEY CLUSTERED
);

And procedures that emulates NEXT VALUE:

CREATE PROCEDURE GetNewSeqVal_Emp
  @NewSeqVal bigint OUTPUT
AS
BEGIN
  SET NOCOUNT ON
  INSERT EmpIdSeq DEFAULT VALUES

  SET @NewSeqVal = scope_identity()

  DELETE FROM EmpIdSeq WITH (READPAST)
END;

Usage exemple:

DECLARE @NewSeqVal bigint
EXEC GetNewSeqVal_Emp @NewSeqVal OUTPUT

The performance overhead of deleting the last inserted element will be minimal; still, as pointed out by the original author, you can optionally remove the delete statement and schedule a maintenance job to delete the table contents off-hour (trading space for performance).

Adapted from SQL Server Customer Advisory Team Blog.

---

Working SQL Fiddle

How do I get the Next available number from an SQL Server? (Not an Identity column)

I think from the question you're after the next available, although that may not be the same as max+1 right? - In that case:

Start with a list of integers, and look for those that aren't there in the groupid column, for example:

;WITH CTE_Numbers AS (
    SELECT n = 2001
    UNION ALL
    SELECT n + 1 FROM CTE_Numbers WHERE n < 4000 
)
SELECT top 1 n 
FROM CTE_Numbers num
WHERE NOT EXISTS (SELECT 1 FROM MyTable tab WHERE num.n = tab.groupid)
ORDER BY n

Note: you need to tweak the 2001/4000 values int the CTE to allow for the range you want. I assumed the name of your table to by MyTable

How to get the next identity value from SQL Server

I think you'll want to look for an alternative way to calculate the next available value (such as setting the column to auto-increment).

From the IDENT_CURRENT documentation, regarding empty tables:

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

It doesn't even seem all that reliable, especially if you end up designing an app that has more than one person writing to the table at the same time.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

---

Related Topics