In general, how to detect the content of the two tables is consistent, reflected in the replication at the end of the publisher and the end of the subscriber on the data above
I've got a list of ways to solve this kind of problem from the database level.
The first step of course is to check whether the number of records is consistent, otherwise do not think of other methods ~
Here we use two tables t1_old,t1_new to demonstrate
Method Introduction
Method One: Honestly look at the table structure and the number of table records, the disadvantage is that the two tables do not see the data is consistent, just see the table structure and record number is consistent
--Table structure:CREATETABLET1_old (IDIntNotNull, Log_timeDatetimeDEFAULT‘‘);
create table t1_ New (ID int not null< Span style= "color: #000000;" >, log_time datetime default "); -- Both tables have a record count of 100. select count (*) from T1_old; Select count (from T1 _new
Method Two: The addition de-duplication Union operator excludes duplicates, but there are bugs, in some cases it is not simple to represent the result set consistent, equivalent to invalid
Since the Union itself has a unique sort of record of the upper and lower connections, it is relatively simple to detect.
SELECTCOUNT (*)From (SELECT*From[T1_old]UNIONSELECT*From[T1_new])AsTINSERTInto[Dbo].[T1_new]([Id],[Log_time])VALUES (1,‘‘),(3,‘‘),(4,‘‘)INSERTInto[Dbo].[T1_old]([Id],[Log_time])VALUES (1,‘‘),(2,‘‘),(3,‘‘)SELECT*From[Dbo].[T1_new]SELECT*From[Dbo]. [t1_old]select count (*from (select * from Span style= "color: #ff0000;" >[t1_newunion select * from [t1_old]as T;
Two-table data
The result of the query is 4.
Method Three: EXCEPT subtraction zeroing
SELECTCOUNT (*)From (SELECT*From[Dbo].[T1_new]EXCEPTSELECT*From[Dbo].[T1_old])AsTSELECTCOUNT (*)From (SELECT*From[Dbo].[T1_old]EXCEPTSELECT*From[Dbo]. [t1_new]) as T;* from [dbo]. [t1_new]select * from [dbo]. [t1_old "
If the results are not correct, then the inconsistent conclusions are given directly.
Method Four: Use the full table inner JOIN, this is also the worst practice, of course, this refers to the table records in the case of super-many cases
DECLARE@t1_newcountBIGINTDECLARE@countBIGINTSELECT@t1_newcount=COUNT (*)FromT1_new;SELECT@count=COUNT (*)From[T1_old]AsAINNERJOIN[T1_new]As BOn[B].[Id]=[A].[Id]and[B].[Log_time]=[A].[Log_time]-- If there are other fields in the table that you add yourself print print @t1_newcount if ( @count = @t1_newcount ) begin ' equal "end else begin select Span style= "color: #ff0000;" > '
Method five: With SQL Server's own Tablediff tool, Microsoft made this tool to compare data in the published and subscribed tables in replication
Identical are equal meanings.
Method Six: Verify that the Subscriber is consistent with the data on the publishing side with the verification subscription function at the publisher
Method Seven: Compare the checksum values of the contents of two tables with checksum check
But this approach is only confined to the two-table structure.
I copy the data from the [t1_new] table to a new table for comparison
SELECT*From[Dbo].[T1_new]SELECT*From[Dbo].[T1_newreplica]SELECTSUM (CHECKSUM (*))As ChecksumvalueFrom[Dbo].[T1_old]SELECT SUM (CHECKSUM (*)) as checksumvalue from [dbo]. [t1_new]SELECT SUM (CHECKSUM (*)) as checksumvalue from [dbo]. [T1_newreplica]
Summarize
From the methods provided in the above several databases, it is relatively reliable to use except subtraction to zero, and other methods are more suitable for detection in specific situations.
If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o
The copyright of this article is owned by the author and cannot be reproduced without the author's consent.
How to quickly compare two tables in SQL Server