Data Integrity Validation

Transact-SQL has a set of DBCC statements that are used to verify the integrity of a database. Microsoft® SQL Server™ version 7.0 improves these statements in several ways.

The need to run the statements has been reduced significantly. There have been two architectural changes in SQL Server that make the databases even more robust than in earlier versions of SQL Server:

• The database engine has fail-fast logic to detect potential errors closer to the time they originate. This means errors are less likely to persist long enough to cause problems in a database.
• The data structures in the database are simpler. This means they are easier to manage and less likely to have errors.

In earlier versions of SQL Server, it was recommended that DBCC statements be made a regular part of a database backup strategy. It was recommended that databases be checked before being backed up. In SQL Server 7.0, this is no longer necessary.

The DBCC statements themselves also run significantly faster than in earlier versions of SQL Server. Checks of complex databases typically run 8 to 10 times faster in version 7.0 than in 6.5 and checks of some individual objects have run over to 300 times faster in version 7.0 than 6.5. In SQL Server 6.5, DBCC CHECKDB processed the tables serially. For each table, it first checked the structure of the underlying data and then checked each index individually. This resulted in very random pattern of reads. In SQL Server 7.0, DBCC CHECKDB performs a serial scan of the database while performing parallel checks of multiple objects as it proceeds.

Another option introduced in SQL Server 7.0 is to have the DBCC statements repair minor problems they might encounter. The statements have the option to repair certain errors in the B-tree structures of indexes, or errors in some of the allocation structures.

See Also