Microsoft KB Archive/928810
Article ID: 928810
Article Last Modified on 11/2/2007
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Personal Edition
Bug: #50000540 (SQL Hotfix)
After you run the alter table Transact-SQL command to change a column that is not null to null, you receive incorrect results. This problem occurs if the column is a clustered index key in Microsoft SQL Server 2000. Additionally, you experience any of the following corruption symptoms:
- You receive records that have null columns. However, you only want rows that have columns that are not null.
- A database is marked as suspect. Additionally, the database becomes unavailable. In the SQL Server error log, you see runtime errors that resemble the following:
- If you run the dbcc checkdb command, you experience corruption errors that resemble the following:
Note You do not experience this problem in SQL Server 2005.
This problem occurs if the following conditions are true:
- SQL Server does not synchronize column status between the syscolumns system table and the sysindexes system table.
- A column is part of a clustered index key that may be null.
This problem may cause SQL Server to perform incorrect assumptions during data modification operations, such as the insert operation.
To work around this problem, use any of the following methods:
- Make sure that the database is online. Then, run the dbcc dbreindex command or the dbcc checktable command by using the repair_rebuild option.
- If the database is marked as suspect, telephone Microsoft Customer Support Services. For a complete list of Microsoft Customer Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:
- Run the alter table Transact-SQL command to change a column that is a key of a clustered index from not null to null.
Note If you run the alter table Transact-SQL command, you must immediately run the dbcc dbreindex command. Or, you can re-create the index on the table.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
To determine whether database corruption has occurred, use one of the following methods:
- Run either the dbcc checkdb command or the dbcc checktable command.
- The problem exists when the command output recommends running the dbcc checkdb command that uses the repair_rebuild option to fix the problem.
- The problem does not exist when the command output recommends running the command that uses the repair_allow_data_loss option.
Run the following query. The query determines whether some columns are part of an index key. The query also shows differences in the syscolumns table and in the sysindexes.status column:
print 'the following tables have indexes defined on a column that is nullable but sysindexes.status column says not nullable' print 'this could lead to corruption or incorrect results' select cast(object_name(i.id) as varchar(25)) 'object name', cast ( i.name as varchar(30)) 'index name' , * from sysindexkeys k inner join syscolumns c on (k.id = c.id and k.colid = c.colid) inner join sysindexes i on (i.id=k.id and i.indid=k.indid) where c.isnullable =1 and ((i.status & 0x200000) = 0)
Keywords: kbtshoot kbprb KB928810