Sometimes we required to delete duplicate rows from a table although the table has a UniqueID Column with identity. In this article, I would like to share the best way to delete duplicate rows in SQL server from a table.
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 Rows 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 Rows 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 rows 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.