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 : 206,445   
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)
6 JUL
ASP.NET Core with Angular4 Development (Online)

7:00 AM-9:00AM IST(+5:30 GMT)

Know More
5 JUL
NodeJS with Angular4 Development (Online)

08:00 PM-09:30 PM IST(+5.30 GMT)

Know More
1 JUL
ASP.NET MVC with Angular4 Development (Online)

03:30 PM-05:30 PM IST(+5.30 GMT)

Know More
1 JUL
ASP.NET MVC with Angular4 Development (Classroom)

8:00 AM-10:00 AM

Know More
1 JUL
ASP.NET Core with Angular4 Development (Classroom)

3:00PM 5:00PM

Know More
1 JUL
Xamarin Forms : Build Cross-platform Apps (Classroom)

3:00 PM 5:00 PM

Know More
1 JUL
MEAN Stack 2 Development (Classroom)

11:00 AM-12:30 PM

Know More
30 JUN
ASP.NET MVC with Angular4 Development (Online)

07:00 AM-09:00 AM IST(+5.30 GMT)

Know More
29 JUN
Hadoop Development cum Administration (Online)

7:00 AM-9:00AM IST(+5:30 GMT)

Know More
26 JUN
Xamarin Forms : Build Cross-platform Apps (Online)

9:30PM- 11:00PM IST(+5:30GMT)

6 JUN
ASP.NET MVC with Angular4 Development (Online)

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

3 JUN
ASP.NET MVC with Angular4 Development (Classroom)

4:00 PM-5:30 PM Weekend

20 MAY
Angular2 and Angular4 Development (Online)

08:00 AM-10:00 AM IST(+5.30 GMT) Weekend

20 MAY
NodeJS with Angular4 Development (Classroom)

5:30 PM-7:00 PM Weekend

8 MAY
ASP.NET MVC with Angular4 Development (Online)

07:00 AM-09:00 AM IST(+5:30 GMT) M/W/F

2 MAY
ASP.NET Core Development (Online)

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

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

9:30 AM-11:00 AM

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 
+