sp_table_validation
Either returns rowcount or checksum information on a table or indexed view, or compares the provided rowcount or checksum information with the specified table or indexed view. This stored procedure is executed at the Publisher on the publication database.
Syntax
sp_table_validation [ @table = ] 'table'
[ , [ @expected_rowcount = ] type_of_check_requested OUTPUT]
[ , [ @expected_checksum = ] expected_checksum OUTPUT]
[ , [ @rowcount_only = ] rowcount_only ]
[ , [ @owner = ] 'owner' ]
[ , [ @full_or_fast = ] full_or_fast ]
[ , [ @shutdown_agent = ] shutdown_agent ]
[ , [ @table_name = ] table_name ]
[ , [ @column_list = ] 'column_list' ]
Arguments
[@table =] 'table'
Is the name of the table. table is sysname, with no default.
[@expected_rowcount =] expected_rowcount OUTPUT
Specifies 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 OUTPUT
Specifies 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 =] type_of_check_requested
Specifies what type of checksum or rowcount to perform. type_of_check_requested is smallint, with a default of 1. If 0, perform a SQL Server 7.0 compatible checksum. If 1, perform a rowcount check only. If 2, perform a rowcount and checksum.
[@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, specifies 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 signalled 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.
[@column_list = ] 'column_list'
Is the list of columns that should be used in the binary_checksum function. column_list is nvarchar(4000), with a default of NULL. Enables validation of merge articles to specify a column list that excludes computed and timestamp columns.
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 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.