SQL Server Delete Duplicate Rows

SQL Server Delete Duplicate Rows

Hi Friends in this tutorial we will learn SQL Server Delete Duplicate Rows or you can say how to delete duplicate rows in sql server. Sometime it is required to delete duplicate records from a table. We will delete duplicate rows using ROW_NUMBER() feature of SQL Server.

Earlier we had discussed about How to Connect MySQL Database in Asp.Net using C#, Logon Screen Message to Users in SAP, SAP User Password Reset, MySQL to SQL Server Data Migration Through SSIS and How to Get The Day of The Week in SQL Server.

Create Table Script:

CREATE TABLE Employee
( 
EmpID int IDENTITY(0,1) NOT NULL, 
Name varchar(50) NULL, 
Salary decimal(10, 2) NULL, 
Designation varchar(20) NULL
)
GO

Now insert some data into employee table.

Insert Data Script:

Insert into Employee values ('Mandeep',57000.00,'DBA')
Insert into Employee values ('Sushmit',89000.00,'Consultant')
Insert into Employee values ('Samir',50000.00,'SAP MM')
Insert into Employee values ('Mandeep',57000.00,'DBA')
Insert into Employee values ('Aman',20000.00,'IT Support')
Insert into Employee values ('Sushmit',89000.00,'Consultant')
Insert into Employee values ('Subhash',115000.00,'SAP')
GO

Delete Table Script:

WITH TempEmployee (Name,duplicateCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary, Designation ORDER BY Name) 
AS duplicateCount
FROM dbo.Employee
)
--Now Delete Duplicate Records
DELETE FROM TempEmployee
WHERE duplicateCount > 1
go 

Result: