Collation is a fundamental concept in SQL Server that is often overlooked or misunderstood. Many developers and DBAs tend to search for workarounds to fix collation errors without fully understanding the implications of their actions. In this article, we will explore the importance of collation and how it can affect query results.

Collation refers to the rules that determine how string comparison and sorting operations are performed in a database. It defines the character set, case sensitivity, and accent sensitivity for textual data. Different collations can yield different results when comparing or sorting data.

Let’s consider a simple example to illustrate this concept. We will create a table with two columns, each having a different collation. One column will be case-sensitive (CS), while the other will be case-insensitive (CI).

CREATE TABLE ColTable (
    Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS,
    Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS
);

INSERT INTO ColTable (Col1, Col2)
VALUES ('Apple', 'Apple'),
       (' apple ', 'apple'),
       (' pineapple ', 'pineapple'),
       ('Pineapple', 'Pineapple');

Now, let’s retrieve the data from the table and compare the results of two queries. In the first query, we will order the data by Col1, and in the second query, we will order it by Col2.

-- Retrieve Data
SELECT * FROM ColTable ORDER BY Col1;

-- Retrieve Data
SELECT * FROM ColTable ORDER BY Col2;

Although both columns contain the exact same data, the order of the results will be different. This is because one column has a case-sensitive collation, while the other has a case-insensitive collation. The case-sensitive collation will treat lowercase ‘apple’ as different from uppercase ‘Apple’, resulting in a different sorting order.

It is crucial to understand the business needs and sensitivity of the data before changing the collation of columns or databases. Changing the collation without considering these factors can lead to unexpected results and data inconsistencies.

Collation is a complex topic that requires careful consideration. It is essential to thoroughly understand and explore collation before making any assumptions or taking it for granted. By gaining a deeper understanding of collation, you can avoid collation-related issues and ensure consistent and accurate query results.

For more information on collation in SQL Server, you can refer to the following video: