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
--See affected table Select * from Employee
For more help about ROW_NUMBER(), please follow the MSDN link.
In this article, I expose how can you remove duplicate records from a table in SQL Server. I hope after reading this article you will be able to use these tips. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.