How to find duplicate records in SQL Server?

10 months ago

Noah Thompson

1 minute

One way to find duplicate records in SQL Server is by using the following methods:

Option 1: Use the COUNT() function and GROUP BY clause to find duplicate records.

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

Option 2: Utilize window functions and CTE (Common Table Expressions) to find duplicate records.

WITH CTE AS (
  SELECT column1, column2,
         ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY column1) AS rn
  FROM table_name
)
SELECT column1, column2
FROM CTE
WHERE rn > 1;

You can choose the appropriate method to find duplicate records based on the specific situation.