Comprehensive Guide to Identifying and Removing Duplicate Records in SQL

Introduction

Managing data integrity is a critical aspect of database administration. One common issue that arises is the presence of duplicate records within a table. Duplicate records can lead to inaccurate analyses, redundant storage, and compromised data quality. This guide provides a thorough approach to identifying and removing duplicate records in SQL, focusing on the manpower_setting_monthly table as an example.

Understanding Duplicate Records

Duplicate records are rows in a database table that contain identical data across certain columns. The definition of a duplicate can vary based on which columns are considered essential for uniqueness. For instance, two records might be duplicates if they share the same SN, fab, and year_month values, even if other fields differ.

Methods to Identify Duplicate Records

There are multiple strategies to identify duplicate records in SQL. The most effective methods involve using GROUP BY clauses, window functions like ROW_NUMBER(), and Common Table Expressions (CTEs). Below are detailed explanations and SQL examples for each method.

1. Using GROUP BY and HAVING Clauses

The GROUP BY clause combined with the HAVING clause is a straightforward way to identify duplicates based on specific columns.

a. Identifying Duplicates Based on All Columns

This method checks for duplicates across all columns selected. It's the strictest form of duplication.

SELECT 
      [SN],
      [fab],
      [phase],
      [attribute],
      [stage],
      [unit],
      [adt],
      [shiftDN],
      [idl],
      [year_month],
      [actual_work_hour_sum],
      [actual_mix_fte_hour_sum],
      [actual_mix_fte_hour_sum_with_break],
      [headcount],
      [optimize_manpower],
      [challenge_manpower],
      [lock],
      [lock_user],
      [lm_user],
      [lm_time],
      [lrf_user],
      [lrf_time],
      [unit_id],
      COUNT(*) AS DuplicateCount
  FROM 
      [dbo].[manpower_setting_monthly]
  GROUP BY 
      [SN],
      [fab],
      [phase],
      [attribute],
      [stage],
      [unit],
      [adt],
      [shiftDN],
      [idl],
      [year_month],
      [actual_work_hour_sum],
      [actual_mix_fte_hour_sum],
      [actual_mix_fte_hour_sum_with_break],
      [headcount],
      [optimize_manpower],
      [challenge_manpower],
      [lock],
      [lock_user],
      [lm_user],
      [lm_time],
      [lrf_user],
      [lrf_time],
      [unit_id]
  HAVING 
      COUNT(*) > 1
  ORDER BY 
      DuplicateCount DESC;
  

Explanation:

• GROUP BY: Groups records that have identical values in all specified columns.
• COUNT(*): Counts the number of records in each group.
• HAVING COUNT(*) > 1: Filters groups to only include those with more than one record, indicating duplicates.
• ORDER BY DuplicateCount DESC: Orders the results to show the most duplicated records first.

b. Identifying Duplicates Based on a Subset of Columns

Often, duplication is determined by specific columns rather than all. For example, duplicates might be identified based on SN, fab, year_month, and unit.

SELECT 
      [SN],
      [fab],
      [year_month],
      [unit],
      COUNT(*) AS DuplicateCount
  FROM 
      [dbo].[manpower_setting_monthly]
  GROUP BY 
      [SN],
      [fab],
      [year_month],
      [unit]
  HAVING 
      COUNT(*) > 1
  ORDER BY 
      DuplicateCount DESC;
  

Explanation:

• By grouping only on [SN], [fab], [year_month], and [unit], this query identifies duplicates based on these key columns.
• Other columns may contain different data, which is acceptable as long as the specified columns are identical.

2. Using Common Table Expressions (CTEs) with ROW_NUMBER()

CTEs combined with the ROW_NUMBER() window function offer a flexible method to identify and handle duplicates.

a. Assigning Row Numbers to Identify Duplicates

WITH CTE AS (
      SELECT 
          [SN],
          [fab],
          [phase],
          [attribute],
          [stage],
          [unit],
          [adt],
          [shiftDN],
          [idl],
          [year_month],
          [actual_work_hour_sum],
          [actual_mix_fte_hour_sum],
          [actual_mix_fte_hour_sum_with_break],
          [headcount],
          [optimize_manpower],
          [challenge_manpower],
          [lock],
          [lock_user],
          [lm_user],
          [lm_time],
          [lrf_user],
          [lrf_time],
          [unit_id],
          ROW_NUMBER() OVER (
              PARTITION BY [SN], [fab], [phase], [attribute], [stage], [unit], 
                           [adt], [shiftDN], [idl], [year_month] 
              ORDER BY [SN]
          ) AS RowNum
      FROM 
          [dbo].[manpower_setting_monthly]
  )
  SELECT *
  FROM CTE
  WHERE RowNum > 1;
  

Explanation:

• ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS RowNum: Assigns a unique sequential number to each row within the partition defined by the specified columns.
• WHERE RowNum > 1: Filters out the first occurrence (RowNum = 1) of each duplicate group, leaving only the duplicates.

b. Deleting Duplicate Records Using CTE

WITH CTE AS (
      SELECT 
          [SN],
          [fab],
          [phase],
          [attribute],
          [stage],
          [unit],
          [adt],
          [shiftDN],
          [idl],
          [year_month],
          [actual_work_hour_sum],
          [actual_mix_fte_hour_sum],
          [actual_mix_fte_hour_sum_with_break],
          [headcount],
          [optimize_manpower],
          [challenge_manpower],
          [lock],
          [lock_user],
          [lm_user],
          [lm_time],
          [lrf_user],
          [lrf_time],
          [unit_id],
          ROW_NUMBER() OVER (
              PARTITION BY [SN], [fab], [phase], [attribute], [stage], [unit], 
                           [adt], [shiftDN], [idl], [year_month] 
              ORDER BY [SN]
          ) AS RowNum
      FROM 
          [dbo].[manpower_setting_monthly]
  )
  DELETE FROM CTE
  WHERE RowNum > 1;
  

Explanation:

• This CTE identifies duplicates in the same manner as the previous example.
• The DELETE FROM CTE WHERE RowNum > 1 statement removes all duplicates, retaining only the first occurrence of each unique group.

3. Using the GROUP BY Clause with COUNT(*)

An alternative approach involves using the COUNT(*) function directly within a GROUP BY clause to identify duplicates.

SELECT 
      [SN],
      [fab],
      [phase],
      [attribute],
      [stage],
      [unit],
      [adt],
      [shiftDN],
      [idl],
      [year_month],
      [actual_work_hour_sum],
      [actual_mix_fte_hour_sum],
      [actual_mix_fte_hour_sum_with_break],
      [headcount],
      [optimize_manpower],
      [challenge_manpower],
      [lock],
      [lock_user],
      [lm_user],
          [lm_time],
      [lrf_user],
      [lrf_time],
      [unit_id],
      COUNT(*) AS DuplicateCount
  FROM 
      [dbo].[manpower_setting_monthly]
  GROUP BY 
      [SN],
      [fab],
      [phase],
      [attribute],
      [stage],
      [unit],
      [adt],
      [shiftDN],
      [idl],
      [year_month],
      [actual_work_hour_sum],
      [actual_mix_fte_hour_sum],
      [actual_mix_fte_hour_sum_with_break],
      [headcount],
      [optimize_manpower],
      [challenge_manpower],
      [lock],
      [lock_user],
      [lm_user],
      [lm_time],
      [lrf_user],
      [lrf_time],
      [unit_id]
  HAVING 
      COUNT(*) > 1
  ORDER BY 
      DuplicateCount DESC;
  

Explanation:

• This query groups records based on all specified columns.
• It then counts the number of records in each group.
• The HAVING COUNT(*) > 1 clause filters out groups that have only one record, effectively identifying duplicates.

4. Using Self-Joins to Identify Duplicates

Self-joins can also be utilized to find duplicate records by joining the table with itself based on the columns that define uniqueness.

SELECT 
      a.*
  FROM 
      [dbo].[manpower_setting_monthly] a
  INNER JOIN 
      [dbo].[manpower_setting_monthly] b
      ON a.[SN] = b.[SN]
      AND a.[fab] = b.[fab]
      AND a.[year_month] = b.[year_month]
      AND a.[unit] = b.[unit]
  WHERE 
      a.[unit_id] < b.[unit_id];
  

Explanation:

• The table is joined with itself on the columns that should be unique.
• The WHERE a.[unit_id] < b.[unit_id] condition ensures that only one of the duplicate pairs is selected, preventing full duplication.

Deleting Duplicate Records

After identifying duplicate records, the next step is to remove them to maintain data integrity. Below are methods to delete duplicates while retaining unique records.

1. Deleting Using CTE and ROW_NUMBER()

WITH CTE AS (
      SELECT 
          [SN],
          [fab],
          [phase],
          [attribute],
          [stage],
          [unit],
          [adt],
          [shiftDN],
          [idl],
          [year_month],
          [actual_work_hour_sum],
          [actual_mix_fte_hour_sum],
          [actual_mix_fte_hour_sum_with_break],
          [headcount],
          [optimize_manpower],
          [challenge_manpower],
          [lock],
          [lock_user],
          [lm_user],
          [lm_time],
          [lrf_user],
          [lrf_time],
          [unit_id],
          ROW_NUMBER() OVER (
              PARTITION BY [SN], [fab], [phase], [attribute], [stage], [unit], 
                           [adt], [shiftDN], [idl], [year_month]
              ORDER BY [SN]
          ) AS RowNum
      FROM 
          [dbo].[manpower_setting_monthly]
  )
  DELETE FROM CTE
  WHERE RowNum > 1;
  

Explanation:

• The CTE assigns a row number to each record within its duplicate group.
• The DELETE statement removes all records where RowNum > 1, effectively keeping only the first occurrence.

2. Deleting Using a Subquery with ROW_NUMBER()

DELETE FROM [dbo].[manpower_setting_monthly]
  WHERE [unit_id] IN (
      SELECT [unit_id]
      FROM (
          SELECT 
              [unit_id],
              ROW_NUMBER() OVER (
                  PARTITION BY [SN], [fab], [phase], [attribute], [stage], [unit], 
                               [adt], [shiftDN], [idl], [year_month]
                  ORDER BY [SN]
              ) AS RowNum
          FROM 
              [dbo].[manpower_setting_monthly]
      ) AS SubQuery
      WHERE RowNum > 1
  );
  

Explanation:

• This method uses a subquery to identify duplicate unit_id values.
• Only records with RowNum > 1 are targeted for deletion.

3. Deleting Using Self-Joins

DELETE a
  FROM 
      [dbo].[manpower_setting_monthly] a
  INNER JOIN 
      [dbo].[manpower_setting_monthly] b
      ON a.[SN] = b.[SN]
      AND a.[fab] = b.[fab]
      AND a.[year_month] = b.[year_month]
      AND a.[unit] = b.[unit]
  WHERE 
      a.[unit_id] < b.[unit_id];
  

Explanation:

• The self-join identifies duplicates based on the specified columns.
• The condition a.[unit_id] < b.[unit_id] ensures that only duplicates with a smaller unit_id are deleted.

Best Practices for Handling Duplicates

Effectively managing duplicates involves not just their identification and removal but also implementing practices to prevent their recurrence.

1. Define Clear Criteria for Uniqueness

Before attempting to remove duplicates, clearly define which columns constitute a unique record. This ensures that only truly duplicate records are targeted without affecting legitimate data variations.

2. Use Unique Constraints or Indexes

Implementing unique constraints or indexes on columns that should be unique can prevent future duplicates from being inserted into the table.

ALTER TABLE [dbo].[manpower_setting_monthly]
  ADD CONSTRAINT UQ_Manpower UNIQUE ([SN], [fab], [year_month], [unit]);
  

Explanation:

• This statement adds a unique constraint named UQ_Manpower on the combination of SN, fab, year_month, and unit.
• Any attempt to insert a record that violates this uniqueness will result in an error, thus preventing duplicates.

3. Regular Data Audits

Performing regular checks on the database can help in early detection and correction of duplicate records, maintaining data integrity over time.

4. Implement Application-Level Checks

In addition to database constraints, incorporating checks in the application layer ensures that duplicates are caught before they reach the database.

Practical Example: Removing Duplicates from manpower_setting_monthly

Let's walk through a practical example of identifying and removing duplicate records from the manpower_setting_monthly table based on specific columns.

Step 1: Identify Duplicates

SELECT 
      [SN],
      [fab],
      [year_month],
      [unit],
      COUNT(*) AS DuplicateCount
  FROM 
      [dbo].[manpower_setting_monthly]
  GROUP BY 
      [SN],
      [fab],
      [year_month],
      [unit]
  HAVING 
      COUNT(*) > 1
  ORDER BY 
      DuplicateCount DESC;
  

This query identifies records where the combination of SN, fab, year_month, and unit appears more than once.

Step 2: Review Duplicate Records

Before deletion, it's prudent to review the duplicates to ensure that the correct records are targeted.

WITH DuplicateCTE AS (
      SELECT 
          [SN],
          [fab],
          [year_month],
          [unit],
          [unit_id],
          ROW_NUMBER() OVER (
              PARTITION BY [SN], [fab], [year_month], [unit] 
              ORDER BY [unit_id]
          ) AS RowNum
      FROM 
          [dbo].[manpower_setting_monthly]
  )
  SELECT *
  FROM DuplicateCTE
  WHERE RowNum > 1;
  

This query lists all duplicates, excluding the first occurrence based on unit_id.

Step 3: Delete Duplicate Records

WITH DuplicateCTE AS (
      SELECT 
          [unit_id],
          ROW_NUMBER() OVER (
              PARTITION BY [SN], [fab], [year_month], [unit] 
              ORDER BY [unit_id]
          ) AS RowNum
      FROM 
          [dbo].[manpower_setting_monthly]
  )
  DELETE FROM DuplicateCTE
  WHERE RowNum > 1;
  

This statement removes all duplicate records, keeping only the first occurrence of each unique group.

Preventing Future Duplicates

While removing existing duplicates is essential, preventing future duplicates ensures long-term data integrity.

1. Implement Unique Constraints

ALTER TABLE [dbo].[manpower_setting_monthly]
  ADD CONSTRAINT UQ_Manpower UNIQUE ([SN], [fab], [year_month], [unit]);
  

This constraint prevents the insertion of any new records that would duplicate existing combinations of SN, fab, year_month, and unit.

2. Use Indexes Wisely

Creating indexes on columns frequently used to identify duplicates can optimize query performance.

CREATE INDEX IDX_Manpower_Uniqueness 
  ON [dbo].[manpower_setting_monthly] ([SN], [fab], [year_month], [unit]);
  

Note: While indexes improve query performance, they can slow down write operations. Balance indexing based on read/write patterns.

3. Incorporate Data Validation in Applications

Ensure that applications interacting with the database include validation logic to prevent duplicate entries.

Conclusion

Duplicate records pose significant challenges to data integrity and system performance. By implementing the methods outlined in this guide—such as using GROUP BY with HAVING, utilizing CTEs with ROW_NUMBER(), and applying unique constraints—you can effectively identify, remove, and prevent duplicate records in your SQL databases. Regular audits and thoughtful database design further ensure that your data remains accurate and reliable.

Further Reading and Resources

docs.microsoft.com

Microsoft SQL Server GROUP BY Documentation

docs.microsoft.com

Microsoft SQL Server ROW_NUMBER() Function