Various ways of deleting duplicate records from table in SQL Server

Hi All,

Hope you all are doing good.

In this article, we will know about various ways of removing duplicate records from table in SQL Server.

First we are going to create Employee table and inserts some duplicate data in it as follows.

CREATE TABLE Employee
( 
    [ID] INT identity(1,1), 
    [FirstName] Varchar(100), 
    [LastName] Varchar(100), 
    [Country] Varchar(100), 
)
    
INSERT INTO Employee ([FirstName],[LastName],[Country] )
VALUES('Raj','Gupta','India'),
      ('Raj','Gupta','India'),
      ('Mohan','Kumar','USA'),
      ('James','Barry','UK'),
      ('James','Barry','UK'),
      ('James','Barry','UK')

Delete duplicate rows using Group By and Having clause:
By using below query we can remove duplicate rows in Employee table

DELETE FROM Employee
    WHERE ID NOT IN
    (
        SELECT MIN(ID) AS MaxRecordID
        FROM Employee
        GROUP BY [FirstName], [LastName], [Country]
    );


Delete duplicate rows using CTE:
By using below query we can remove duplicate rows in Employee table
WITH CTE
(
  [firstname], 
  [lastname], 
  [country], 
  duplicatecount
)
AS 
(
   SELECT [firstname], [lastname], [country], 
   ROW_NUMBER() OVER
   (
     PARTITION BY [firstname], [lastname], [country] ORDER BY id
   ) AS DuplicateCount  FROM employee
)
DELETE FROM CTE WHERE DuplicateCount > 1;

Thanks for reading this article!!