So for example, Latin1_General_CI_AS is case-insensitive and accent-sensitive.īelow is a quick overview of the options: Option

In SQL Server, collation options are specified by appending the options to the collation name. However, if we change the collation to case-sensitive by using the Latin1_General_CS_AI collation, we get the following result: ArtistId ArtistNameĪs mentioned, case-sensitivity is just one of various options when specifying collation. The options associated with a collation are case sensitivity, accent sensitivity, Kana-sensitivity, width sensitivity, variation-selector-sensitivity.Ĭollations are expressed in different ways, depending on the DBMS that you use. The above query returns the following result: ArtistId ArtistName Note that this collation has the letters CI, which stands for case-insensitive. In this case, we use the Latin1_General_CI_AI collation. This query uses the ORDER BY clause to order the results, and it uses the COLLATE clause to specify the collation to be used when ordering those results. ORDER BY ArtistName COLLATE Latin1_General_CI_AI Collations can also be specified at the expression level (so you can specify which collation to use when you run a query), and at the identifier level.īelow is a comparison between running a case-sensitive query vs a case-insensitive query in SQL Server. Depending on the DBMS, collation can be specified at the server level, the database level, the table level, and the column level. Collation can also be used to distinguish between various ideographic variation selectors in certain collations (such as the Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140 collations that were introduced in SQL Server 2017).ĭifferent database management systems will provide different collation options. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.įor example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.Ĭollation is also used to determine how accents are treated, as well as character width and Japanese kana characters. In database systems, Collation specifies how data is sorted and compared in a database.

Collate means