Browse Articles

Delete Duplicate Rows in SQL Server From a Table

31 Aug 2022
Intermediate
340K Views

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.

Learn to Crack Your Technical Interview

Accept cookies & close this