Microsoft KB Archive/835188

= The IDENT_CURRENT Transact-SQL statement returns an unexpected value for an empty table that has an identity column =

Article ID: 835188

Article Last Modified on 9/20/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



Bug #: 213497 (SQL Server 8.0)



SYMPTOMS
When you use the IDENT_CURRENT Transact-SQL statement to retrieve the last identity value that is generated for an empty table, the IDENT_CURRENT Transact-SQL statement returns the seed. The seed is the value that is used for the first row that is loaded into the table.



CAUSE
The IDENT_CURRENT Transact-SQL statement returns the last identity value that is generated for the identity column in the specified table. However, if the identity value has not yet been generated for the specified table, the IDENT_CURRENT Transact-SQL statement returns the seed value that is specified when the identity column in the specified table is created.



Steps to reproduce the problem
To reproduce the problem, follow these steps:  Start SQL Query Analyzer, and then connect to your instance of Microsoft SQL Server 2000.  Create a Test_Ident table that has one IDENTITY column. To do so, run the following Transact-SQL command in SQL Query Analyzer: CREATE TABLE Test_Ident (   Col1  int IDENTITY(100,1) )

GO   Use the IDENT_CURRENT Transact-SQL statement to retrieve the last identity value that was generated for the Test_Ident table. To do so, run the following Transact-SQL command in SQL Query Analyzer: SELECT IDENT_CURRENT('Test_Ident')

GO 

Although the identity values have not yet been generated for the Test_Ident table, the IDENT_CURRENT Transact-SQL statement for the Test_Ident table returns an identity value of 100.

