Width Sensitive and Width Insensitive Collation in SQL Server explained

Width sensitive collation treats a single-byte character and the same character represented as a double-byte character differently.

Here is an example to test Width sensitive (WS) and Width insensitive collation difference.

We will compare values m2 and m² in a Width insensitive collation:

IF 'm2' = 'm²' COLLATE Latin1_General_CI_AI
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

Here is the result:

...which means that comparing 'm2' to 'm²' in a Width insensitive collation treats a single-byte character and the same character represented as a double-byte character as the same value.

Then we will compare values m2 and m² in a Width sensitive collation:

IF 'm2' = 'm²' COLLATE Latin1_General_CI_AI_WS
SELECT 'Values are the same'
ELSE
SELECT 'Values are different'

Here is the result:

...which means that comparing 'm2' to 'm²' in a Width sensitive collation treats a single-byte character and the same character represented as a double-byte character differently.

See also:
Accent Sensitive and Accent Insensitive Collation in SQL Server explained
Case Sensitive and Case Insensitive Collation in SQL Server explained

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">