Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

The collation settings of SQL Server determine the range of characters that can be stored in a database. Collation also has a direct bearing on how data is sorted (ordered) and compared. SQL Server can have either a Windows or a SQL collation configured at the server level. At the database level, individual databases can have different collation settings applied, which override those set at the server level. As a general rule, if tables include both Unicode and non-Unicode (ASCII) data, then a Windows collation should be selected.

I tend to use Latin1_General_CI_AS across all of my servers. This ensures compatibility when querying or moving data across multiple servers and databases. It leads to fewer conflicts and more consistent, predictable query results. Latin1 includes all major English and European-style characters. The CI (case insensitive) part of the collation ensures that queries do not differentiate between lower case and upper case characters. Thus,

SELECT firstname
FROM mytable
WHERE firstname = 'Zoe'

will return anyone with a name of ‘Zoe’, ‘zoe’, ‘ZOE’ etc. Setting the collation to CS (case sensitive) would lead to the same query returning only ‘Zoe’. By specifying AS (accent sensitive) in the collation, accented characters such as ö would affect query results. The above query would not return Zöe unless AI (accent insensitive) were specified.

To find the collation of all databases on a server, execute the following query:-

SELECT [name], [collation_name]
FROM sys.databases

To return a list of all available collations, run the following:-

FROM :: fn_helpcollations()

To return server-level collation settings:-


Tags: , , , , , , ,

%d bloggers like this: