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.

Data Base
Image 1.

The following is the script of my table:

  1. CREATE TABLE [dbo].[Employee](
  2. [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [varchar](50) NULL,
  4. [Email] [varchar](500) NULL,
  5. [Designation] [varchar](50) NULL,
  6. [City] [varchar](50) NULL,
  7. [State] [varchar](50) NULL,
  8. [Country] [varchar](50) NULL
  9. ) ON [PRIMARY]
  11. GO

The following are some records in my table:

records
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:

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

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

duplicate records
Image 3.

Now use the following statements to remove duplicate records.

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

remove duplicate records
Image 4.

Now select records from your table.

select records from your table
Image 5.