Find duplicate records from a table in SQL Server database

In this article, we learn about find duplicate records from a table in the SQL Server database

SQL Query for finding duplicate records:

Method 1:

SELECT columnName, COUNT(*) AS TotalRecords, Max(ID) AS Duplicates
FROM tableName
GROUP BY columnName
HAVING (COUNT(*) > 1)

Method 2 with CTE:

WITH CTE AS(
   SELECT columnName1,columnName2,
       RowNo = ROW_NUMBER() OVER (PARTITION BY columnName1 ORDER BY columnName1)
   FROM tableName
)
SELECT * FROM CTE WHERE RowNo > 1

Note: PARTITION BY columnName1 represent the partition base column, for example, if we have a customer table and the email column has duplicate email addresses then we make the partition on the base of email column records.