🧹 How to Delete Duplicate Rows in SQL Server (Step-by-Step for Beginners)

This simple guide shows you how to remove duplicate rows from a SQL Server table using a CTE (Common Table Expression).

🎯 Step 1: Understand your table

Imagine you have a table named Employees that looks like this:

Here, John Doe and Jane Smith each appear twice. We want to keep only one copy of each.

⚙️ Step 2: Find duplicates

We’ll use a helper query called a CTE to find duplicate rows:

WITH CTE AS (
    SELECT 
        EmployeeID,
        ROW_NUMBER() OVER (
            PARTITION BY FirstName, LastName, Email 
            ORDER BY EmployeeID
        ) AS rn
    FROM Employees
)
SELECT * FROM CTE;

This does the following:

• Groups rows that have the same FirstName, LastName, and Email.
• Gives each one a number (rn = row number).
• The first one gets rn = 1 (we’ll keep it), and others get rn = 2, 3, etc. (we’ll delete them).

🧹 Step 3: Delete the duplicates

WITH CTE AS (
    SELECT 
        EmployeeID,
        ROW_NUMBER() OVER (
            PARTITION BY FirstName, LastName, Email 
            ORDER BY EmployeeID
        ) AS rn
    FROM Employees
)
DELETE FROM CTE WHERE rn > 1;

This deletes all rows where rn > 1, meaning all the duplicate copies.

✅ Step 4: Check the result

SELECT * FROM Employees;

You should now see only one of each record: