Delete Duplicate Rows in SQL Server

If you have encountered duplicate values in your table, here is a tutorial on how to delete duplicate rows in SQL Server. Duplicate values lead to insufficient database storage.

I’ll explain the three methods to delete duplicate rows from a table in this SQL Server tutorial.

Table of Contents

Method 1: Find Duplicate Rows in the Table using Row_Number

Before deleting duplicate rows in the table, use the query below to check how many duplicate rows there are. Here, the table name is CustomerDetail.

Select Row_number () over (partition by FirstName Order By FirstName) 
 CustomerId, FirstName, LastName, Email, Phone, Address, City, State, ZipCode
from CustomerDetail

Now, you can see how many duplicate records this table has.

If I want to show the duplicate value count in a separate column, I can use sno in the query to get it in an individual serial number column.

Delete Duplicate Rows in the SQL Server using CTE

Now, we will see how to delete duplicate records using Common Table Expression (CTE) in SQL Server using the query below.

With CTE_CustomerDetail
As

(Select Row_number () over (partition by FirstName Order By FirstName) 
 sno, CustomerID, FirstName, LastName, Email, Phone, Address, City, State, ZipCode
from CustomerDetail) 
Delete from CTE_CustomerDetail where sno > 1;

Now, the duplicate value has been deleted from the SQL Table.

Method 2: Find Duplicate Rows in the Table using COUNT

Let’s consider another table. Here, we use the GROUP BY and COUNT clause to identify the duplicate rows.

Use the query below to check how many duplicate rows there will be.

SELECT FirstName, LastName,  Country, 
COUNT(*) AS CNT  
 FROM EmployeeInformation
 GROUP BY FirstName,  LastName,  Country
 HAVING COUNT(*) > 1;

Delete Duplicate Rows in the SQL Server using GROUP BY and Having clause

Now, we will delete the rows in the SQL Server table using GROUP BY and HAVING Clause. Look at the query below to learn more.

DELETE FROM EmployeeInformation
    WHERE ID NOT IN
    (
        SELECT MAX(ID) AS MaxRecordID
        FROM EmployeeInformation
        GROUP BY FirstName,  LastName,  Country
         );

Once deleted, we can see the output with actual values instead of duplicate values.

Method 3: Find Duplicate Rows in SQL by the RANK function

We can delete duplicate rows using the RANK function. Use the query below to see the number of duplicate rows in the table.

SELECT E.ID, 
    E.Firstname, 
    E.Lastname, 
    E.Country, 
    T.rank
FROM EmployeeInformation E
  INNER JOIN
(
 SELECT *, 
        RANK() OVER(PARTITION BY Firstname, 
                                 Lastname, 
                                 Country
        ORDER BY id) rank
 FROM EmployeeInformation			
) T ON E.ID = t.ID;

Delete Duplicate Rows by using the RANK Function

Once you have seen the duplicate rows, delete them using the query below.

DELETE E
    FROM EmployeeInformation E
         INNER JOIN
    (
  SELECT *, RANK() OVER(PARTITION BY firstname, lastname,  country
  ORDER BY id) rank
  FROM EmployeeInformation ) 
 T ON E.ID = t.ID
  WHERE rank > 1;