How to find and Delete Duplicate records in SQL Server table

How to find and Delete Duplicate records in SQL Server table

Solution:

Step1: Find duplicate records by using Group by OR CTE (Common Table Expression)

/* Using GroupBY */

SELECT FirstName,
            LastName,
            COUNT(*) AS RecordCnt
    FROM   dbo.Customer
    GROUP  BY FirstName,
            LastName
    HAVING COUNT(*) > 1

or

/* Using CTE */

WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName
            ORDER BY (
            SELECT 1) ) AS RowNo
        FROM   dbo.Customer)
       SELECT *
    FROM   CTE
    WHERE  RowNo > 1

Step2: Delete duplicate records by using Group by OR CTE (Common Table Expression)

/* Delete duplicate records by using Group By */

DELETE FROM 
   FROM dbo.Customer C
         LEFT JOIN 
    ( 
   SELECT FirstName,
            LastName,
            COUNT(*) AS RecordCnt
    FROM   dbo.Customer
    GROUP  BY FirstName,
            LastName
    HAVING COUNT(*) > 1) DR
   ON DR.FirstName=C.FirstName
   AND DR.LastName=C.LastName
   WHERE DR.FirstName IS NOT NULL
   AND DR.LastName IS NOT NULL

or

/* Delete all duplicate records by using Common Table Expressions(CTE) */

;WITH CTE
            AS (
    SELECT FirstName,
                LastName,
                Row_number()
                OVER (
                    PARTITION BY FirstName, LastName
            ORDER BY (
            SELECT 1) ) AS Rn
        FROM   dbo.Customer)
       DELETE FROM 
      CTE WHERE EXISTS (SELECT 1 FROM cte t WHERE t.FirstName=cte.FirstName 
AND t.LastName=cte.LastName AND rn>1)