By default, each column in a SQL Server database uses the collation that’s been specified at the database level. And by default, the database collation is taken from the server collation. However, these collation settings can be overridden, both at the database level and the column level by explicitly setting the collation at that level.

This page demonstrates how to specify the collation of a column. You can set the collation by adding the T-SQL COLLATE clause to the CREATE TABLE and ALTER TABLE statements. When you use those statements, you define the column and its properties, including any collation settings. The COLLATE clause is optional so if you don’t include it, the column will simply use the default collation of the database.

Set the Collation

Here’s an example of specifying the collation when creating a table:

CREATE TABLE Tasks (
    TaskId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    TaskName nvarchar(255) COLLATE French_CI_AI NOT NULL
);

This creates a table called Tasks with two columns. We set the collation of the second column (TaskName) to French_CI_AI. If we didn’t do that, it would’ve used the default database collation.

Change the Collation

In this next example, I change the column’s collation to Modern_Spanish_CI_AI_WS:

ALTER TABLE Tasks ALTER COLUMN TaskName
    nvarchar(255) COLLATE Modern_Spanish_CI_AI_WS NOT NULL
GO

Note that you can’t change the collation of a column that is referenced by any one of the following:

• A computed column
• An index
• Distribution statistics
• A CHECK constraint
• A FOREIGN KEY constraint

Check the Collation

If you use SSMS, you can check the collation of any column by right-clicking on that column in the Object Browser and selecting Properties.

In Transact-SQL, there’s a couple of different ways of returning a column’s collation. Here’s how to check the collation of a column using T-SQL.