PrismitaLEARN · PRACTICE · GROW

What is collation in SQL Server?

SQL Server Design Intermediate 2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

Collation is the set of rules for sorting and comparing character data — case sensitivity, accent sensitivity, and the language/culture sort order. It can be set at the server, database, or column level. The common default (SQL_Latin1_General_CP1_CI_AS) is case-insensitive and accent-sensitive.

Collation defines how text is sorted and compared — whether it's case-sensitive, accent-sensitive, and which language's sort order applies. You can set it per server, per database or per column. The default is case-insensitive, which is actually convenient because WHERE name = 'smith' matches 'Smith' and stays index-friendly. The problem you run into is a collation conflict — comparing or joining two columns with different collations throws an error, and you fix it with an explicit COLLATE clause or by making the columns consistent. Temp tables are a common source of that because tempdb uses the server collation.

  • CI/CS = case-insensitive/sensitive; AI/AS = accent-insensitive/sensitive.
  • Case-insensitive collation is why WHERE Name = 'smith' matches 'Smith' without UPPER() — which keeps the query sargable.
  • Collation conflict error: joining or comparing two columns with different collations. Fix with COLLATE in the query, or align the column collations.
  • tempdb has the server collation, so temp tables can conflict with a differently-collated user database — declare the column COLLATE DATABASE_DEFAULT.
All SQL Server questions More on Design