Remove duplicate records/data from SQL Server database table

Introduction:

 In this article I will explain how to remove the duplicate/redundant records from the SQL server table.

Some time we have duplicate data in database table and we want to fetch unique data. It can be done by deleting the duplicate records from table. Duplicate data can be inserted by user by mistake. So to delete duplicate record follow the following procedure:

Implementation:  create a table using the script below.

CREATE TABLE [dbo].[EmDetails]

(

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

                [Name] [varchar](100) NULL,

                [Age] [int] NULL,

                [Salary] [decimal](18, 2) NULL,

                [City] [varchar](100) NULL

) 

Insert some data in this table.
It will look like as:

In this table there is some duplicate data. To delete the duplicate or redundant data follow the following process:

Create CTE(Common table expression) to delete duplicate records as:

                WITH EmployeeDetails(EmpName,duplicateRecordCount)

                AS

                (

                                SELECT Name,ROW_NUMBER() OVER(PARTITION BY Name, Salary ORDER BY Name)

                                AS duplicateRecordCount  FROM EmDetails

                )

                DELETE FROM EmployeeDetails  WHERE duplicateRecordCount > 1                

                SELECT * FROM EmDetails

It will delete all the duplicate records from the table as shown in :

But suppose instead of removing all duplicate rows from table, we only want to remove the duplicate entry of a particular employee. For Example:  Arjun,

In that case the use following query:

WITH EmployeeDetails (EmpName,duplicateRecordCount)

                AS

                (

                                SELECT EmpName,ROW_NUMBER() OVER(PARTITION BY Name, Salary ORDER BY Name)

                                AS duplicateRecordCount  FROM EmDetails WHERE EmpName='Arjun'

                )

                DELETE FROM EmployeeDetails WHERE duplicateRecordCount > 1               

                SELECT * FROM EmDetails