Duplication records with INNER JOIN

Question

I’m validating records after a partition’s rotation.When I run the SELECT COUNT for each table I get 114,817,959 records.

SELECT COUNT(*)   FROM SMS.dbo.MessagesPerf WITH (NOLOCK) WHERE 
DateEntered >= '2021-09-01 00:00:00' AND DateEntered < '2021-10-01 00:00:00' 

SELECT COUNT(*)   FROM SMS.dbo.MessagesPerfHistory WITH (NOLOCK) WHERE 
DateEntered >= '2021-09-01 00:00:00' AND DateEntered < '2021-10-01 00:00:00'

However, when I execute an INNER JOIN made duplicate records don't allow me to view the information correctly as I get 130,962,977 records

  select 
   count(*)
  FROM [SMS].[dbo].[MessagesPerf] MP WITH (NOLOCK)
  INNER JOIN [SMS].[dbo].[MessagesPerfHistory] MH WITH (NOLOCK)
  ON MP.RowId=MH.RowId
  AND MP.[DateEntered]=MH.[DateEntered] 
  WHERE MP.DateEntered >='2021-09-01 00:00:00.000'
  and MP.DateEntered<'2021-10-01 00:00:00.000'

This time it's only validation, I only need to demonstrate with the SELECT than the records are equal before rotate the productive table for that reason I don't pretend to delete duplicates .

Can you advise me on how I could run the inner so that duplicate records don't alter the final result

In the "duplicate records" there are columns that aren't equal. How can I use for improve the query

This is the table definition for both tables, the difference is only the name and the purpose of them.

The productive table keep 1 year of information (currently year) and the historic keep two years ago

I added a new column CustomerId and now see 114837423 rows. It is possible to use null rows for matching the inner join?

  select 
   count(*)
  FROM [SMS].[dbo].[MessagesPerf] MP WITH (NOLOCK)
  INNER JOIN [SMS].[dbo].[MessagesPerfHistory] MH WITH (NOLOCK)
  ON MP.RowId=MH.RowId
  AND MP.[DateEntered]=MH.[DateEntered] 
  AND MP.[CustomerId]=MH.[CustomerId]
  WHERE MP.DateEntered >='2021-09-01 00:00:00.000'
  and MP.DateEntered<'2021-10-01 00:00:00.000'

Answer 1

If you only need to see rows from MessagesPerf and not MessagesPerfHistory, using an EXISTS subquery will avoid row duplication.

Of course, you may see incorrect results with the NOLOCK hint in place, anyway. Tsk tsk tsk.

SELECT
    records = COUNT_BIG(*)
FROM [SMS].[dbo].[MessagesPerf] AS MP WITH (NOLOCK)
WHERE EXISTS
(
    SELECT
        1/0
    FROM [SMS].[dbo].[MessagesPerfHistory] AS MH WITH (NOLOCK)
    WHERE MP.RowId = MH.RowId
    AND   MP.[DateEntered] = MH.[DateEntered]
    AND   MP.DateEntered >= '2021-09-01 00:00:00.000'
    AND   MP.DateEntered < '2021-10-01 00:00:00.000'
);

Answer 2

WITH 
cte1 AS (
    SELECT RowId, DateEntered, COUNT(*) cnt
    FROM MessagesPerf 
    WHERE DateEntered >= '2021-09-01 00:00:00' 
      AND DateEntered < '2021-10-01 00:00:00'
    GROUP BY 1, 2
    ),
cte2 AS (
    SELECT RowId, DateEntered, COUNT(*) cnt
    FROM MessagesPerfHistory 
    WHERE DateEntered >= '2021-09-01 00:00:00' 
      AND DateEntered < '2021-10-01 00:00:00'
    GROUP BY 1, 2
    )
SELECT *
FROM cte1
JOIN cte2 ON cte1.RowId = cte2.RowId
         AND cte1.DateEntered = cte2.DateEntered
         AND cte1.cnt <> cte2.cnt

This query will return (RowId, DateEntered) pairs for which the number of rows in the tables differs, including the number of rows in each separate table. If the data in the tables does not differ then the output rowset will be empty.

PS. The query assumes than neither RowId nor DateEntered is NULLable.