How SQL Collation Affects Sorting Explained with Examples

What is collation in SQL Server?

Collation is a set of rules that determines how SQL Server stores, compares, and sorts character data.

It controls:

Language and alphabet rules
Case sensitivity
Accent sensitivity
Character encoding/code page
Sorting order

Example collation:

Latin1_General_100_CI_AS

Meaning:

Latin1_General — language/sorting rules
100 — collation version
CI — Case Insensitive
AS — Accent Sensitive

Common collation options

Option	Meaning	Example
`CI`	Case-insensitive	`'ABC' = 'abc'`
`CS`	Case-sensitive	`'ABC' <> 'abc'`
`AI`	Accent-insensitive	`'café' = 'cafe'`
`AS`	Accent-sensitive	`'café' <> 'cafe'`
`BIN2`	Binary comparison	Compares character code values
`UTF8`	UTF-8 encoding	Available for `CHAR`/`VARCHAR` in supported collations

How does it affect comparison?

Case-insensitive comparison

SELECT CASE
    WHEN 'SQL' COLLATE Latin1_General_100_CI_AS =
         'sql' COLLATE Latin1_General_100_CI_AS
    THEN 'Equal'
    ELSE 'Not Equal'
END;

Result:

Equal

Case-sensitive comparison

SELECT CASE
    WHEN 'SQL' COLLATE Latin1_General_100_CS_AS =
         'sql' COLLATE Latin1_General_100_CS_AS
    THEN 'Equal'
    ELSE 'Not Equal'
END;

Result:

Not Equal

Collation affects operators and clauses such as:

=, <>, LIKE, IN, JOIN, GROUP BY, DISTINCT

For example, under a case-insensitive collation:

WHERE UserName = 'syed'

can also match Syed, SYED, and syed.

How does it affect sorting?

Collation decides the order in which text values are returned by ORDER BY.

SELECT Name
FROM Employees
ORDER BY Name;

The order may differ based on:

Language-specific alphabet rules
Uppercase and lowercase handling
Accented characters
Binary versus linguistic sorting

You can override the collation for a query:

SELECT Name
FROM Employees
ORDER BY Name COLLATE Latin1_General_100_CS_AS;

Collation levels

SQL Server collation can be configured at different levels:

Server collation – default for the SQL Server instance and system databases.
Database collation – default for new character columns in that database.
Column collation – rules for a specific column.
Expression-level collation – temporary override using COLLATE.

CREATE TABLE Users
(
    UserName VARCHAR(100)
        COLLATE Latin1_General_100_CS_AS
);

Collation conflict

Joining or comparing columns with incompatible collations can cause an error:

Cannot resolve the collation conflict...

It commonly occurs when joining:

Tables from different databases
User tables with temporary tables
Columns created with different collations

Temporary query-level solution:

SELECT *
FROM DatabaseA.dbo.Customers a
JOIN DatabaseB.dbo.Customers b
  ON a.Name = b.Name COLLATE DATABASE_DEFAULT;

The better long-term solution is to use consistent collations for related columns.

Performance consideration

Applying COLLATE directly to an indexed column may require conversion and can prevent efficient index usage:

WHERE UserName COLLATE Latin1_General_100_CS_AS = 'Syed'

Choose the correct column collation during database design whenever possible.

Key interview points

Collation defines comparison, sorting, case, accent and encoding rules.
CI/CS control case sensitivity; AI/AS control accent sensitivity.
Collation affects WHERE, JOIN, LIKE, ORDER BY, GROUP BY and DISTINCT.
Server, database and column collations can be different.
The COLLATE clause overrides collation for a particular expression.
Different collations can produce collation-conflict errors.
Collation does not translate text; it only defines encoding and linguistic comparison rules.
Changing collation can affect uniqueness, indexes, query results and application behavior.

How SQL Collation Affects Sorting?