Suppose we have the situation to delete some duplicate records in our table. Suppose consider one table

create table #Test
(
EmpID int,
EmpName varchar(50)
)

–Insert the Records into #Test table

insert into #Test values(1,’Daya‘)
insert into #Test values(1,’Daya‘)
insert into #Test values(1,’Daya‘)

Now i have two duplicate records inserted and i want to delete those records. The following query will delete the duplicate records

–Query to Delete Duplicate Records

WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )
DELETE FROM Emp WHERE RNUM > 1