In database systems, collation determines how data is sorted and compared in a database. For 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.

In SQL Server, collation is specified at the server level, the database level, and the column level.

Collation can also be applied to a character string expression to apply a collation cast. For example, you can use the COLLATE clause in a T-SQL SELECT statement to specify the collation to be used. Like this:

USE Music;
SELECT ArtistId, ArtistName
FROM Artists
ORDER BY ArtistName COLLATE Latin1_General_CI_AI;

That collation uses CI for case-insensitive, and AI for accent-insensitive.

We could change that to CS for case-sensitive, and AS for accent-sensitive, and the query results may be sorted differently (depending on the data):

USE Music;
SELECT ArtistId, ArtistName
FROM Artists
ORDER BY ArtistName COLLATE Latin1_General_CS_AS;

So, this allows you to override the collation that’s applied at the database or column level when running a query.

You can use T-SQL to check the collation of the server, database, and a column.