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:
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
OR
delete from Employee
where
EmpID NOT IN ( select min(EmpID) from Employee
Employees GROUP BY Name,Salary,Designation)
–See affected table
Select * from Employee