BUG: Upgrade of an Empty Table Resets Identity Value to Seed (263887)

SYMPTOMS

If you insert rows into a Microsoft SQL Server 6.5 table that has an identity column, then you delete all the rows in the table, and subsequently upgrade the table to Microsoft SQL Server 7.0 or Microsoft SQL Server 2000, the identity value in the upgraded table is reset to the original seed value of the table in Microsoft SQL Server 6.5.

WORKAROUND

If you must guarantee that the identity values chosen in the upgraded database do not include those that were previously used in the now-empty SQL Server 6.5 table, use the DBCC CHECKIDENT statement to reset the next identity value for the table after the upgrade.

To determine what the current value for the identity column is in the SQL Server 6.5 database, use the DBCC CHECKIDENT statement with the NORESEED parameter. Then run the DBCC CHECKIDENT statement in the upgraded database with the RESEED parameter to set the identity value to the proper starting point.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

REFERENCES

For more information about DBCC CHECKIDENT, see the "DBCC CHECKIDENT (T-SQL)" topic in SQL Server Books Online.