PRB: Index corruption occurs in a database that is upgraded to SQL Server 2000 (298806)

SYMPTOMS

If you run a DBCC CHECKDB statement on a database that was upgraded from SQL Server 7.0 to SQL Server 2000, the following error message may occur:

Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table '<TABLENAME>' (ID <OBJECTID>). Missing or invalid key in index '<INDEXNAME>' (ID <INDEXID>) for the row:

Server: Msg 8955, Level 16, State 1, Line 1 Data row (1:133:42) identified by (RID = (1:133:42) ) has index values (<INDEXNAME> = 0).

Server: Msg 8952, Level 16, State 1,
Line 1 Table error: Database '<DBNAME>', index '<TABLENAME>.<INDEXNAME>' (ID <OBJECTID>) (index ID <INDEXID>). Extra or invalid key for the keys:

Server: Msg 8956, Level 16, State 1, Line 1
Index row (1:137:0) with values (<INDEXNAME> = 0) points to the data row identified by (RID = (1:133:42)).

CAUSE

The sysindexes.status field should have a value of 2097152 so that the nonclustered index column can allow the insertion of NULL values for both SQL Server 7.0 and SQL Server 2000.

In SQL Server 7.0, if the sysindexes.status value is 0 for the nonclustered index in a table, the nonclustered index also allows the insertion of NULL values. If you run a DBCC CHECKDB statement on the SQL Server 7.0 database, the problem is not reported. After you upgrade the table in SQL Server 2000, the DBCC CHECKDB statement reports the error message described in the "Symptoms" section because a NULL value is not allowed when the sysindexes.status value is not 2097152.

WORKAROUND

To work around this problem, use any one of the following three methods:

Run a DBCC DBREINDEX statement on the SQL Server 2000 table to rebuild the nonclustered index on the column that has a null value inserted.
Drop, and then re-create the nonclustered index on the column that is inserted with a null value on the SQL Server 2000 table.
Drop, and then re-create the indexes or run a DBCC DBREINDEX statement on SQL Server 7.0 before you upgrade the databases to SQL Server 2000.

MORE INFORMATION

This problem does not cause any data or index corruption. The problem is in the metadata which is corrected only by dropping and re-creating the indexes.