ROW_NUMBER turns duplicate detection into a controlled survivor decision.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
Finding duplicates and deciding which row survives are different problems. GROUP BY/HAVING identifies duplicate business keys. ROW_NUMBER assigns an ordered position to every row within each key group.
Three customer rows may share the same email. The business rule might be to keep the newest verified record and classify the other two as non-survivors.
WITH Ranked AS
(
SELECT CustomerID,
Email,
ModifiedDate,
ROW_NUMBER() OVER
(
PARTITION BY Email
ORDER BY ModifiedDate DESC, CustomerID DESC
) AS rn
FROM dbo.Customer
)
SELECT *
FROM Ranked
ORDER BY Email, rn;ROW_NUMBER is the bridge from 'duplicate exists' to 'this row wins for a documented reason.'
The hardest part of deduplication is rarely the ROW_NUMBER syntax. It is agreeing on what 'duplicate' actually means. A technical duplicate key must reflect the business identity rule.
Two employees can share a last name. Two family members can share a phone number. A dedupe key must distinguish legitimate repetition from true duplication.
ROW_NUMBER() OVER
(
PARTITION BY
EmployeeNumber,
EffectiveDate,
SourceSystem
ORDER BY
LoadTimestamp DESC,
RowID DESC
) AS rnThe PARTITION BY clause is a business rule disguised as SQL syntax.
ORDER BY inside ROW_NUMBER is where the business decides what 'best row' means. Newest is only one possible rule; verified, complete, trusted-source or manually approved records may deserve priority.
If two records have the same ModifiedDate, SQL Server still needs a stable tie-breaker. Add a unique key last so the ranking is deterministic.
ROW_NUMBER() OVER
(
PARTITION BY Email
ORDER BY
IsVerified DESC,
DataQualityScore DESC,
ModifiedDate DESC,
CustomerID DESC
) AS rnDeterministic ranking turns deduplication from a guess into an auditable rule.
A safe dedupe process is inspection-first. Build the ranking, display the full duplicate groups, verify rn = 1, then decide what to do with rn > 1.
A dedupe query that returns exactly the rows you intended to remove is evidence. A DELETE written first is risk.
WITH Ranked AS
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY Email
ORDER BY ModifiedDate DESC, CustomerID DESC
) AS rn
FROM dbo.Customer
)
SELECT *,
CASE WHEN rn = 1
THEN 'SURVIVOR'
ELSE 'DUPLICATE'
END AS DedupeStatus
FROM Ranked
WHERE Email IN
(
SELECT Email
FROM dbo.Customer
GROUP BY Email
HAVING COUNT(*) > 1
)
ORDER BY Email, rn;A preview is not extra work; it is the evidence that makes destructive dedupe defensible.
Once the ranking has been validated, SQL Server can delete non-survivors through an updatable CTE. The syntax is short; the safety process around it matters more than the syntax.
The destructive statement may be only one line — DELETE FROM Ranked WHERE rn > 1 — but production discipline should include counts, transaction control, archive options and post-delete verification.
BEGIN TRANSACTION;
WITH Ranked AS
(
SELECT CustomerID,
ROW_NUMBER() OVER
(
PARTITION BY Email
ORDER BY ModifiedDate DESC, CustomerID DESC
) AS rn
FROM dbo.Customer
)
DELETE FROM Ranked
WHERE rn > 1;
-- Validate counts and survivors here.
-- COMMIT TRANSACTION;
-- or ROLLBACK TRANSACTION;The DELETE should be the final step of a validated dedupe process, not the first experiment.
Not every duplicate should disappear without trace. In regulated, operational or high-value systems, archive the non-survivors with enough metadata to reconstruct what happened.
If someone asks six months later why CustomerID 8124 disappeared, an archive row with batch ID and survivor reference turns a mystery into an answer.
DELETE d
OUTPUT
DELETED.CustomerID,
DELETED.Email,
DELETED.ModifiedDate,
@BatchID,
SYSUTCDATETIME()
INTO dbo.Customer_DedupeArchive
(
CustomerID,
Email,
ModifiedDate,
DedupeBatchID,
ArchivedAtUTC
)
FROM dbo.Customer AS d
JOIN #RowsToDelete AS x
ON x.CustomerID = d.CustomerID;An archive path converts destructive cleanup into an auditable data-quality operation.
ROW_NUMBER often needs rows grouped and ordered by the PARTITION BY and ORDER BY keys. On large tables that can mean significant scans, sorts, memory grants and tempdb work.
If the table has 200 million rows but only yesterday's load can contain new duplicates, filtering the candidate set before ranking can matter more than micro-optimizing syntax.
CREATE INDEX IX_Customer_Dedupe
ON dbo.Customer
(
Email,
IsVerified DESC,
ModifiedDate DESC,
CustomerID DESC
);
-- Validate with:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- + Actual Execution PlanThe best dedupe performance often comes from reducing the rows that need ranking and giving SQL Server useful ordering.
A dedupe script fixes existing data. A production data-quality solution also addresses why duplicates were created: missing uniqueness rules, retry logic, ETL replay, source-system duplication or weak matching rules.
If the same duplicates return tomorrow, ROW_NUMBER did its job but the pipeline did not. Add prevention where the business identity can safely support it.
1. Define the business duplicate key
2. Define the survivor priority
3. Add deterministic tie-breakers
4. Preview full duplicate groups
5. Count survivors and non-survivors
6. Archive / transaction-protect destructive changes
7. Delete only validated rn > 1 rows
8. Recheck duplicate counts after cleanup
9. Fix the upstream cause
10. Add constraints / monitoring where appropriateROW_NUMBER solves the ranking problem; durable data quality also requires prevention, controls and monitoring.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
Detection identifies duplicate groups; ROW_NUMBER ranks rows so a survivor can be chosen.
The business key combination that defines the duplicate group.
They make survivor selection stable and repeatable.
To verify the survivor rule before making destructive changes.
Explicit rules, deterministic ranking, validation, auditability and recurrence prevention.
| Layer | Purpose |
|---|---|
| PARTITION BY | Define the duplicate business key |
| ORDER BY | Express survivor priority and tie-breakers |
| rn = 1 | Chosen survivor |
| rn > 1 | Review / archive / remove candidates |
| Prevention | Fix upstream cause and add controls |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
ROW_NUMBER dedupe is a survivor-selection process, not just a duplicate-finding trick.