Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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 and at the Subscriber on the subscription database. Not supported for Oracle Publishers.
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' ]
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.
sp_table_validation is used in all types of replication. sp_table_validation is not supported for Oracle Publishers.
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 Subscriber 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 non-SQL Server subscribers. These are due to minor and unavoidable differences in precision when doing conversion to and from character mode.