Delete Duplicate Rows in SQL Server From a Table

Shailendra Chauhan  Print   1 min read  
24 Aug 2012
08 Mar 2019
Intermediate
274K

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.

Summary

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.

Crack Your Technical Interview

 
Learn in-demand Technologies
+