-1

I have a table where it auto increment the primary key. I want to know the last instead primary key's number(which is always the primary key with the biggest value). I can't do this by counting the number of rows because some rows has been deleted after the creation. any help would be appreciated.

D P.
  • 1,039
  • 7
  • 27
  • 56
  • 3
    Look at the `SCOPE_IDENTITY()` function. – Alex K. Jul 21 '14 at 15:30
  • SELECT @@IDENTITY Check Here for some other conditions and more info: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ – PWilliams0530 Jul 21 '14 at 15:30
  • http://msdn.microsoft.com/en-us/library/ms190315.aspx – David Jul 21 '14 at 15:31
  • This question has multiple duplicates, just do some basic searching please. – Andrew Jul 21 '14 at 15:31
  • if you just inserted it, use: ``SCOPE_IDENTITY``, if it was already inserted, use: ``SELECT MAX(YourPK) FROM YourTable`` – KM. Jul 21 '14 at 15:52

2 Answers2

0

If you are in the scope of the stored procedure where the insert is taking place you can use SCOPE_IDENTITY().

if you are outside of the scope of the insert you could: Select max(id) as maximumId from Table

sarin
  • 5,227
  • 3
  • 34
  • 63
  • This is bad, check thishttp://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert – T.S. Jul 21 '14 at 15:33
  • OP makes no reference to the context of how this will be used. In some circumstances selecting the max value will be ok. It's another alternative hence why I listed it. – sarin Jul 21 '14 at 15:35
  • 1
    I agree that using MAX(ID) is not good. The brings up concurrency issues that are difficult to deal with. Either use SCOPE_IDENTITY or OUTPUT. – Sean Lange Jul 21 '14 at 16:17
-1

You can use CHECKIDENT too get the last Inserted ID.

DBCC CHECKIDENT ('Schema.TableName', NORESEED);

Wyn
  • 24
  • 2
  • Totally open to race conditions, this is really one of the ways to avoid. – Andrew Jul 21 '14 at 18:05
  • how do you get race conditions when your not trying to insert anything? – Wyn Jul 28 '14 at 13:18
  • Original question is : SQL sever, get the number associated with the last inserted primary key - clearly they are inserting and then retrieving the key. – Andrew Jul 28 '14 at 14:10
  • Yes, so the reterival of the last ID would be after the insert. They don't say they are using it to insert with. Plus checkindent prints so would be only for information not passing into a variable. – Wyn Jul 28 '14 at 14:50
  • So two people inserting and then checking - would see the same ID. – Andrew Jul 28 '14 at 17:25