Understanding Collations in SQL Server
Collations in SQL Server define the rules for sorting and comparing character data. They control properties such as case sensitivity and accent sensitivity for your data. When dealing with character data types like char and varchar, collations determine the code page and which characters can be stored.
Key Collation Options Explained
In collation names, you may encounter several common abbreviations:
- CS – Case-Sensitive
- AI – Accent-Insensitive
- KS – Kana Type-Sensitive (for Japanese kana characters)
- WS – Width-Sensitive (distinguishes between single-byte and double-byte characters)
- SC – Supports Supplementary Characters
- UTF8 – Uses UTF-8 Encoding Standard
Collation Levels in SQL Server
Collations can be set at different levels within a SQL Server instance, including:
- Server-level collations
- Database-level collations
- Column-level collations
- Expression-level collations
Server-Level Collations
The default collation for the SQL Server instance is chosen during installation. This setting applies to system databases as well as any new user databases created afterwards.
To check the current server collation, you can use the following query:
SELECT CONVERT(varchar, SERVERPROPERTY('collation'));
-- To find the code page of a specific collation
SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');
To view all available collations supported by the server:
SELECT * FROM sys.fn_helpcollations();
Database-Level Collations
You can change the collation of a user database using the ALTER DATABASE command. For example:
ALTER DATABASE myDataBase COLLATE Latin1_General_CS_AI;
To check the collation of a particular database:
SELECT CONVERT(VARCHAR(50), DATABASEPROPERTYEX('database_name', 'collation'));
Or, to list collations for all databases:
SELECT name, collation_name FROM sys.databases;
Column-Level Collations
Collations can be assigned to individual columns within a table. To change the collation for a specific column:
ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Latin1_General_CS_AI;
Expression-Level Collations
Collations can also be applied at the expression level in queries to affect sorting and comparison results. This is useful when you want to enforce locale-specific sorting, for example with ORDER BY:
SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;
-- Or to join tables with different collations
SELECT * FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.Col1 COLLATE Latin1_General_CS_AS = TABLE2.Col1 COLLATE Latin1_General_CS_AS;
Finding Collation for a Specific Column
To determine the collation used by a particular column, you can use this query:
SELECT OBJECT_NAME(OBJECT_ID), name AS ColumnName, collation_name AS ColumnCollation
FROM sys.columns
WHERE collation_name IS NOT NULL
AND OBJECT_NAME(OBJECT_ID) = '?'
AND name = '?';
Or to check all columns with collations across all databases:
EXEC dbo.sp_MSforeachdb N'SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME FROM ?.INFORMATION_SCHEMA.COLUMNS WHERE COLLATION_NAME IS NOT NULL;';
