Remove duplicate records from a table in SQL Server

Suppose we have below Employee table in SQL Server.

1.   CREATE TABLE dbo.Employee

2.  ( 

3.  EmpID int IDENTITY(1,1) NOT NULL, 

4.  Name varchar(55) NULL, 

5.  Salary decimal(10, 2) NULL, 

6.  Designation varchar(20) NULL

7.   ) 

The data in this table is as shown below:

Remove Duplicate Records by using ROW_NUMBER()

1.   WITH TempEmp (Name,duplicateRecCount)

2.  AS

3.  (

4.  SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) 

5.  AS duplicateRecCount

6.  FROM dbo.Employee

7.  )

8.  --Now Delete Duplicate Records

9.  DELETE FROM TempEmp

10.WHERE duplicateRecCount > 1 

1.   --See affected table

Select * from Employee 

For more help about ROW_NUMBER(), please follow the MSDN link.