Using @@IDENTITY in SQL on a specific table

Question

How can I get the @@IDENTITY for a specific table?

I have been doing

select * from myTable 

as I assume this sets the scope, from the same window SQL query window in SSMS I then run

select @@IDENTITY as identt

It returns identt as null which is not expected since myTable has many entrie in it already..

I expect it to return the next available ID integer.

myTable has a ID column set to Primary key and auto increment.

Answer 1

You can only truly use SELECT @@IDENTITY after an insert - the last insert into a table that has an IDENTITY column is the value you'll get back.

You cannot "limit" it to a table - the value in @@IDENTITY - and by the way, I'd strongly recommend using SCOPE_IDENTITY() instead!! - is the last value on any IDENTITY column that was set.

The problem with @@IDENTITY is that it will report back the last IDENTITY value inserted into any table - if your INSERT into your data table will cause e.g. a trigger to write an entry into an Audit table and that Audit table has an IDENTITY field, you'll get back that IDENTITY value - not the one inserted into your table. SCOPE_IDENTITY() solves that.

Answer 2

I've never known @@IDENTITY to be used this way, i've only ever used it to access the ID of a newly inserted record.

Answer 3

@@IDENTITY only works after an insert statement.

SELECT MAX(IDCOL)+1 FROM table will give you the next id.

Answer 4

You can use IDENT_CURRENT

IDENT_CURRENT( 'table_name' )

Note that IDENT_CURRENT returns the last identity value for the table in any session and any scope. This means, that if another identity value was inserted after your identity value then you will not retrieve the identity value that you inserted.

Answer 5

IDENT_CURRENT does what you want. But don't.

This is in addition to marc_s' answer

Answer 6

That's correct. @@IDENTITY cannot be used the way you think it can be. It can only be used after an INSERT into a table. Let's consider this for a scenario:

You have two tables: Order (Primary Key: OrderID), OrderDetails (Foreign Key: OrderID)

You perform INSERT INTO Order VALUES('Pillows') -- Note that OrderId is not mentioned in Values since it is auto number (primary key)

Now you want to perform insert into OrderDetail. But you don't always remember how many records there were in Order table prior to you having inserted the record for 'Pillows' and hence you don't remember what was the last PrimaryKey inserted into Order table. You could but even then you wouldn't want to specifically mention to insert (let's say OrderID of 1) when you insert into OrderDetail table.

Hence, your OderDetail insert would work kinda like so: INSERT INTO OrderDetail VALUES (@@IDENTITY,'Soft Pillows')

Hope this explains the user of @@IDENTITY.