How to Delete Duplicate Records in SQL Server Using ROW_NUMBER()?

Data Integrity: Removing Duplicate Entries in SQL Server

Maintaining a clean and efficient database requires regular removal of duplicate records. This is vital for data integrity and optimal database performance. When identical entries exist (for example, multiple employees with the same name), eliminating redundancies is essential.

SQL Server offers a powerful solution using window functions and DELETE statements. Window functions provide the ability to perform calculations across related rows.

To illustrate, let's consider deleting duplicate entries based on the EmployeeName column. The ROW_NUMBER() function assigns a unique rank to each row within a partition (group of identical EmployeeName values). This allows us to selectively delete duplicates.

Here's the T-SQL query:

DELETE x
FROM (
    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY EmployeeName ORDER BY empId)
    FROM Employee
) x
WHERE RN > 1;

This query functions as follows: A subquery creates a temporary table, applying ROW_NUMBER() partitioned by EmployeeName and ordered by empId. This assigns a unique rank (RN) to each employee name, numbering duplicates sequentially.

The DELETE statement then targets this temporary table (aliased as 'x') and removes all rows where RN is greater than 1. This effectively keeps only the first instance of each EmployeeName, deleting all subsequent duplicates.

Verification Before Execution:

Before executing the DELETE statement, it's crucial to verify its impact. Run this SELECT statement:

SELECT *
FROM (
    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY EmployeeName ORDER BY empId)
    FROM Employee
) x
WHERE RN > 1;

This will preview the records slated for deletion. Once you've confirmed the accuracy, execute the DELETE statement to permanently remove the duplicate records from the Employee table.

The above is the detailed content of How to Delete Duplicate Records in SQL Server Using ROW_NUMBER()?. For more information, please follow other related articles on the PHP Chinese website!