Please enable Javascript to correctly display the contents on Dot Net Tricks!

Remove duplicate records from a table in SQL Server

 Print 
  Author : Shailendra Chauhan
Posted On : 24 Aug 2012
Total Views : 209,992   
Updated On : 24 Sep 2016
 

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.

Summary

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.



Free Interview Books
 
COMMENTS
19 AUG
MEAN Stack 2 (Online)
5:30 Pm - 7:00 PM IST (+5:30 GMT)
19 AUG
ASP.NET MVC with Angular4 (Online)
10:30 Am to 12:30 PM IST (+ 5:30 GMT)
19 AUG
NodeJS with Angular4 (Classroom)
05:30 PM to 07:00 PM IST (+5:30 GMT)
12 AUG
Angular2 and Angular4 (Online)
08:00 AM - 10:00 AM IST(+5.30 GMT)
12 AUG
ASP.NET MVC with Angular4 (Classroom)
09:30 AM - 11:00 AM IST (+5:30GMT)
4 AUG
NodeJS with Angular4 (Online)
08:00 PM - 09:30 PM IST(+5.30 GMT)
26 JUL
ASP.NET MVC with Angular4 (Online)
07:00 AM - 09:00 AM IST(+5.30 GMT)
22 JUL
MEAN Stack 2 (Classroom)
04:00 PM - 05:30 PM IST(+5:30GMT)
20 JUL
Hadoop cum Administration (Online)
07:00 AM - 09:00 AM IST(+5:30 GMT)
15 JUL
ASP.NET MVC with Angular4 (Online)
03:30 PM - 05:30 PM IST(+5.30 GMT)
1 JUL
ASP.NET MVC with Angular4 (Classroom)
08:00 AM - 09:30 AM IST
1 JUL
MEAN Stack 2 (Classroom)
11:00 AM - 12:30 PM IST(+5:30 GMT)
30 JUN
Xamarin Forms : Build Cross-platform Apps (Online)
09:30 PM - 11:00PM IST(+5:30GMT)
6 JUN
ASP.NET MVC with Angular4 (Online)
09:00 PM-11:00 PM IST(+5.30 GMT)
LIKE US ON FACEBOOK
 
+