Two customer rows look alike, but one missing apartment number can change the answer. Finding duplicate customers takes candidate queries and a review step before any merge.
Define What Duplicate Customers Mean
A duplicate is a business decision, not a string function result. Two rows can have the same name but represent different people or offices. Two different names can represent one organization after a rename. Start with the attributes and rules the customer owner trusts.
I ask which record should survive and which facts must move with it. Orders, contacts, permissions, and audit history can point at either customer key. If the merge plan ignores those relationships, deleting a duplicate row can break more than the customer list.
Keep a distinction between candidate and confirmed duplicate customers. A query can find candidates. A data steward or a documented rule confirms the relationship. That pause is useful. The database will gladly merge two Smith records without asking whether they know each other.
Normalize Without Destroying the Source
Build comparison columns that trim surrounding spaces, standardize case, and normalize known punctuation. Keep original names for display and audit. Do not remove every word or digit in the hope of making more matches. Overly aggressive normalization increases false positives.
TRIM handles leading and trailing spaces. UPPER can make a simple case-insensitive comparison explicit, though collation also affects equality. If you remove punctuation, do it under a documented rule and test names that contain meaningful punctuation. A normalization version helps explain why older candidate sets differ.
I keep the normalized value in a staging or derived column while evaluating the rule. That lets me inspect both original and comparison text side by side. A clean-looking key that cannot be traced to the original value is hard to trust.
SELECT CustomerId, CustomerName,
UPPER(TRIM(CustomerName)) AS NormalizedName
FROM dbo.Customer
WHERE NULLIF(TRIM(CustomerName), N'') IS NOT NULL;Find Exact Candidate Groups
Start with exact matches on strong identifiers such as tax ID, account number, or verified email when those identifiers are appropriate and permitted. Then try normalized name with address or phone. The grouping key should reflect the business entity you want to deduplicate, not merely the most convenient columns.
Count groups before showing pairs. A group with three records needs a survivor decision across all three, not two independent merges. Save the candidate generation rule and run date so a reviewer knows why rows appeared together.
Exact matching still needs review. A shared office phone can belong to several customers. A placeholder email can be reused. I use exact groups as a high confidence queue, not as permission to delete rows automatically.
WITH n AS
(
SELECT CustomerId,
UPPER(TRIM(CustomerName)) AS NormalizedName,
UPPER(TRIM(PostalCode)) AS NormalizedPostalCode
FROM dbo.Customer
)
SELECT NormalizedName, NormalizedPostalCode,
COUNT_BIG(*) AS candidate_count
FROM n
GROUP BY NormalizedName, NormalizedPostalCode
HAVING COUNT_BIG(*) > 1;
Use SOUNDEX as a Candidate Filter
SOUNDEX maps a word to a phonetic code. DIFFERENCE compares two strings by their SOUNDEX representations and returns a score. These functions can find spelling variants, but their output is coarse and language dependent. Use them to narrow a review queue, not to declare identity.
Avoid comparing every row with every other row. Block candidates by a stronger field, such as postal code or the first letter of a normalized surname. Then apply DIFFERENCE within the block. Add another attribute to the review display so a person can judge the pair.
I have seen fuzzy scores treated like proof. A matching sound is a clue. It does not know whether two customers share a household, a business name, or a typo. Ask what false merge would cost before raising an automatic threshold.
SELECT a.CustomerId AS CustomerIdA,
b.CustomerId AS CustomerIdB,
a.CustomerName AS NameA,
b.CustomerName AS NameB,
DIFFERENCE(a.CustomerName, b.CustomerName) AS SoundScore
FROM dbo.Customer AS a
JOIN dbo.Customer AS b
ON a.CustomerId < b.CustomerId
AND a.PostalCode = b.PostalCode
WHERE DIFFERENCE(a.CustomerName, b.CustomerName) >= 3;Review the Whole Record of Duplicate Customers
Show address history, verified contacts, recent orders, source system IDs, and existing relationships for each candidate. A customer can have a new name or address while still being the same entity. Another pair can share a name and postal code yet remain distinct.
Record the decision as confirmed match, confirmed separate, or needs more evidence. Keep reviewer, time, rule version, and reason. A confirmed separate pair should be suppressed from the next candidate queue unless new evidence appears. Otherwise the same false positive returns every week and wears out trust.
I want the review screen to show why the query made the match. A score without the compared fields is a puzzle. The reviewer should be able to say what evidence supports the decision and what remains uncertain.
Merge Through a Controlled Mapping
Create a mapping from duplicate CustomerId to survivor CustomerId. Validate that a duplicate maps to one survivor and that mapping chains or cycles do not exist. Update child tables in a transaction under a tested plan. Some child rows have unique constraints that need special handling when two customers share related records.
Do not erase the duplicate immediately. Mark it merged and keep a redirect or audit entry so imports using the old source ID can resolve correctly. Preserve source identifiers and a record of moved relationships. A merge without lineage can be impossible to explain later.
Test a reversal path in a nonproduction copy. If a false merge is discovered, can you separate the records and restore their child relationships? I ask that before executing a batch merge. The answer can lead you to store more detail in the merge log.
Monitor New Duplicate Customers Without Auto-Merging
Run candidate queries on new or changed customers, and compare with prior decisions. Track confirmed matches and false positives by rule. A rule with many false positives needs refinement, even if it finds a few real duplicates. The right metric is not simply how many pairs it produces.
Protect sensitive fields used for matching. A candidate table can expose names, addresses, and contact details beyond the normal customer access path. Apply the same permissions and retention policy as the main table. Data quality work does not suspend privacy rules.
The safest process separates discovery, confirmation, and merge. T-SQL is excellent at building a focused candidate list. The business owner is better placed to decide identity when evidence is ambiguous. Keep those responsibilities visible and the merge auditable.
Related reading on this blog: Remove Duplicate Rows Using UNION Operator and Interview Question of the Week #014: How to DELETE Duplicate Rows.
A duplicate candidate is not a duplicate customer, it is a request for evidence.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
