WITH EXPRESS CHECK
In addition to the default checks, check that the number of rows in the table or materialized view matches the number
of entries in the index. This option does not perform individual index lookups for each row, nor does it perform checksum
validation. This option can significantly improve performance when validating large databases with a small cache.
Validation of tables includes a checksum validation, and validation that the number of rows in a table matches the number
of rows in each index associated with the table. If you specify WITH EXPRESS CHECK, a checksum validation is not performed.
The VALIDATE DATABASE statement validates that all table pages in the database belong to the correct object. VALIDATE DATABASE
also performs a checksum validation, but does not validate the indexes, or check data correctness. If you start database validation
while the database cleaner is running, the validation does not run until the database cleaner is finished running. See sa_clean_database system procedure.
Use the VALIDATE CHECKSUM statement to perform a checksum validation on the database. The VALIDATE CHECKSUM statement ensures
that database pages have not been modified on disk. When a database is created with checksums enabled, a checksum is calculated
for each database page before it is written to disk. VALIDATE CHECKSUM reads each database page from disk and calculates the
checksum for each page. If the calculated checksum for a page does not match the stored checksum for that page, an error occurs
and information about the invalid page appears in the database server messages window. The VALIDATE CHECKSUM statement can
also be useful on databases with checksums disabled, since critical database pages still include checksums.
Use the VALIDATE INDEX statement to validate an index, including index statistics, on a table or a materialized view. The
VALIDATE INDEX statement ensures that every row referenced in the index actually exists. For foreign key indexes, it also
ensures that the corresponding row exists in the primary table. This check complements the validity checking carried out by
the VALIDATE TABLE statement. The VALIDATE INDEX statement also verifies that the statistics reported on the specified indexes
are accurate. If they are not accurate, an error is generated.
Validating a table or an entire database should be performed while no connections are making changes to the database; otherwise,
errors may be reported indicating some form of database corruption even though no corruption actually exists.