T-SQL 101: 42 Comparing strings by using collations in SQL Server

Another important concept when you’re working with strings is the concept of Collations. If I write code like the first line here:

The question is how would I know if a capital A is treated the same as a lower case A. The answer to that is “it depends upon the collation”. Collations are sets of rules that determine how strings are compared. SQL Server supports lots of collations. You can see them all by executing:

SELECT * FROM sys.fn_helpcollations();

If you’ve never done that, it’s worth taking a look at the available options.

For most people, the most important part of the collation names, apart from the language, is the CI and AS parts in the example above. CI says “Case insensitive”. If it was CS, it would have been “Case sensitive”. This determines how capitals and lower letters are dealt with.

The AS is “Accent sensitive” and you can similarly have AI for “Access insensitive”. This relates to accents like accents, graves, umlauts, etc. commonly seen in European languages.

Some collation names start with a SQL prefix. These are old Sybase/SQL Server collations that are retained for backwards compatibility. You should avoid them and use the Windows collations i.e. the ones without the SQL prefix.

You can set default collations at the database and column levels. (Note you cannot set them at the table level).

If the two values you are comparing use different collations, or if you want to use a different collation to the database’s default collation, you can specify which one you want by using the COLLATE clause. You can see it in the code image above. In that example, you might have a case-sensitive database but you want to that one comparison in a case-insensitive way.

Temporary Tables

One common problem happens when your database has a different collation to the tempdb database, and you are comparing values between your database and some temporary object. When that happens, you’ll see an error that says SQL Server can’t resolve the collation conflict. In this case, you definitely need to override by using a COLLATE clause.

You could then specify a particular collation, or if you just want to default to what the database uses, you can say COLLATE DATABASE_DEFAULT without needing to know which collation the database uses.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-11-04