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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
Bug #: 213497 (SQL Server 8.0)
SQL Server 8.0:213497

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.

MORE INFORMATION

Steps to reproduce the problem

To reproduce the problem, follow these steps:
  1. Start SQL Query Analyzer, and then connect to your instance of Microsoft SQL Server 2000.
  2. 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
  3. 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.

REFERENCES

For more information about the IDENT_CURRENT Transact-SQL statement, see the "IDENT_CURRENT" topic in SQL Server Books Online.
Modification Type:MajorLast Reviewed:9/20/2005
Keywords:kbprb kbtable KB835188 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.