Remove Duplicate Records From SQL Server Table Using Common Table Expression

This article shows how to remove duplicate records from a SQL Server table.

For the example of this article I have the following SQL Server table in my database.


                                                Image 1.

The following is the script of my table:

CREATE TABLE [dbo].[Employee](
   [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
   [Name] [varchar](50) NULL,
   [Email] [varchar](500) NULL,
   [Designation] [varchar](50) NULL,
   [City] [varchar](50) NULL,
   [State] [varchar](50) NULL,
   [Country] [varchar](50) NULL
) ON [PRIMARY]

GO

The following are some records in my table:


                                                               Image 2.

Here you can see I have some duplicate records in this table.

Now the challenge is, how to remove these duplicate records using a SQL statement.

So use the following statements: 

WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS ROWNO,
                    NAME,Emp_ID, Designation, Email FROM Employee
)
SELECT * FROM CTE WHERE ROWNO > 1

The preceding statement returns all the duplicate records with a number of occurrences:


                                                                  Image 3.

Now use the following statements to remove duplicate records.

WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS ROWNO,
                    NAME,Emp_ID, Designation, Email FROM Employee
)
DELETE FROM CTE WHERE ROWNO > 1

                                                                     Image 4.

Now select records from your table.


                                                                  Image 5.