Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/104446

From BetaArchive Wiki
Knowledge Base


INFO: New NO_INDEX Option for SQL Server for Windows NT

Article ID: 104446

Article Last Modified on 3/14/2005



APPLIES TO

  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 6.5 Service Pack 1
  • Microsoft SQL Server 6.5 Service Pack 2
  • Microsoft SQL Server 6.5 Service Pack 3
  • Microsoft SQL Server 6.5 Service Pack 4
  • Microsoft SQL Server 6.5 Service Pack 5a
  • Microsoft SQL Server 6.5 Service Pack 5a
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 7.0 Service Pack 1



This article was previously published under Q104446

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.

Please note that in SQL Server versions 6.0 and later, the option has been changed to NOINDEX.

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)


When the NO_INDEX option is used, only the data portion of user-defined tables is checked for consistency. Indexes are ignored. System tables, however, continue to be checked in their entirety.

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.


Additional query words: Windows NT

Keywords: kbinfo kbother KB104446