Getting the next identifier without inserting a row

Question

Is it possible in SQL (SQL Server) to get the following identifier (integer) from the identity column in the table before and without actually inserting a row? This is not necessarily the highest ID plus 1 if the last row has been deleted.

I ask about this because we sometimes have to update Live DB with new lines. The row identifier is used in our code (for example, Switch (ID) {Case ID:} and should be the same. If our development database and live DB are not synchronized, it would be nice to predict the row identifier before deployment.

I could, of course, SET IDENTITY OFF set INSERT_IDENTITY ON or execute a transaction (is it an ID rollback, etc.), but I wondered if there was a function returning the next identifier (without increasing it).

Answer 1

Edit:

After spending several hours comparing entire page dumps, I realized that there is an easier way and I have to stay on DMV.

The value saves backup / restore, which is a clear indication that it is stored - I dumped all the pages in the database and could not find the location / change when the record was added. Comparing 200k lines of page dumps is not fun.

I used a special admin console. I took a dump of each open internal table inserted in a row, and then made another dump of the system tables. Both of these dumps were identical, which indicates that while he survived and, therefore, should be stored, he is not exposed even at this level.

So, having walked around in a circle, I realized that the DMV has an 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 were deleted, the last value was not reset, so the last value + increment should be the correct answer.

And I'm going to write an episode on my blog.

Oh, and a short cut on all of this:

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

Thus, it actually becomes easy - but it all assumes that no one else used your identifier until you returned it. Good for research, but I would not want to use it in code.

Answer 2

try IDENT_CURRENT :

 Select IDENT_CURRENT('yourtablename') 

This works even if you did not insert any rows in the current session:

Returns the last identifier value generated for the specified table or view. The last generated identity value can be for any session and any area.

Answer 3

Instead of using the IDENTITY column, you can use the UNIQUEIDENTIFIER (Guid) column as a unique row identifier and insert known values.

Another option (which I use) is SET IDENTITY_INSERT ON, where line identifiers are managed by one managed source by one document.

Answer 4

You can easily determine that the last value used is :

 SELECT last_value FROM sys.identity_columns WHERE object_id = OBJECT_ID('yourtablename') 

Usually the next identifier will be last_value + 1 - but there is no guarantee for this.

Mark

Answer 5

This is a bit strange, but it will work:

If you want to know the following value, start by getting the highest value plus one:

 SELECT max(id) FROM yourtable 

To do this, you need to reset the insert id:

 DECLARE @value INTEGER SELECT @value = max(id) + 1 FROM yourtable DBCC CHECKIDENT (yourtable, reseed, @value) INSERT INTO yourtable ... 

Not quite an elegant solution, but I haven't had my coffee yet; -)

(This also assumes that nothing is being done with the table by your process or any other process between the first and second blocks of code).