0

I got a problem. I use SQL Server 2008 Express.

When new row is inserted into a table the IDENT_CURRENT function returns correct value (last inserted row id). But when the last inserted row is deleted (DELETE FROM REPORT WHERE ID='last_inserted_id') the IDENT_CURRENT function returns id of the deleted row. It do not update.

I need to return the last id from the table.

SELECT IDENT_CURRENT('report')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jakentus
  • 896
  • 12
  • 22
  • `IDENT_CURRENT` works just fine - it's your expectation that is wrong. The identity values in SQL Server are **NEVER** "recycled" - once handed out, they're gone and will never be reused. – marc_s Nov 02 '13 at 07:19
  • thanks. got it. how to get last row id in any session and any scope? – jakentus Nov 02 '13 at 07:20
  • What do you need that information for?? Identity is **per table** - so such a "global" highest row id really doesn't seem very useful .... – marc_s Nov 02 '13 at 07:32
  • thank you for reply. I mean per table. not global. identity will never go reverse if the last row is deleted. but when i delete last row from a table the ident_current will give me the last identitiy. but i need to get the last row id. as in example: got tree rows in a table with id`s respectively:[1, 'a'],[2, 'b'],[3, 'c']. if you get last row id then it is 3. now, delete last row and get the last row id, so it be 2. how to realize it? thanks – jakentus Nov 02 '13 at 07:42
  • 1
    `SELECT MAX(Rowid) FROM dbo.YourTable` ??? – marc_s Nov 02 '13 at 07:43
  • @marc_s Thank you! Great, so simple. Sorry for inaccurate question, i am a newbie. :) – jakentus Nov 02 '13 at 07:45

3 Answers3

2

This is working as expected. The function returns the last identity value used, even if it's been deleted. Here's an excerpt from Microsoft's documentation:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

Brian Shamblen
  • 4,653
  • 1
  • 23
  • 37
  • Thanks. so how to get the last current id? – jakentus Nov 02 '13 at 06:22
  • You probably want to use SCOPE_IDENTITY. Here's a link to the MS docs http://technet.microsoft.com/en-us/library/ms175098.aspx – Brian Shamblen Nov 02 '13 at 06:24
  • If you're trying to capture the id of the row being deleted you'll probably need to add a trigger to the table. – Brian Shamblen Nov 02 '13 at 06:26
  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. need to get the last row id of the table in any session and any scope. ex. got tree rows in a table with id`s respectively:[1, 'a'],[2, 'b'],[3, 'c']. if you get last row id then it is 3. now, delete last row and get the last row id, so it be 2. simple actually. how to realize it? thanks – jakentus Nov 02 '13 at 07:11
1

Identity always goes up. It won't go down if you delete rows. Plus it can have 'blanks' in values. Read this for detailes http://technet.microsoft.com/en-us/library/ms186775.aspx

unconnected
  • 991
  • 1
  • 10
  • 21
  • thanks. so how to get last row id in any session and scope? i wrote an example to @Brian Shamblen. ex. got tree rows in a table with id`s respectively:[1, 'a'],[2, 'b'],[3, 'c']. if you get last row id then it is 3. now, delete last row and get the last row id, so it be 2. how to realize it? thanks – jakentus Nov 02 '13 at 07:14
  • Maybe if you describe the purpose of your question it will be easier to answer. SELECT max(id) returns current last row. SELECT @@IDENTITY returns id generated during last insert operation. And I cannot catch what value you try to get and it's purpose. – unconnected Nov 02 '13 at 15:07
0

If you want the max ID you can find in your table, just use

SELECT MAX(ID) FROM table
GCallie
  • 413
  • 1
  • 3
  • 11