TSQL - How To Delete All Duplicate Records From a SQL Server Table

Here are couple of ways to delete duplicate records from a SQL Server table.

--Prepare Sample Data
USE TestDB
CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)

GO
INSERT INTO dbo.Customer 

VALUES(1,'Aamir','Shahzad',34)
,(1,'Aamir','Shahzad',34)
,(2,'Raza','M',32)
,(3,'Sukhjeet','Singh',27)
,(4,'Sukhjeet','Singh',28)

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)

Delete all duplicate records by using Group By and Left Join:

DELETE FROM C
   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