How to quickly compare two tables in SQL Server

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