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.

einstein SQL SERVER   Example of Width Sensitive and Width Insensitive Collation

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.

widthsensitive SQL SERVER   Example of Width Sensitive and Width Insensitive Collation

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 (http://blog.sqlauthority.com)

10 thoughts on “SQL SERVER – Example of Width Sensitive and Width Insensitive Collation

  1. Hello Pinal,
    I like your articles very much. From last week i started read your articles. Thanks for your articles.
    I want to become master in database so please tell me what steps i will follow.
    Thanks in advance.

    Thanks,
    MK.

    Like

  2. Pingback: SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  4. 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.

    Like

  5. 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 ??

    use AdventureWorks2008

    Create table testcollation
    (
    Name varchar(10),
    Addres nvarchar(20) collate Latin1_General_CI_AI_WS,
    )

    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’

    Select a.Name,b.Addres from testcollation1 a join testcollation b on a.Addres=b.Addres
    — 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
    on datalength(a.Addres)=datalength(b.Addres)
    — No Row return

    Warm Regards
    Ashish Jain

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s