Remove Duplicate Records From A Table In SQL Server

Here I Will explain how to remove duplicate records from a table in SQL Server. 


First of all I have created on table with following columns.

CREATE TABLE #Employee  

(   
EmployeId int IDENTITY(1,1) NOT NULL,   
Name varchar(55) NULL,   
Salary decimal(10, 2) NULL,   
Designation varchar(20) NULL  
 )

After that add a same record multiple time.

INSERT INTO #Employee values('Nikhil' ,44000 ,'Web Developer')
INSERT INTO #Employee values('Nikhil' ,44000 ,'Web Developer')
INSERT INTO #Employee values('Nikhil' ,44000 ,'Web Developer')
INSERT INTO #Employee values('Nikhil' ,44000 ,'Web Developer')
INSERT INTO #Employee values('Nikhil' ,44000 ,'Web Developer')
INSERT INTO #Employee values('Nikhil' ,44000 ,'Web Developer')

After that execute a query to remove duplicate records.

WITH TempEmp (Name,duplicateRecCount)  
AS  
(  
   SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name)   
   AS duplicateRecCount  
   FROM dbo.#Employee  
)  
--Delete Duplicate Records  
DELETE FROM TempEmp  
WHERE duplicateRecCount > 1  

Now see all duplicate records are removed from the table.

SELECT * FROM  #Employee