INF: New NO_INDEX Option for SQL Server for Windows NT

  - Microsoft SQL Server, version 4.2

SUMMARY

This article provides information on the new NO_INDEX option for DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKALLOC. This enhancement has been added in Microsoft SQL Server version 4.2 for Microsoft Windows NT.

MORE INFORMATION

The syntax for the NO_INDEX option is as follows:

   DBCC CHECKDB (database_name, NO_INDEX)

   DBCC CHECKTABLE (table_name, NO_INDEX)

   DBCC CHECKALLOC (database_name, NO_INDEX)

Using this option can lead to a substantial reduction in the time that it takes to run the DBCC commands against very large databases. In some cases this time can be so prohibitive as to prevent these consistency checks being run on a regular basis.

In a production environment, the NO_INDEX option is provided to counter such situations. Not checking indexes is relatively safe since problems in index structures can usually be corrected by dropping and recreating the index.

If this option is used, it is still highly recommended that a full CHECKDB and CHECKALLOC be run, whenever possible, to detect any problems in the index structures.