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

Remove duplicate records from a table in SQL Server

  Author : Shailendra Chauhan
Posted On : 24 Aug 2012
Total Views : 201,871   
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 (0)
23 MAY
Xamarin Forms : Build Cross-platform Apps (Online)

09:00 PM-11:00 PM IST(+5.30 GMT)

Know More
5 MAY
ASP.NET MVC with Angular4 Development (Online)

06:30 AM-08:30 AM IST(+5.30 GMT)

Know More
29 APR
Xamarin Forms : Build Cross-platform Apps (Classroom)

5:00 PM-6:30 PM

Know More
29 APR
ASP.NET MVC with Angular2 Development (Classroom)

9:30 AM-11:00 AM

Know More
27 APR
ASP.NET Core Development (Online)

07:00 AM - 09:00 AM IST( TUS, THR)

Know More
22 APR
NodeJS Development (Classroom)

11:00 AM-12:30 PM

10 APR
Master Class ASP.NET MVC 5 with Angular2 Plus Angular4 Development (Online)

09:00 PM - 11:00 PM IST (M/W/F)

8 APR
MEAN Stack 2 Development (Classroom)

11:00 AM-12:30 PM

25 MAR
Xamarin Forms : Build Cross-platform Apps (Classroom)

8:00 AM-9:30AM

21 MAR
ASP.NET MVC with Angular2 Development (Online)

09:00 PM - 11:00 PM IST( TUS, THR, SAT)

25 FEB
ASP.NET MVC with Angular2 Development (Classroom)

03:30 PM-05:30 PM

24 FEB
Angular 2 with Type Script (Online)

09:00 PM-11:00 PM IST(Mon,Wed,Fri)

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 
+