How to get the next identity value from SQL Server

问题:

I need to get the next identity value from SQL Server.

I use this code :

SELECT IDENT_CURRENT('table_name') + 1

This is correct, but when the table_name is empty (and next identity value is "1") returned "2" but result is "1"

回答1:

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.

回答2:

In case when your table will be empty then this query will work perfectly.

SELECT
  CASE
    WHEN (SELECT
        COUNT(1)
      FROM tablename) = 0 THEN 1
    ELSE IDENT_CURRENT('tablename') + 1
  END AS Current_Identity;

回答3:

I tend to agree with other posters that this is not the right way to do this, however it can be convenient for certain cases. Several posts asks why at all do this, and let me give you an example where it was convenient for me, and how and why.

I'm implementing a Bitcoin node. I want the blockchain stored in an SQL database. Every block is received from the network from other nodes and miners. The details you can find elsewhere.

When receiving a block it contains one header, any number of transactions and each transaction any number of inputs and outputs. I have 4 tables in my database - you guessed it - a header table, transaction table, inputs table and outputs table. Each row in the transaction, inputs and outputs table are linked with IDs to eachother up into the header row.

Some blocks contain several thousand transactions. Some transactions hundres of inputs and/or outputs. I need them stored in the DB from a convenient call in C# without compromising integrity (the IDs all link up) and with decent performance - which I cannot get by committing row by row when there are close to 10000 commits.

Instead, I make absolutely sure to sync-lock my database object in C# during the operation (and I don't have to worry about other processes accessing the database also), so I can conveniently do a IDENT_CURRENT on all 4 tables, return the values from a stored proc, fill up the nearly 10000 rows in 4 List<DBTableRow> while incrementing the IDs and call the SqlBulkCopy.WriteToServer method with option SqlBulkCopyOptions.KeepIdentity set, and then send it all in 4 simple calls, one for each tableset.

The performance gain (on a 4-5 years old mid-range laptop) was going from about 60-90 seconds down to 2-3 seconds for the really large blocks, so I was happy to learn about IDENT_CURRENT().

The solution might not be elegant, might not be by the book so to speak, but it is convenient and simple. There are also other ways to accomplish this, I know, but this was just straight forward and took a few hours to implement. Just make sure you don't have concurrency issues.

回答4:

I know there is already an answer but it really irks me that all of my searches along the lines of "get next identity sql server" came up with flaky solutions (like merely selecting the current identity value and adding 1) or "it can't be reliably done".

There are a couple of ways to actually do this.

SQL Server >= 2012

CREATE SEQUENCE dbo.seq_FooId START WITH 1 INCREMENT BY 1
GO

CREATE TABLE dbo.Foos (
    FooId int NOT NULL 
        DEFAULT (NEXT VALUE FOR dbo.seq_FooId)
        PRIMARY KEY CLUSTERED 
)
GO

// Get the next identity before an insert
DECLARE @next_id = NEXT VALUE FOR dbo.seq_FooId

SQL Server 2012 introduced the SEQUENCE object. In this case, the sequence will be incremented each time NEXT VALUE FOR is called, so you don't need to worry about concurrency.

SQL Server <= 2008

CREATE TABLE dbo.Foos (
    FooId int NOT NULL 
        IDENTITY (1, 1)
        PRIMARY KEY CLUSTERED 
)
GO

// Get the next identity before an insert
BEGIN TRANSACTION
SELECT TOP 1 1 FROM dbo.Foos WITH (TABLOCKX, HOLDLOCK)
DECLARE @next_id int = IDENT_CURRENT('dbo.Foos') + IDENT_INCR('dbo.Foos');
DBCC CHECKIDENT('dbo.Foos', RESEED, @next_id)
COMMIT TRANSACTION

You will probably want to encapsulate all of that in a stored procedure, especially because the DBCC statement requires elevated access and you probably won't want everyone having that kind of access.

Not anywhere near as elegant as NEXT VALUE FOR, but it should be reliable. Note that you will get 2 for your first value if there are no rows in the table, but if you intend to always use this method to get the next identity, you could seed the identity at 0 instead of 1 (with IDENTITY (0, 1)) if you are dead set on starting off with 1.

Why would anyone want to do this?

I cannot speak for the question poster, but the book 'Domain Driven Design' and the 'official' DDD sample uses this technique (or at least hints at it) as a way of enforcing that entities always have a valid identifier. If your entity has a bogus identifier (like -1 or default(int) or null) until it is INSERTed into the database, it is potentially leaking a persistence concern.

回答5:

SELECT isnull(IDENT_CURRENT('emp') + IDENT_INCR('emp'),1)