When using SQL Server, you can use T-SQL to specify the default collation of a database. Specifically, you can use the COLLATE clause of either the CREATE DATABASE or ALTER DATABASE  statements.

The COLLATE clause is optional – if you don’t use it when creating the database, the database will use the default collation of the server (which was specified at the time SQL Server was installed). And if you don’t specify the clause when altering the database, its default collation won’t change.

Set the Collation

First up, here’s an example of using the COLLATE clause to specify the default collation when creating a new database:

CREATE DATABASE Solutions  
COLLATE French_CI_AI;
GO

This example creates a database called Solutions and sets its collation to French_CI_AI.

Change the Collation

To change the collation of an existing database, use the ALTER DATABASE statement with the COLLATE clause:

ALTER DATABASE Solutions
COLLATE Thai_CI_AI_WS; 
GO

This changes the collation to Thai_CI_AI_WS.

Check the Collation

There are several ways to check the default collation of a database in SQL Server. If you’re using SSMS,  you can right-click on the database in the Object Browser and select Properties.

There are also a couple of different ways to check your database collation using Transact-SQL.

Overriding the Database Collation

You can override the database collation at the column level. Each column can have its own collation, which could be different to the default database collation and/or the server collation.

You can specify the collation at the column level with the COLLATE clause when using the CREATE TABLE or ALTER TABLE statements.