SQL server database collation explained

When we installing a new SQL server instance, we have to choose a database collation. This will be the default collation for all databases that going to be created on this instance later on.

However, this default database collation can be overridden in following ways.

1. When creating a new database, we can specify a different collation for the database.

For example

CREATE DATABASE DbWithNewLanguage COLLATE SQL_Latin1_General_CP1_CI_AS

2. When creating a new table, we can specify different collations for individual column.

For example

CREAT TABLE MixLanguageTable (
ID INT IDENTITY,
EnglishName nvarchar(150) COLLATE Latin1_General_CS_AS,
GreekName nvarchar(150) COLLATE Greek_CS_AS_KS,
JapaneseName nvarhcar(150) COLLATE Japanese_90_CI_AS_KS_WS
)

3. When querying the tables, we can specify different collations.

For example

--EnglishName column in the example above is case sensitive. But we can change the collation so when we query the table on this column, we can set it to be case insensitive.

SELECT * FROM dbo.MixLanguageTable WHERE EnglishName = ‘test name’ COLLATE Latin1_General_CI_AS