How to delete duplicate record from datatable in SQL Server

Introduction

In this article I will explain how to delete duplicate records from a datatable in SQL server.

Query

CREATE TABLE [dbo].[Emp_Info](

      [Emp_Id] [int] NULL,

      [Name] [varchar](50) NULL,

      [Salary] [int] NULL,

      [City] [varchar](50) NULL

) ON [PRIMARY]

This table does not contain any primary key column because of that it contains duplicate records. Insert some record in table  then

Now you will see in above fig table does not contain any primary key column because of that duplicate records exist in table.


Now I want to get duplicate records from datatable.

Query

with TableTemp as

(

select row_number() over(partition by Name,Salary order by Name) as RowNumber,* from Emp_Info

)

select * from Tabletemp

Return data by above query

If you examine above table RowNumber column added in table this column is used to know which record contains duplicate values based on rows with RowNumber greater than 1.

Now if we want to get unique value table from datatable. write below query

Query

with TableTemp as

(

select row_number() over(partition by Name,Salary order by Name) as RowNumber,* from Emp_Info

)

delete from TableTemp where RowNumber > 1

Select * from Emp_Info order by Emp_Id asc

All duplicate records will delete from our table and that would be like this