Names That Differ Only by Case: Finding Them With COLLATE

Names that differ only by case are two values in a case-sensitive column and one value in a case-insensitive column. Move data from one world to the other and the rows can collide. Find those pairs before the migration, not during it.

Why this bites during a migration

Picture a customer table that lives in a case-sensitive database. Someone is moving it to a server that is case-insensitive, with a unique index on Name. The load runs for a while and then stops with a duplicate key error. Now you are fixing data at midnight with half a table loaded.

The fix is a report you run first. Let me build a tiny sample. The names live in a temp table with a case-sensitive collation.

Find the pairs under the target rule

The join compares names using the case-insensitive collation you plan to use. It then excludes pairs that are identical byte for byte. The condition a.Id < b.Id reports each pair once.

DROP TABLE IF EXISTS #Names;
CREATE TABLE #Names (Id int PRIMARY KEY, Name nvarchar(100) COLLATE Latin1_General_100_CS_AS);
INSERT #Names VALUES (1, N'Smith'), (2, N'SMITH'), (3, N'Jones'), (4, N'Jones ');

SELECT a.Id AS FirstId, a.Name AS FirstName, b.Id AS SecondId, b.Name AS SecondName
FROM #Names AS a JOIN #Names AS b
 ON a.Id < b.Id
 AND a.Name COLLATE Latin1_General_100_CI_AS = b.Name COLLATE Latin1_General_100_CI_AS
 AND a.Name COLLATE Latin1_General_BIN2 <> b.Name COLLATE Latin1_General_BIN2
ORDER BY a.Id, b.Id;

The report finds Smith and SMITH. It does not list Jones and Jones with a trailing space. Is that a bug? No, and the next section shows why.

The collision the report cannot see

SQL Server ignores trailing spaces when it compares text, even under a binary collation. LEN ignores them too. So row 3 and row 4 both report 5 characters. Only DATALENGTH and the stored bytes show the truth: 10 bytes against 12.

SELECT Id, N'[' + Name + N']' AS VisibleName,
       LEN(Name) AS CharactersWithoutTrailingSpaces,
       DATALENGTH(Name) AS Bytes, CONVERT(varbinary(200), Name) AS StoredBytes
FROM #Names ORDER BY Id;

The picture shows both results together. To see every colliding group, group by the target collation. The second query shows that Jones and Jones with a space already match under today’s case-sensitive rule.

SELECT MIN(Id) AS FirstId, MAX(Id) AS LastId, COUNT(*) AS RowsInGroup
FROM #Names
GROUP BY Name COLLATE Latin1_General_100_CI_AS
HAVING COUNT(*) > 1
ORDER BY MIN(Id);

SELECT COUNT(*) AS PairsAlreadyEqualToday
FROM #Names AS a JOIN #Names AS b ON a.Id < b.Id AND a.Name = b.Name;

Two groups come back: rows 1 and 2, and rows 3 and 4. Only one pair is equal today. The other pair is new, and it is the one that will hurt.

See the load fail

Here is the midnight failure in miniature. The target table is case-insensitive with a unique constraint. The insert stops with error 2627 on SMITH, and the second query confirms that no rows were loaded.

DROP TABLE IF EXISTS #NamesNew;
CREATE TABLE #NamesNew (Name nvarchar(100) COLLATE Latin1_General_100_CI_AS NOT NULL,
                        CONSTRAINT UQ_NamesNew UNIQUE (Name));

INSERT #NamesNew (Name) SELECT Name FROM #Names;
GO
SELECT COUNT(*) AS rows_loaded FROM #NamesNew;

Accents are a separate question

Case is one setting. Accent is another. Under an accent-sensitive collation, Jose and José are different. Under an accent-insensitive one, they are equal. Test the exact collation you will use, not the one you assume.

SELECT CASE WHEN N'Jose' = N'Jos' + NCHAR(233) COLLATE Latin1_General_100_CI_AS THEN 'equal' ELSE 'different' END AS AccentSensitive,
       CASE WHEN N'Jose' = N'Jos' + NCHAR(233) COLLATE Latin1_General_100_CI_AI THEN 'equal' ELSE 'different' END AS AccentInsensitive;

Decide who survives

Uppercasing every name does not settle anything. First decide whether two rows are one person or two. If they are one, pick a survivor and move the references. This query maps each row to the lowest Id in its group. If names are not real identities, choose a better key.

WITH Ranked AS (
    SELECT Id, Name,
           MIN(Id) OVER (PARTITION BY Name COLLATE Latin1_General_100_CI_AS) AS SurvivorId
    FROM #Names)
SELECT Id, SurvivorId, N'[' + Name + N']' AS VisibleName
FROM Ranked
ORDER BY Id;

DROP TABLE IF EXISTS #NamesNew;
DROP TABLE IF EXISTS #Names;

Rows 1 and 2 map to 1. Rows 3 and 4 map to 3. Keep the original spelling somewhere if aliases matter to your users.

Run the pair report first, and the migration will have fewer surprises.

A collation change is not a spelling fix, it is a new definition of equal.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.