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.

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;

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 INSERT ed into the database, it is potentially leaking a persistence concern.

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 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.

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

Rather than asking "why the heck would you want to do this???", I'm going to assume you have a good reason.

Here's what I did for some quick-and-dirty test SQL, which worked fine in all but one case, even after emptying a previously-populated table:

DECLARE @nextId INTEGER = IDENT_CURRENT('myTable')
    IF (SELECT COUNT(*) FROM myTable) > 0
    OR @nextId > 1
        SET @nextId += 1

I then used this to build a status string that I put into myTable (I wanted it to have the NEW identity value that the row would be given) to verify the SQL was working as expected.

The only case this does not handle properly is:

1. Create a new (empty) table and run the above code. It will correctly tell you that it will have an ID of 1.
2. Add a single element to the table. This will have an ID of 1, as predicted.
3. Empty the table.
4. Run the above SQL again. It will incorrectly tell you that the next entry will also have an ID of 1.

This is the one case for which I couldn't find a workaround. I was OK with it, since step 1 of my test involved adding more than one row to the table, but be aware of this one unlikely-but-possible sequence of events that could lead to an error.

There may be a "better" way, but it wasn't for production code, so quick-and-dirty was fine.

I can contribute another situation where knowing the next value would be useful. I have data in a line in Excel, such as: A B C X1 X2 Y1 Y2 Z1 Z2 I want to put A B C in one database table, and use the new primary key for that record in another table where I'm putting X1 and X2, then Y1 and Y2, then Z1 and Z2. So this one line would generate one record in the first table: xxx A B C

And three records in the second table

yyy xxx X1 X2

yyy+1 xxx Y1 Y2

yyy+2 xxx Z1 Z3

If I could determine xxx before starting the process, I could just increment it for each record being processed. I too made the mistake of just finding the highest value M and assuming M+1 would be the next one. That wasn't working because some uploads didn't complete due to errors, the transaction was rolled back, but the indexed PK's had been assigned and weren't going to be reused. Manual deletion of the highest valued PK records would cause a similar issue.

My simplistic solution was just to write an SP that gets the max value of the PK after uploading the first case.

CREATE     PROCEDURE [MIRR].[GetLast_CasesIndexID]
@GroupNumber as int Output
AS
BEGIN
    SELECT @GroupNumber = max(CasesIndexID)  
    FROM MIRR.Cases
END
GO

I did use it this way to calculate next id also it works good when table in empty

select isnull(max(ID),0)+1 from yourTable

In truth, the ability to rollback an insert, which doesn't rollback identity() values assigned to the values, causes the next identity value to "jump" ... hence thinking of the primary key as a sequential value is incorrect - your assumption is that there wasn't a rollback just prior to your insert execution! Its an incremented value to ensure unique values. No guarantees there are no gaps... even when you start the insert.

I suggest when you have a scenario where you think you need the next IDENTITY value, you really need a foreign-key to a different table on the row - for your set to have a way of being identified.

Also, you might want to research OUTPUT INTO to capture which records were inserted to link to what information you inserted...

INSERT INTO #A(Col1)
OUTPUT inserted.IdentityColumn, inserted.Col1 
INTO #B (DestinationPrimaryKey, SourceRowId)
SELECT SourceRowID FROM #ThingToRecord

SELECT DestinationPrimaryKey, SourceRowId
FROM #B