Use BINARY_CHECKSUM to detect changes in the table's rows

Use BINARY_CHECKSUM to detect changes in the table's rows without USEAdventureWorks2012; GOCREATETABLEmyTable (column1int, column2varchar (256); values (1, test); values (*) frommyTable; GOUPDATEmyTablesetcolumn2

Using BINARY_CHECKSUM to detect changes in rows in a TABLE does not USE AdventureWorks2012; gocreate table myTable (column1 int, column2 varchar (256); goinsert into myTable VALUES (1, 'test '); GOSELECT BINARY_CHECKSUM (*) from myTable; GOUPDATE myTable set column2 ='

Use BINARY_CHECKSUM to detect changes in the table's rows <无>

USE AdventureWorks2012;GOCREATE TABLE myTable (column1 int, column2 varchar(256));GOINSERT INTO myTable VALUES (1, 'test');GOSELECT BINARY_CHECKSUM(*) from myTable;GOUPDATE myTable set column2 = 'TEST';GOSELECT BINARY_CHECKSUM(*) from myTable;GO

Use the following method: select id, binary_checksum (*) from tb1select id binary_checksum (*) from tb2. You can compare the two tables. If the check value is the same, it means it passes. ------------------------------------------ Details are as follows: select. id from (select id, CA = binary_checksum (*) from tb1) A where. CA not in (select B. CB from (select id, CB = binary_checksum (*) from tb2) B ------------- published on | only the author of course, if you simply want to quickly check whether the table and table are the same, use sp_table_validation directly. In fact, its core is to use binary_checksum --------------- 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 Subscriber database. not supported for Oracle Publishers. syntaxsp_table_validation [@ table =] 'table' [, [@ expected_rowcount =] partition 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']

Sp_table_validation or returns information about the number of rows or check values of the table or index view, or compares the provided number of rows or check values with the specified table or index view. This stored procedure is executed on the Publishing Database of the Publishing Server. 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'] parameter [@ table =] 'table 'Is the table name. The data type of the table is sysname, and no default value is set. [@ Expected_rowcount =] expected_rowcount OUTPUT specifies whether to return the expected number of rows in the table. The data type of expected_rowcount is int, and the default value is NULL. If it is NULL, the actual number of rows is returned as the output parameter. If a value is provided, the value is checked against the actual number of rows to determine any difference. [@ Expected_checksum =] expected_checksum OUTPUT specifies whether to return the expected check value of the table. The data type of expected_checksum is numeric, and the default value is NULL. If it is NULL, the actual check value is returned as an output parameter. If a value is provided, the value is checked against the actual check value to determine any difference. [@ Rowcount_only =] type_of_check_requested indicates the type or number of rows of the check value to be executed. The data type of type_of_check_requested is smallint, and the default value is 1. If the value is 0, the SQL Server 7.0 compatible check value is executed. If the value is 1, only the number of rows is checked. If the value is 2, the number of rows and the check value are executed. [@ Owner =] 'owner' is the name of the table owner. The owner's data type is sysname, and the default value is NULL. [@ Full_or_fast =] full_or_fast is the method for calculating the number of rows. The data type of full_or_fast is tinyint. The default value is 2, which can be one of the following values. The value description 0 uses COUNT (*) for complete counting. 1. Fast count from sysindexes. rows. Counting rows in sysindexes is much faster than counting rows in real tables. However, because sysindexes is rarely updated, the row count may be inaccurate. 2 (default value) use the quick method to calculate the condition quickly. If the quick method shows travel differences, use the complete method instead. If the value of expected_rowcount is NULL and the value is being obtained using the stored procedure, the full COUNT (*) is always used (*). [@ Shutdown_agent =] shutdown_agent if the distribution agent is executing sp_table_validation, specify whether the distribution agent is closed immediately after verification. The data type of shutdown_agent is bit. The default value is 0. If the value is 0, the replication agent is not closed. If the value is 1, error 20578 is generated and the replication agent is closed. [@ Table_name =] table_name is the table name of the view used to output messages. The data type of table_name is sysname, and the default value is @ table. [@ Column_list =] 'column _ list' is the list of columns used for the binary_checksum function. The data type of column_list is nvarchar (4000), and the default value is NULL. Enable merge project verification to specify a list of columns excluded from calculation columns and timestamp columns. Return code value if verification is performed and the expected verification value is the same as the verification value in the table, sp_table_validation returns a message indicating that the table has passed verification. Otherwise, a message is returned indicating that the table may not be synchronized, and the difference between the expected number of rows and the actual number of rows is reported. If the number of rows is verified and the expected number of rows is equal to the number of rows in the table, sp_table_validation returns a message indicating that the table has passed the number of rows verification. Otherwise, a message is returned indicating that the table may not be synchronized, and the difference between the expected number of rows and the actual number of rows is reported. Note sp_table_validation for all types of replication. The check value calculates the 32-bit cyclic redundancy check (CRC) on the entire row image of the page ). It does not selectively check columns and cannot operate on the vertical partitions of the view or table. In addition, the check value skips the content of the text and image columns (as designed ). During the check, the table structures of the two servers must be identical; that is, the columns in the table must be the same, the column sequence, data type, length, and NULL/not null conditions must be the same. For example, if the publisher uses create table and then uses alter table to add columns, but the script applied on the publisher is a simple create table, the TABLE structure is different. If you cannot determine whether the two tables have the same structure, you can view syscolumns and check whether the offsets in each table are the same. If the Character Mode bcp is used (this happens when the publication contains a heterogeneous subscription server), the floating point value may be different. This is because there are inevitable minor differences in precision during character mode conversion. Only sysadmin fixed server role members or db_owner fixed database role members can execute sp_table_validation. See the sp_article_validationsp_publication_validation system stored procedure.