Collation is an important concept in SQL Server that determines how string comparison and sorting operations are performed. In a previous blog post, we discussed the effect of case-sensitive collation on the result set. Today, we will explore another aspect of collation – width sensitivity.

Width sensitive collation refers to the comparison of single-byte characters (half-width) and double-byte characters (full-width). When these characters are compared, they are not considered equal in width sensitive collation. This means that a single-byte character represented as a single-byte and the same character represented as a double-byte character will be treated as different.

Let’s understand this concept with a simple example. Imagine we have a table with two columns – one with a width sensitive collation and the other with a width insensitive collation. We will insert the same data in both columns and retrieve the data using the LIKE statement.

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’s analyze the results. In the case of width insensitive collation, the comparison between “E=mc²” and “E=mc2” evaluates as equal, and the query returns a result. However, in the case of width sensitive collation, the same comparison evaluates as false, resulting in no results.

This example clearly demonstrates the difference between width sensitive and width insensitive collation. It is important to understand the behavior of collation in SQL Server to ensure accurate string comparison and sorting operations in your database.

By being aware of the various collation options and their impact, you can make informed decisions when designing and querying your SQL Server databases.

Stay tuned for more articles on SQL Server concepts and best practices!