How to quickly compare two tables in SQL Server

Original: How to quickly compare two tables in SQL Server

How to quickly compare two tables in SQL Server

Unconsciously write the last blog post for 2014 years ~

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: CREATE TABLET1_old (IDint  not NULL, Log_timeDATETIME DEFAULT "') ;
CREATE TABLEt1_new (IDint  not NULL, Log_timeDATETIME DEFAULT "') ;--the number of records for both tables is 100. Select Count(*) fromT1_old;Select Count(*) fromT1_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.

SELECT  COUNT(*) from(SELECT    *           from      [T1_old]          UNION          SELECT    *           from      [t1_new]        )  asT;INSERT  into [dbo].[t1_new]        ( [ID],[Log_time] )VALUES(1,"'),(3,"'),(4,"')INSERT  into [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      [t1_new]          UNION          SELECT    *           from      [T1_old]        )  asT

Two-table data

The result of the query is 4.

Method Three: EXCEPT subtraction zeroing

SELECT  COUNT(*) from(SELECT    *           from      [dbo].[t1_new]          EXCEPT          SELECT    *           from      [dbo].[T1_old]        )  asT;SELECT  COUNT(*) from(SELECT    *           from      [dbo].[T1_old]          EXCEPT          SELECT    *           from      [dbo].[t1_new]        )  asT;SELECT *  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_newcount BIGINTDECLARE @count BIGINTSELECT  @t1_newcount = COUNT(*) fromt1_new;SELECT  @count = COUNT(*) from    [T1_old]  asaINNER JOIN [t1_new]  asB on [b].[ID] = [a].[ID]                                     and [b].[Log_time] = [a].[Log_time] --If there are other fields in the table that you add yourselfPRINT @countPRINT @t1_newcountIF(@count = @t1_newcount )    BEGIN         SELECT  'Equal'    END ELSE    BEGIN        SELECT  'Not equal'    END 

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]SELECT SUM(CHECKSUM (*)) asChecksumvalue from [dbo].[T1_old]SELECT SUM(CHECKSUM (*)) asChecksumvalue from [dbo].[t1_new]SELECT SUM(CHECKSUM (*)) asChecksumvalue 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

How to quickly compare two tables in SQL Server