Deleting duplicates in SQL Server table

Delete duplicate rows in SQL Table:

There are many situations where we need to delete duplicate rows in SQL tables. Infact, there are many ways to do it but one of the finest and simplest way is below.

I am providing the below example using temp tables. Replace the temp table with the tablename where you want to delete duplicate rows. In the below example I am using ROW_NUMBER() and Partition By keywords.

The beauty of the below process is this will remove duplicate with a single query.


If Exists(Select * From tempdb.Information_Schema.Tables Where Table_Name Like '#DuplicateRows%')
Drop Table #DuplicateRows

Create Table #DuplicateRows ([Id] int, [Name] varchar(50), [Age] int, [Sex] bit default 1)
Go

Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(2,'Lisa',24,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(3,'Mirsa',23,0)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(4,'John',26,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(5,'Abraham',28,default)
Insert Into #DuplicateRows ([Id] , [Name] , [Age] , [Sex] ) Values(6,'Lincoln',30,default)

Delete T From
(Select Row_Number() Over(Partition By [ID],[Name],[Age],[Sex] order By [ID]) As RowNumber,* From #DuplicateRows)T
Where T.RowNumber > 1

Select * From #DuplicateRows