# 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)

#### Related Posts

• I am munish i like u r blog very interesting daily, I saw in your blog very thanks in your article

• Hello Pinal,
I want to become master in database so please tell me what steps i will follow.

Thanks,
MK.

• Very interesting stuff! I had been unaware this collation setting even existed until now.

• Pian Dave you are just awesome… i have leanrt lot of things from you blog and infact i have read your books buy purchasing… Thanks a lot for your awesome explanations.

• Hi pinal,
Thanks a lot for your Blogs, I am following you for more than last two years and also studying your books.

I have a query for Width Senstivity, as I understood, WS check the bytes occupied by data.

I executed the below queries and share the results, please let e know, where I am wrong ??

Create table testcollation
(
Name varchar(10),
)

Create table testcollation1
(
Name varchar(10) collate Latin1_General_CS_AS,
Addres varchar(20) collate Latin1_General_CI_AI_WS, — WS Sesitive
)

insert into testcollation select ‘Ashish’,’Jain’
insert into testcollation1 select ‘Ashish’,’Jain’

Select * from testcollation1 where Name=’ashish’
Select * from testcollation1 where Addres=’jain’

— Ashish Jain
— Why data is return, when it’s WS sensitive testcollation table column addres is nvarchar and testcollation1 is varchar.

Select a.Name,b.Addres from testcollation1 a join testcollation b