Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (P-Z)

 

VALIDATE statement

Use this statement to validate the current database, or a single table or materialized view in the current database.

Syntax 1 - Validating tables and materialized views
VALIDATE { 
TABLE [ owner.]table-name
   | MATERIALIZED VIEW [ owner.]materialized-view-name }
[ WITH EXPRESS CHECK ]
Syntax 2 - Validating a database
VALIDATE { CHECKSUM | DATABASE }
Syntax 3 - Validating indexes
 VALIDATE {
INDEX index-name 
| [ INDEX ] FOREIGN KEY role-name 
| [ INDEX ] PRIMARY KEY  }
ON [ owner.]object-name
}
object-name : 
table-name 
| materialized-view-name
Parameters
  • 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.

Remarks

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.

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.

Caution

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.

Permissions

Must have DBA or VALIDATE authority.

Side effects

None.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.