Find Duplicate Column Values (qcoldup.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script identifies duplicate values in any specified table column, showing each duplicate value along with its occurrence count. It’s essential for data quality analysis, constraint validation, and identifying potential primary key candidates. The script groups all rows by the specified column and filters to show only values that appear more than once, providing a clear view of data duplicates.

The Script

rem  qcoldup.sql
rem
col column_value heading 'COLUMN VALUE'
col group_count format 999,999,999 heading 'ROW COUNT'
rem
ttitle 'Duplicate Column Distribution'
rem
break on report
compute sum of group_count on report
rem
select &&column column_value, count(*) group_count
  from &table
 group by &column
having count(*) > 1;
rem
undefine column;

Key Features

• Simple and Flexible: Works with any table and column combination
• Duplicate Detection: Shows only values that appear multiple times
• Occurrence Counting: Displays exact count of each duplicate value
• Statistical Summary: Provides total count of duplicate rows
• Universal Compatibility: Works with any data type column
• Performance Optimized: Uses efficient GROUP BY with HAVING clause

Usage

@qcoldup.sql

Input Parameters:

• table: Table name to analyze (required)
• column: Column name to check for duplicates (required, prompted twice for use in SELECT and GROUP BY)

Required Privileges

SELECT on the specified table

Sample Output

                                        Duplicate Column Distribution

COLUMN VALUE                                                     ROW COUNT
---------------------------------------------------------------- -----------
[email protected]                                                     3
[email protected]                                                   2
[email protected]                                                    4
[email protected]                                                     2
[email protected]                                                 5
                                                                -----------
sum                                                                      16

Key Output Columns

• COLUMN VALUE: The actual duplicate value found in the column
• ROW COUNT: Number of times this value appears in the table

Understanding Duplicate Analysis

Data Quality Implications

Duplicate Detection Importance

-- Duplicates can indicate:
-- Data entry errors
-- Missing unique constraints
-- ETL process issues
-- Business rule violations
-- Data integration problems

Data Integrity Issues

-- Common duplicate scenarios:
-- Email addresses in user tables
-- Product codes in inventory
-- Customer IDs in transactions
-- Reference data inconsistencies

Business Impact Assessment

Operational Impact

-- Duplicate values can cause:
-- Report inaccuracies
-- Customer communication issues
-- Inventory management problems
-- Financial discrepancies
-- Process automation failures

Compliance and Auditing

-- Regulatory considerations:
-- Data accuracy requirements
-- Audit trail integrity
-- Customer data privacy
-- Financial reporting accuracy

Common Use Cases

1. Data Quality Assessment

   • Identify data entry errors and inconsistencies
   • Validate data cleansing processes
   • Assess data migration quality
   • Support data governance initiatives

2. Constraint Validation

   • Verify unique constraint requirements
   • Identify primary key candidates
   • Validate business rules
   • Support database design decisions

3. Data Cleansing Preparation

   • Identify records needing deduplication
   • Plan data cleansing strategies
   • Assess cleansing impact
   • Support data standardization efforts

4. Business Rule Enforcement

   • Validate business logic constraints
   • Identify policy violations
   • Support compliance monitoring
   • Plan process improvements

Advanced Analysis Techniques

Frequency Analysis

-- Analyze duplicate frequency patterns:
-- High occurrence counts = systematic issues
-- Low occurrence counts = random errors
-- Frequency distribution = data quality patterns
-- Outlier identification = specific problem areas

Impact Assessment

-- Assess duplicate impact:
-- Calculate percentage of affected rows
-- Identify most problematic values
-- Estimate cleansing effort required
-- Plan remediation strategies

Root Cause Analysis

-- Investigate duplicate sources:
-- Data entry process issues
-- System integration problems
-- Migration process errors
-- Business process gaps

Usage Examples

Email Address Duplicates

-- Find duplicate email addresses:
Enter value for table: customers
Enter value for column: email_address
Enter value for column: email_address

Product Code Analysis

-- Check for duplicate product codes:
Enter value for table: products
Enter value for column: product_code
Enter value for column: product_code

Customer ID Validation

-- Validate customer ID uniqueness:
Enter value for table: orders
Enter value for column: customer_id
Enter value for column: customer_id

Social Security Number Check

-- Find duplicate SSNs:
Enter value for table: employees
Enter value for column: ssn
Enter value for column: ssn

Data Cleansing Strategies

Duplicate Resolution

Manual Review Process

-- For critical duplicates:
-- Review each duplicate case individually
-- Determine which record is authoritative
-- Merge or delete inappropriate records
-- Update references to consolidated records

Automated Deduplication

-- For systematic duplicates:
-- Develop deduplication rules
-- Implement automated merge logic
-- Create backup before processing
-- Validate results after deduplication

Prevention Strategies

Database Constraints

-- Implement preventive constraints:
-- UNIQUE constraints on columns
-- Composite unique constraints
-- Check constraints for business rules
-- Foreign key constraints for referential integrity

Application Controls

-- Application-level prevention:
-- Input validation routines
-- Duplicate checking before insert
-- User interface controls
-- Business logic enforcement

Performance Considerations

Large Table Analysis

-- For large tables:
-- Consider adding WHERE clauses for date ranges
-- Use partitioned tables for better performance
-- Schedule analysis during off-peak hours
-- Monitor resource utilization

Index Optimization

-- Optimize for duplicate checking:
-- Ensure indexes exist on analyzed columns
-- Consider composite indexes for multi-column analysis
-- Monitor query execution plans
-- Plan index maintenance

Resource Management

-- Manage resource usage:
-- Monitor CPU and memory consumption
-- Consider parallel processing for large datasets
-- Plan disk space for temporary operations
-- Schedule during maintenance windows

Integration with Data Quality Processes

Data Profiling

-- Include in data profiling:
-- Regular duplicate analysis schedules
-- Trend monitoring over time
-- Quality metric tracking
-- Report generation automation

ETL Process Integration

-- ETL process integration:
-- Pre-load duplicate checking
-- Post-load validation
-- Exception handling procedures
-- Quality gate implementation

Continuous Monitoring

-- Ongoing monitoring:
-- Automated duplicate detection
-- Alert generation for new duplicates
-- Quality dashboard integration
-- Trend analysis and reporting

Advanced Duplicate Analysis

Multi-Column Duplicates

-- For multi-column duplicate analysis, modify script:
select column1, column2, count(*) group_count
  from table_name
 group by column1, column2
having count(*) > 1;

Percentage Analysis

-- Calculate duplicate percentage:
WITH duplicate_count AS (
  SELECT COUNT(*) as dup_rows
  FROM (
    SELECT column_name
    FROM table_name
    GROUP BY column_name
    HAVING COUNT(*) > 1
  )
),
total_count AS (
  SELECT COUNT(*) as total_rows
  FROM table_name
)
SELECT
  (dup_rows * 100.0 / total_rows) as duplicate_percentage
FROM duplicate_count, total_count;

Fuzzy Matching

-- For near-duplicate detection:
-- Use SOUNDEX for phonetic matching
-- Implement string similarity functions
-- Consider case-insensitive comparisons
-- Apply data standardization before comparison

Reporting and Documentation

Quality Reports

-- Generate quality reports:
-- Duplicate summary statistics
-- Trend analysis over time
-- Before/after cleansing comparisons
-- Quality improvement metrics

Business Communication

-- Communicate findings:
-- Executive summary of data quality
-- Impact assessment on business processes
-- Recommended remediation actions
-- Timeline for quality improvements

Audit Documentation

-- Maintain audit documentation:
-- Record of duplicate analysis results
-- Actions taken for remediation
-- Validation of cleansing results
-- Process improvement recommendations

Automation and Scheduling

Automated Execution

-- Schedule regular duplicate checks:
-- Daily for critical business data
-- Weekly for reference data
-- Monthly for historical data
-- After major data loads

Alert Generation

-- Implement alerting:
-- Threshold-based duplicate alerts
-- Trend change notifications
-- Quality degradation warnings
-- Process failure notifications

Integration with Monitoring

-- Monitor integration:
-- Database monitoring tools
-- Data quality dashboards
-- Business intelligence reports
-- Operational health checks

Best Practices

Analysis Planning

-- Plan duplicate analysis:
-- Identify critical columns for uniqueness
-- Prioritize by business impact
-- Schedule appropriate frequency
-- Define acceptable quality thresholds

Remediation Strategy

-- Develop remediation approach:
-- Create data backup before changes
-- Implement validation procedures
-- Plan rollback strategies
-- Test in development environment

Process Integration

-- Integrate with business processes:
-- Include in data governance framework
-- Align with business rules
-- Coordinate with stakeholders
-- Document procedures and standards

Related Scripts

• qcolgrp.sql - Column value distribution analysis
• dtable.sql - General table analysis
• dtabcol.sql - Table column details
• dobject.sql - Database object analysis

Troubleshooting

Common Issues

-- Script execution problems:
-- Verify table and column names
-- Check user privileges
-- Ensure column data type compatibility
-- Monitor resource utilization

Performance Problems

-- Performance optimization:
-- Add appropriate indexes
-- Consider partitioning for large tables
-- Use parallel processing if available
-- Schedule during off-peak hours

Data Type Considerations

-- Handle different data types:
-- Date columns may need formatting
-- CLOB/BLOB columns require special handling
-- Number precision considerations
-- Character set compatibility

Summary

This script is essential for:

1. Data Quality Assessment - Identifying and quantifying duplicate values
2. Constraint Validation - Verifying uniqueness requirements before constraint creation
3. Data Cleansing - Planning and executing deduplication processes
4. Business Rule Enforcement - Validating data against business requirements
5. Compliance Monitoring - Supporting regulatory and audit requirements