Width Sensitive and Width Insensitive Collation: An Example

A width sensitive collation treats a half-width character and its full-width form as different characters. I had a great time writing SQL Server: Effect of Case Sensitive Collation on Resultset. It brought many questions about collation. One of them I found interesting enough to share here. It was about width sensitive collation and what it means.

Question: What is a width sensitive collation? Can you explain it with an example?

I find this question interesting, because I see little awareness of collation, and even less of width sensitive collation. Let me explain it with a simple example.

What Width Sensitive Means

Take a single-byte (half-width) character, and the same character as a double-byte (full-width) character. If they are not equal when compared, the collation is width sensitive. If that definition isn’t clear, an example will help.

The Example

We will use the most famous equation in the world, E=mc². I am a big fan of Albert Einstein, and there is no better example than Einstein’s own. In 1921, Einstein received the Nobel Prize in Physics for the explanation of the photoelectric effect. Many still believe it was for relativity. That belief is incorrect.

The example has one table with two columns. One column has a width insensitive collation, and the other has a width sensitive collation. We insert exactly the same data in both columns and retrieve it with the LIKE operator. In the WHERE condition, we use E=mc2 instead of E=mc², and see how each collation behaves.

USE TempDB
GO
CREATE TABLE ColTable
(ID1 NVARCHAR(100) COLLATE Latin1_General_CI_AI,
ID2WS NVARCHAR(100) COLLATE Latin1_General_CI_AI_WS)
GO
INSERT INTO ColTable (ID1, ID2WS)
VALUES ('E=mc² Albert Einstein',
'E=mc² Albert Einstein')
GO
SELECT *
FROM ColTable
WHERE ID1 LIKE 'E=mc2%'
GO
SELECT *
FROM ColTable
WHERE ID2WS LIKE 'E=mc2%'
GO
DROP TABLE ColTable
GO

The Results

Now let’s check the results.

When the collation is width insensitive, E=mc² and E=mc2 are evaluated as equal, and the query returns the row. When the collation is width sensitive, they are evaluated as different, and the query returns no rows. I hope this simple example clears up the difference between width sensitive and width insensitive collation.

The same script on SQL Server 2025 gives these results. A plain 2 and a full-width 2 (U+FF12) act the same way: equal under Latin1_General_CI_AI, different under Latin1_General_CI_AI_WS.

Width sensitivity is not about column size, it is about how a character is encoded.

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