sp_table_validation (T-SQL)

Either returns rowcount or checksum information on a table or compares the provided rowcount or checksum information with the specified table.

Syntax

sp_table_validation [@table =] 'table'
    [,[@expected_rowcount =] expected_rowcount]
    [,[@expected_checksum =] expected_checksum]
    [,[@rowcount_only =] rowcount_only]
    [,[@owner =] 'owner']
    [,[@full_or_fast =] full_or_fast]
    [,[@shutdown_agent =] shutdown_agent]
    [,[@table_name =] table_name]

Arguments

[@table =] 'table'

Is the name of the table. table is sysname, with no default.

[@expected_rowcount =] expected_rowcount

Is whether to return the expected number of rows in the table. expected_rowcount is int, with a default of NULL. If NULL, the actual rowcount is returned as an output parameter. If a value is provided, that value is checked against the actual rowcount to identify any differences.

[@expected_checksum =] expected_checksum

Is whether to return the expected checksum for the table. expected_checksum is numeric, with a default of NULL. If NULL, the actual checksum is returned as an output parameter. If a value is provided, that value is checked against the actual checksum to identify any differences.

[@rowcount_only =] rowcount_only

Is whether to return only the rowcount for the table. rowcount_only is bit, with a default of 1.

[@owner =] 'owner'

Is the name of the owner of the table. owner is sysname, with a default of NULL.

[@full_or_fast =] full_or_fast

Is the method used to calculate the rowcount. full_or_fast is tinyint, with a default of 2, and can be one of these values.

 

Value	Description
0	Does full count using COUNT(*).
1	Does fast count from sysindexes.rows. Counting rows in sysindexes is much faster than counting rows in the actual table. However, because sysindexes is lazily updated, the rowcount may not be accurate.
2 (default)	Does conditional fast counting by first trying the fast method. If fast method shows differences, reverts to full method. If expected_rowcount is NULL and the stored procedure is being used to get the value, a full COUNT(*) is always used.

[@shutdown_agent =] shutdown_agent

If the Distribution Agent is executing sp_table_validation, is whether the Distribution Agent should shut down immediately upon completion of the validation. shutdown_agent is bit, with a default of 0. If 0, the replication agent does not shut down. If 1, error 20578 is raised and the replication agent is signaled to shut down.

[@table_name =] table_name

Is the table name of the view used for output messages. table_name is sysname, with a default of @table.

Return Code Values

If performing a checksum validation and the expected checksum equals the checksum in the table, sp_table_validation returns a message that the table passed checksum validation. Otherwise, it returns a message that the table may be out of synchronization and reports the difference between the expected and the actual number of rows.

If performing a rowcount validation and the expected number of rows equals the number in the table, sp_table_validation returns a message that the table passed rowcount validation. Otherwise, it returns a message that the table may be out of synchronization and reports the difference between the expected and the actual number of rows.

Remarks

sp_table_validation is used in all types of replication

Checksum computes a 32-bit cyclic redundancy check (CRC) on the entire row image on the page. It does not selectively check columns and cannot operate on a view or vertical partition of the table. Also, the checksum skips the contents of text and image columns (by design).

When doing a checksum, the structure of the table must be identical between the two servers; that is, the tables must have the same columns created and existing in the same order, same data types and lengths, and same NULL/NOT NULL conditions. For example, if the Publisher did a CREATE TABLE, then an ALTER TABLE to add columns, but the script applied at the Publisher is a simple CREATE table, the structure is NOT the same. If you are not certain that the structure of the two tables is identical, look at syscolumns and confirm that the offset in each table is the same.

Floating point values are likely to generate checksum differences if character-mode bcp was used, which is the case if the publication has heterogeneous subscribers. These are due to minor and unavoidable differences in precision when doing conversion to and from character mode.

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_table_validation.

See Also