Delete Duplicate Rows in SQL Server From a Table

Estimated study time: 9 minutes. Clean up repeated data safely.

Duplicate rows tend to sneak into a table through repeated imports, missing unique constraints, or buggy insert logic. SQL Server gives you a few reliable ways to identify and remove them while keeping exactly one copy of each row.

Method 1: Using ROW_NUMBER() with a CTE

This is the most common and controllable approach. It ranks duplicate rows within each group and deletes everything except the first.

WITH CTE AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY EmpName, Email
      ORDER BY EmpID
    ) AS RowNum
  FROM Employees
)
DELETE FROM CTE WHERE RowNum > 1;

The PARTITION BY columns define what counts as a "duplicate," and ORDER BY EmpID decides which copy survives (here, the one with the smallest EmpID).

Method 2: Using GROUP BY and MIN/MAX

Useful when the table has a single-column primary key and you simply want to keep the lowest (or highest) ID for each duplicate group.

DELETE FROM Employees
WHERE EmpID NOT IN (
  SELECT MIN(EmpID)
  FROM Employees
  GROUP BY EmpName, Email
);

Method 3: Using a Temporary Table

Safer for very large tables, since you build the de-duplicated result set separately before swapping it in.

SELECT DISTINCT *
INTO Employees_Temp
FROM Employees;

TRUNCATE TABLE Employees;

INSERT INTO Employees
SELECT * FROM Employees_Temp;

DROP TABLE Employees_Temp;

Verifying Duplicates Before Deleting

Always inspect what counts as a duplicate before running a DELETE.

SELECT EmpName, Email, COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY EmpName, Email
HAVING COUNT(*) > 1;

Preventing Future Duplicates

Once the table is clean, add a UNIQUE constraint or index on the columns that should never repeat, so the problem can't reoccur.

ALTER TABLE Employees
ADD CONSTRAINT UQ_Employees_NameEmail UNIQUE (EmpName, Email);