SQL Delete Duplicate Rows from a SQL Table in SQL Server

There are scenarios when you need to DELETE Duplicate Rows in SQL Server Table or REMOVE Duplicate Rows in SQL Table. Deleting Duplicate Records from an SQL Server table can sometimes become tedious, especially when you don’t have a Primary Key defined for the source table.

This article explains two different ways to SQL Delete Duplicate Rows from a SQL Table. The first method is to SQL Delete Duplicate Rows Using Group By and Having Clause. The second method is to take advantage of the Common Table Expression (CTE) feature of SQL Server 2005 to delete duplicate records from the table.

This article mentions two different methods to FIND and REMOVE Duplicates in SQL Table.

Learn How to Delete Duplicate Rows in SQL Server? | How to Remove Duplicates in SQL

The first method would be to “Remove Duplicates in SQL using Group By and Having Clause,” & the second method would be “SQL Delete Duplicate Rows in SQL Server using Common Table Expressions (CTE).”

First Method: SQL Delete Duplicate Rows Using Group By and Having Clause

Let’s start to find and remove duplicate rows Using Group By and Having Clause in SQL Server.

/* SQL Delete Duplicate Rows Using Group By and Having Clause | Remove Duplicates SQL */

USE TempDB
GO

/* Drop EmployeeDetails Table if already exists */
IF OBJECT_ID (N'dbo.EmployeeDetails', N'U') IS NOT NULL
    DROP TABLE dbo.EmployeeDetails;
GO 

/* Create EmployeeDetails Details Table*/
CREATE TABLE EmployeeDetails
(
EmpID       INT IDENTITY (1,1),
Name        VARCHAR(25),
Age         INT
)
GO

/* Insert Script is using Row Value Constructor Feature of SQL Server 2008*/
INSERT INTO EmployeeDetails
VALUES
       ('Lilly', 25)
      ,('Lucy',  25)
      ,('Lilly', 25)
      ,('Mary',  26)
      ,('Mariam',26)
      ,('Mary',  26)
      ,('Lisa',  27)
GO

/* Employee Details with Duplicates */
SELECT * FROM EmployeeDetails
GO

/* Find Duplicate Rows in SQL Server | How to Find & Remove Duplicate in SQL Query */
SELECT  
	 Name, 
     Age, 
     COUNT(*) AS [CountDuplicates]
FROM EmployeeDetails
GROUP BY 
      Name, 
      Age
HAVING COUNT(*)  > 1

/* Delete Duplicate Rows in SQL | Remove Duplicate SQL Query using MAX Function | Remove Duplicates SQL */
DELETE FROM EmployeeDetails
    WHERE EmpID NOT IN
    (
        SELECT MAX(EmpID) AS MaxRecordID
        FROM EmployeeDetails
        GROUP BY Name, 
                 Age
    );

/* Remove Duplicate in SQL | Remove Duplicate SQL Query using MAX Function */

SELECT  
     Name, 
     Age, 
     COUNT(*) AS [CountDuplicates]
FROM EmployeeDetails
GROUP BY
      Name, 
      Age

/* Employee Details without Duplicates */
SELECT * FROM EmployeeDetails
GO

Output: How to Find & Delete Duplicate Rows in SQL | Find and Delete Duplicate Rows SQL | SQL Remove Duplicates

Learn How to Delete Duplicate Rows from a Table in SQL Server Using Common Table Expression (CTE) Feature of SQL Server

Deleting Duplicate Records Using Common Table Expression (CTE) Feature of SQL Server

What is Common Table Expression (CTE)?

A common table expression (CTE) can be considered as a temporary result set which is defined within the execution scope of a single INSERT, UPDATE, SELECT OR DELETE statements. It is much similar to a derived table. However, a CTE can be self-referenced multiple times within the scope of the same query.

Let us go through the below mentioned example to remove duplicates from a table which doesn’t have a Primary Key defined.

Second Method: SQL Delete Duplicate Rows using Common Table Expressions (CTE)

It is the simplest method to remove duplicates in SQL Server from Database Table.

I always recommend taking a full production database backup and restoring the same in the QA/Test environment to perform and verify the delete duplicate rows activity on the QA/Test environment.

Once you are satisfied with the script, only then go ahead with SQL Remove Duplicates activity in the Production Environment. However, before proceeding with Data Deleted Operations in a Production Environment, take Full Database Backup followed by Log Backups.

ALWAYS TAKE FULL DATABASE BACKUP Before performing any data delete operations to roll back if the activity fails.

/* SQL Delete Duplicate Rows using Common Table Expressions (CTE) | SQL Remove Duplicates */
/* Sample Script to Deleting Duplicate Records Using CTE */
USE TempDB
GO

/* Drop EmployeeDetails Table if already exists */
IF OBJECT_ID (N'dbo.EmployeeDetails', N'U') IS NOT NULL
    DROP TABLE dbo.EmployeeDetails;
GO 

/* Create EmployeeDetails Details Table*/
CREATE TABLE EmployeeDetails
(
EmpID       INT IDENTITY (1,1),
Name        VARCHAR(25),
Age         INT
)
GO

/* Insert Script is using Row Value Constructor Feature of SQL Server 2008*/

INSERT INTO EmployeeDetails
VALUES
       ('Lilly', 25)
      ,('Lucy',  25)
      ,('Lilly', 25)
      ,('Mary',  26)
      ,('Mariam',26)
      ,('Mary',  26)
      ,('Lisa',  27)
GO

/* Using Common Table Expression Feature to Delete Duplicate Records | Remove Duplicates SQL | Delete Duplicate Rows SQL Server */

WITH SampleCTE
AS
(
      SELECT ROW_NUMBER () OVER (PARTITION BY NAME, AGE ORDER BY EmpID) AS RNUM
      FROM EmployeeDetails
)

DELETE FROM SampleCTE WHERE RNUM > 1
GO

/* Employee Details without Duplicates */
SELECT * FROM EmployeeDetails
GO

Output: How to Remove Duplicates in SQL | SQL Remove Duplicates

Conclusion

In this article you have seen how easily you can delete duplicate rows from a table using the Common Table Expression (CTE) method and How to Delete Duplicate Rows in SQL using Group By and Having Clause. I hope you will find how to remove duplicates in sql article very helpful.

SQL Delete Duplicate Rows from a SQL Table in SQL Server