How to delete duplicate data from physical table in sql server

We'll learn here How to delete duplicate data from physical database table.

Step1: let's create the table and insert the data into table.

CREATE TABLE [dbo].[Employee](

[Id] [int],

[Name] [nvarchar](50) NULL,

[Gender] [nvarchar](10) NULL,

[Salary] [int] NULL,

)

GO

Step2: Insert multiple record into employee table.

INSERT INTO Employee(Id,Name,Gender,Salary)

VALUES(1,'Amit','Male',5000),

(2,'Fernando','Male',20000),

(2,'Fernando','Male',20000),

(3,'Rahul','Male',10000),

(1,'Amit','Male',5000),

(4,'Atom','Male',7000),

(5,'SpTan','Female',5000)

Step3: check the data prior to delete operation with the help of below query.

Select * from Employee;

Output:

Step4: Let's delete data from table using CTE table.

WITH DeleteDuplicateRecord AS (

    SELECT 

        ID,  NAME, GENDER, SALARY, ROW_NUMBER()OVER

        (PARTITION BY ID, NAME,GENDER,SALARY ORDER BY ID,NAME,GENDER,SALARY

        ) DuplicateRecord

     FROM  Employee)

DELETE FROM DeleteDuplicateRecord

WHERE DuplicateRecord > 1

Step5: check data after execute the delete operation.

Select * from Employee