Delete duplicate rows from table in sql server 2008R2.

Sometime we want to delete duplicate row/rows from table but column should have UniqueId column in table.

Deleting a duplicate row from table, I will describe a various way which is given below :-

Type1:-

Suppose we have a employee table in MS SQL Server

CREATE TABLE [dbo].[Employee](

     [Emp_ID] [int] IDENTITY(1,1) NOT NULL,

     [Emp_name] [varchar](100) NULL,

     [Emp_Sal] [decimal](10, 2) NULL

)

Data In the table show as below :- 

 

Removing duplicate records by ROW_Number() and CTE…

with tempTable(name,duplicatecount)

as

(

Select emp_name,ROW_NUMBER() over(PARTITION BY emp_name ,emp_sal order by emp_name) as duplicatecount

from Employee

)

Delete from tempTable

 where duplicatecount >1

After executing above query. See aftected table.

Select * from Employee

 

Type2:-

Note: - If table do not have Unique ID column then insert one Unique ID column in the required table, otherwise we can’t able to delete duplicate records.

Delete from dbo.Employee where Emp_ID not in (Select MIN(emp_id) from Employee group by Emp_name,Emp_Sal)

The above query is very easy to delete duplicate rows from table.