INFO: New NO_INDEX Option for SQL Server for Windows NT

ID: Q104446

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5, 6.5 Service Pack 1 and later, 6.5 Service Pack 2 and later, 6.5 Service Pack 3 and later, 6.5 Service Pack 4 and later, 6.5 Service Pack 5 and 5a, 6.5 Service Pack 5a, 7.0, 7.0 Service Pack 1

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 : kbother SSrvWinNT
Version : winnt:4.2x,6.0,6.5,6.5 Service Pack 1 and later,6.5 Service Pack 2 and later,6.5 Service Pack 3 and later,6.5 Service Pack 4 and later,6.5 Service Pack 5 and 5a,6.5 Service Pack 5a,7.0,7.0 Service Pack 1
Platform : winnt
Issue type : kbinfo