0

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 enter image description here

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

enter image description here

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'
2
  • Tables' definitions please.
    – J.D.
    Nov 28, 2023 at 19:48
  • 1
    INNER JOIN in common assumes that the joining expression is unique in at least one of the tables. If not then join multiplying is observed.
    – Akina
    Nov 28, 2023 at 20:05

2 Answers 2

3

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'
);
1
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.