Problem with IDENT_CURRENT( ) Function

Question

I'm developing a database application with C#. My database management system is SQL server 2005. I'm using the IDENT_CURRENT( ) function to get the ID of the last inserted row in data tables.

According to the documentation IDENT_CURRENT( ) function should return null when invoked against a newly created empty table. However in my application it returns 1 for that kind of tables. Any idea why it behaves like that?

Also please do let me if there is a better way to get the last inserted ID from a data table...

Brij · Answer

hi if u use @@Identity.. u will be in trouble. :) reason: if table on which u are performing @@Identity operation has a triggers which perform some insert operation on other tabel then u will get the identity value for that table. (SCOPE IDENTITY) So better to use Ident_current. we generally use @@Ident_Current to get the last generated identity value in a table so that this generated value can be used in secondary tables. Other ways: u can use a seperate table to hold information regarding the primary key (current value) of u'r table and its(table name) name. mstKey TableName CurrentValue and while inserting record read value from this table increment current value. use all operation inside transaction block

Manuel · Answer

IDENT_CURRENT returns (null) if a table does not contain an identity column. Otherwise the value of IDENT_CURRENT is just the actual position of the identity. If no rows were inserted into a table after creating an identity column it's the initial value of the identity. If the table contains rows IDENT_CURRENT has not to be the last id of the table. You could reset the identity column with an other seed or you could delete the last row of a table. So you can use it to get the id of a value you inserted into a table (but here I would use @@IDENTITY) but not to get the last id securely. If values only are incremented you could use MAX(idColumn) to get last not deleted inserted column.