Remove Duplicate Records from a Table in SQL Server

Remove duplicate records from a table in SQL Server

Sometimes we required to remove duplicate records from a table although table has a UniqueID Column with identity. In this article, I would like to share a best way to delete duplicate records from a table in SQL Server.

Suppose we have below Employee table in SQL Server.

CREATE TABLE dbo.Employee

(

EmpID int IDENTITY(1,1) NOT NULL,

Name varchar(55) NULL,

Salary decimal(10, 2) NULL,

Designation varchar(20) NULL

)

 
The data in this table is as shown below:

Image-1.jpg

Remove Duplicate Records by using ROW_NUMBER()

WITH TempEmp (Name,duplicateRecCount)

AS

(

SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name)

AS duplicateRecCount

FROM dbo.Employee

)

--Now Delete Duplicate Records

DELETE FROM TempEmp

WHERE duplicateRecCount > 1

--See affected table

Select * from Employee


Image-2.jpg