SQL SERVER – Example of Width Sensitive and Width Insensitive Collation

I had a great time writing blog post SQL SERVER – Effect of Case Sensitive Collation on Resultset. It was interesting to see lots of questions related to collation based on this blog post. However, one of the question, I find very interesting and though to share today here.

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

I indeed found this question interesting as I see very little awareness of the subject of collation.  I have talked with many and seen very little awareness on width sensitive collation.

Let me explain the same with a very simple example.

Width Sensitive Collation: A single-byte character (half-width) represented as single-byte and the same character represented as a double-byte character (full-width) are when compared are not equal the collation is width sensitive.

If above definition is not clear, let us try to understand by 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 his famous example. In 1921, Einstein was awarded the Nobel Prize in Physics for his explanation of the photoelectric effect, however many still believes that he was awarded Nobel Prize because of relativity theory (which is incorrect).

Well, let us try to create a small example. In this example we have one table with two columns. One column has a collation of width sensitive and the second column has a collation of width insensitive. Now we will insert exactly the same data in both the column and try to retrieve the data from the column using the LIKE statement. However, instead of using E=mc² we will use E=mc2 in where condition and see how Width Sensitive and Width Insensitive 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

Now let us check results.

Results are very interesting. You can see that when there is width insensitive collation E=mc² and E=mc2 are evaluated as equal and the query return result. In case of width sensitive collation comparison E=mc² and E=mc2 are evaluated as false returning no results. I hope this simple example clears the difference between Width Sensitive and Width Insensitive Collation.

Reference: Pinal Dave (https://blog.sqlauthority.com)