How can I get the last or next Identity of a table?
+2
A:
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Some more information in this question - How Do You Tell What Next Identity Column Will Be?
And this one - SQL Identity (autonumber) is Incremented Even with a Transaction Rollback
Paul Rowland
2010-07-23 04:47:29
but I have no scope. I want to get the Identity from a Table that is not in use.
bochur1
2010-07-23 05:20:58
@bochur1 - sorry dont understand your comment, how can the table not be in use?
Paul Rowland
2010-07-23 05:47:20
ok. from the first link about I got it:>>SELECT IDENT_CURRENT('mytable') + IDENT_INCR('mytable') FROM mytable
bochur1
2010-07-23 06:19:23
FYI: I wouldn't rely on the sequence always being adjacent; gaps can appear if a transaction is rolled back. But for basic snapshot reports it should be OK.
devstuff
2010-07-23 06:25:00
@bochur1 - hope you noticed that the answer using 'ident_current' goes onto say it doesnt work in all situations.
Paul Rowland
2010-07-23 10:35:19
+4
A:
You can also query the table's current IDENTITY in Management Studio by using:
DBCC CHECKIDENT('YourTable')
but that doesn't tell you anything reliably about what the next IDENTITY will be - do not attempt to calculate the next IDENTITY yourself! Let SQL Server handle that and grab it using SCOPE_IDENTITY() once the insert has happened - only that is relevant, really.
marc_s
2010-07-23 04:52:37