1

How can I get the last or next Identity of a table?

shahkalpesh
  • 33,172
  • 3
  • 63
  • 88
Alex
  • 2,081
  • 14
  • 49
  • 76

2 Answers2

4

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
  • 732,580
  • 175
  • 1,330
  • 1,459
2

SCOPE_IDENTITY (Transact-SQL)

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

Community
  • 1
  • 1
Paul Rowland
  • 8,244
  • 12
  • 55
  • 76
  • but I have no scope. I want to get the Identity from a Table that is not in use. – Alex Jul 23 '10 at 05:20
  • 1
    @bochur1 - sorry dont understand your comment, how can the table not be in use? – Paul Rowland Jul 23 '10 at 05:47
  • ok. from the first link about I got it: >>SELECT IDENT_CURRENT('mytable') + IDENT_INCR('mytable') FROM mytable – Alex Jul 23 '10 at 06:19
  • 1
    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 Jul 23 '10 at 06:25
  • 1
    @bochur1 - hope you noticed that the answer using 'ident_current' goes onto say it doesnt work in all situations. – Paul Rowland Jul 23 '10 at 10:35