Collation can be specified at the server level, database level, column level, expression level, and the identifier level. A different method is required for each of these.

Server Level Collation

Here’s how to find the server level collation:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

This returns the collation like this:

Server Collation
----------------------------
SQL_Latin1_General_CP1_CI_AS

You can also use the sp_helpsort stored procedure to return the default collation for the server:

EXECUTE sp_helpsort;

This returns the collation like this:

Server default collation
----------------------------
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Database Level Collation

Run the following query to return the collation of a specific database. The WHERE clause allows you to narrow the results down to the database/s you’re interested in:

SELECT 
    name, 
    collation_name 
FROM sys.databases
WHERE name = 'Music';

This results in something like this:

name   collation_name              
-----  ----------------------------
Music  SQL_Latin1_General_CP1_CI_AS

In this case, we specified the database called Music.

You can also use the DATABASEPROPERTYEX() function to return the default collation for a database:

SELECT DATABASEPROPERTYEX('Music', 'Collation') AS Collation;

Column Level Collation

Collation can also be specified at the column level. To find out what collation a specific column uses, run a query against sys.columns. Like this:

SELECT 
    name, 
    collation_name 
FROM sys.columns 
WHERE name = N'ArtistName';

This results in something like this:

name   collation_name              
----------  ----------------------------
ArtistName  SQL_Latin1_General_CP1_CI_AS

Expression and Identifier Level Collation

Collation can be applied to a character string expression to apply a collation cast. For example, you can use the COLLATE clause in a 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.