Remove duplicate records from a table in SQL Server

Create Employee table

create table tbl_employee(Id int identity(100,1) primary key,Name varchar(200),Sal decimal,address varchar(200))

insert data

insert into tbl_employee(Name,Sal,Address)values('adi',20000,'bangalore')

insert into  tbl_employee values('pavan',30000,'bangalore')

insert into tbl_employee values('Ekanath',40000,'bangalore')

insert into tbl_employee values('ravi',13000,'bangalore')

insert into tbl_employee values('sinu',50000,'bangalore')

insert into tbl_employee values('Ekanath',40000,'bangalore')

insert into tbl_employee values('ravi',13000,'bangalore')

insert into tbl_employee values('sinu',50000,'bangalore')

select  data from table

select * from tbl_employee

Output

Remove Duplicate Records

WITH TempEmp (Name,duplicateRecCount)

AS

(

SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Sal ORDER BY Name)

AS duplicateRecCount

FROM dbo.tbl_employee

)

--Now Delete Duplicate Records

DELETE FROM TempEmp

WHERE duplicateRecCount > 1

Output

(3 row(s) affected)

select  data from table

select * from tbl_employee

Output