Delete Duplicate Records From A Table In SQL Server

In this blog, I would like to share a simple way to delete the duplicate record from a table, using ROW_NUMBER ()

Step 1

Table structure 

CREATE TABLE #Employee (

EmpID INT IDENTITY(1001, 1)

, EmpName VARCHAR(200)

, EmpSalary INT

)

Step 2

Query for inserting record into an Employee table 

INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)
INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)
INSERT# Employee(EmpName, EmpSalary) Values('Vijay P', 100000)
INSERT# Employee(EmpName, EmpSalary) Values('Praveen P', 90000)
INSERT# Employee(EmpName, EmpSalary) Values('Praveen P', 90000)
INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)
INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)
INSERT# Employee(EmpName, EmpSalary) Values('Ranjith R', 80000)
INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)
INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)
INSERT# Employee(EmpName, EmpSalary) Values('Charan B', 50000)
INSERT# Employee(EmpName, EmpSalary) Values('Venkata T', 50000)

Step 3

#Employee table contains duplicated records given below.

SELECT * FROM #Employee

   

Step 4

 

Delete the duplicate record, using ROW_NUMBER () 

SELECT EmpID, EmpName, EmpSalary, ROW_NUMBER() OVER(PARTITION BY EmpName, EmpSalary ORDER BY EmpID) AS RowNumber INTO# EmployeeFinal
FROM# Employee
DELETE
FROM# Employee
WHERE EmpID IN(SELECT EmpID FROM# EmployeeFinal WHERE RowNumber > 1)

Step 5 

After the deletion of the duplicate rows from the #Employee table, it displays, as shown below.