Shailendra Chauhan
29
SepDelete Duplicate Rows in SQL Server From a Table
Print
1 min read
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.
Take our free skill tests to evaluate your skill!
In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.
