|
SQL collation is a set of rules that determine how data is sorted (ORDER BY), compared ( =, <, >, etc.) and appended ( + ) by SQL Server.
Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent sensitivity, and national character types. The COLLATE clause is used to set specific collation for a compare or sort operation. Abbreviations:
CI - Case Insensitive
CS - Case Sensitive
AI - Accent Insensitive
AS - Accent Sensitive
Execute the following
scripts in Management Studio Query Editor to test some of characteristics of collation and list all:
------------
-- SQL COLLATE usage
------------
-- SQL Server collate
use tempdb;
select ProductID,
ProductName = Name COLLATE SQL_Hungarian_CP1250_CS_AS,
ListPrice
into huProduct
from AdventureWorks.Production.Product
go
-- Collations are different for ProductName-s
-- SQL collation compare operation
select p.*
from AdventureWorks.Production.Product p
join huProduct hup
on p.Name = hup.ProductName
go
/* Error message
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "SQL_Hungarian_CP1250_CS_AS"
and "Latin1_General_CS_AS" in the equal to operation.
*/
-- COLLATE SQL_Hungarian_CP1250_CS_AS will correct it
-- SQL collate inner join
-- SQL compare operation with specific collation
select p.*
from AdventureWorks.Production.Product p
join huProduct hup
on p.Name = hup.ProductName COLLATE SQL_Hungarian_CP1250_CS_AS
go
-- COLLATE Latin1_General_CS_AS will correct it
-- SQL collate inner join
select p.*
from AdventureWorks.Production.Product p
join huProduct hup
on p.Name = hup.ProductName COLLATE Latin1_General_CS_AS
go
-- SQL collate database_default
select p.*
from AdventureWorks.Production.Product p
join huProduct hup
on p.Name = hup.ProductName COLLATE DATABASE_DEFAULT
go
-- Cleanup
drop table huProduct
go
------------
-- ALL SQL collations
------------
use master;
-- List all collations with code page
-- SQL system table-valued system function
/*
select TOP (100) name, CodePage = COLLATIONPROPERTY(name, 'CodePage'),
description=LEFT(description,80)
from sys.fn_HelpCollations() order by name
go
/*
Albanian_100_BIN 1250 Albanian-100, binary sort
Albanian_100_BIN2 1250 Albanian-100, binary code point comparison sort
Albanian_100_CI_AI 1250 Albanian-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-
Albanian_100_CI_AI_KS 1250 Albanian-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-in
Albanian_100_CI_AI_KS_WS 1250 Albanian-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-se
Albanian_100_CI_AI_WS 1250 Albanian-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-
Albanian_100_CI_AS 1250 Albanian-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-in
Albanian_100_CI_AS_KS 1250 Albanian-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-inse
Albanian_100_CI_AS_KS_WS 1250 Albanian-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sens
Albanian_100_CI_AS_WS 1250 Albanian-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-se
Albanian_100_CS_AI 1250 Albanian-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-in
Albanian_100_CS_AI_KS 1250 Albanian-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-inse
Albanian_100_CS_AI_KS_WS 1250 Albanian-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sens
Albanian_100_CS_AI_WS 1250 Albanian-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-se
Albanian_100_CS_AS 1250 Albanian-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-inse
Albanian_100_CS_AS_KS 1250 Albanian-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insens
Albanian_100_CS_AS_KS_WS 1250 Albanian-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensit
Albanian_100_CS_AS_WS 1250 Albanian-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sens
Albanian_BIN 1250 Albanian, binary sort
Albanian_BIN2 1250 Albanian, binary code point comparison sort
Albanian_CI_AI 1250 Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-inse
Albanian_CI_AI_KS 1250 Albanian, case-insensitive, accent-insensitive, kanatype-sensitive, width-insens
Albanian_CI_AI_KS_WS 1250 Albanian, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensit
Albanian_CI_AI_WS 1250 Albanian, case-insensitive, accent-insensitive, kanatype-insensitive, width-sens
Albanian_CI_AS 1250 Albanian, case-insensitive, accent-sensitive, kanatype-insensitive, width-insens
Albanian_CI_AS_KS 1250 Albanian, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensit
Albanian_CI_AS_KS_WS 1250 Albanian, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitiv
Albanian_CI_AS_WS 1250 Albanian, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensit
Albanian_CS_AI 1250 Albanian, case-sensitive, accent-insensitive, kanatype-insensitive, width-insens
Albanian_CS_AI_KS 1250 Albanian, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensit
Albanian_CS_AI_KS_WS 1250 Albanian, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitiv
Albanian_CS_AI_WS 1250 Albanian, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensit
Albanian_CS_AS 1250 Albanian, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensit
Albanian_CS_AS_KS 1250 Albanian, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitiv
Albanian_CS_AS_KS_WS 1250 Albanian, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Albanian_CS_AS_WS 1250 Albanian, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitiv
Arabic_100_BIN 1256 Arabic-100, binary sort
Arabic_100_BIN2 1256 Arabic-100, binary code point comparison sort
Arabic_100_CI_AI 1256 Arabic-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-in
Arabic_100_CI_AI_KS 1256 Arabic-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-inse
Arabic_100_CI_AI_KS_WS 1256 Arabic-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sens
Arabic_100_CI_AI_WS 1256 Arabic-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-se
Arabic_100_CI_AS 1256 Arabic-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-inse
Arabic_100_CI_AS_KS 1256 Arabic-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insens
Arabic_100_CI_AS_KS_WS 1256 Arabic-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensit
Arabic_100_CI_AS_WS 1256 Arabic-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sens
Arabic_100_CS_AI 1256 Arabic-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-inse
Arabic_100_CS_AI_KS 1256 Arabic-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insens
Arabic_100_CS_AI_KS_WS 1256 Arabic-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensit
Arabic_100_CS_AI_WS 1256 Arabic-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sens
Arabic_100_CS_AS 1256 Arabic-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insens
Arabic_100_CS_AS_KS 1256 Arabic-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensit
Arabic_100_CS_AS_KS_WS 1256 Arabic-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitiv
Arabic_100_CS_AS_WS 1256 Arabic-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensit
Arabic_BIN 1256 Arabic, binary sort
Arabic_BIN2 1256 Arabic, binary code point comparison sort
Arabic_CI_AI 1256 Arabic, case-insensitive, accent-insensitive, kanatype-insensitive, width-insens
Arabic_CI_AI_KS 1256 Arabic, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensit
Arabic_CI_AI_KS_WS 1256 Arabic, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitiv
Arabic_CI_AI_WS 1256 Arabic, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensit
Arabic_CI_AS 1256 Arabic, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensit
Arabic_CI_AS_KS 1256 Arabic, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitiv
Arabic_CI_AS_KS_WS 1256 Arabic, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Arabic_CI_AS_WS 1256 Arabic, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitiv
Arabic_CS_AI 1256 Arabic, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensit
Arabic_CS_AI_KS 1256 Arabic, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitiv
Arabic_CS_AI_KS_WS 1256 Arabic, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Arabic_CS_AI_WS 1256 Arabic, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitiv
Arabic_CS_AS 1256 Arabic, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitiv
Arabic_CS_AS_KS 1256 Arabic, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Arabic_CS_AS_KS_WS 1256 Arabic, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Arabic_CS_AS_WS 1256 Arabic, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Assamese_100_BIN 0 Assamese-100, binary sort
Assamese_100_BIN2 0 Assamese-100, binary code point comparison sort
Assamese_100_CI_AI 0 Assamese-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-
Assamese_100_CI_AI_KS 0 Assamese-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-in
Assamese_100_CI_AI_KS_WS 0 Assamese-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-se
Assamese_100_CI_AI_WS 0 Assamese-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-
Assamese_100_CI_AS 0 Assamese-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-in
Assamese_100_CI_AS_KS 0 Assamese-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-inse
Assamese_100_CI_AS_KS_WS 0 Assamese-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sens
Assamese_100_CI_AS_WS 0 Assamese-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-se
Assamese_100_CS_AI 0 Assamese-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-in
Assamese_100_CS_AI_KS 0 Assamese-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-inse
Assamese_100_CS_AI_KS_WS 0 Assamese-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sens
Assamese_100_CS_AI_WS 0 Assamese-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-se
Assamese_100_CS_AS 0 Assamese-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-inse
Assamese_100_CS_AS_KS 0 Assamese-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insens
Assamese_100_CS_AS_KS_WS 0 Assamese-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensit
Assamese_100_CS_AS_WS 0 Assamese-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sens
Azeri_Cyrillic_100_BIN 1251 Azeri-Cyrillic-100, binary sort
Azeri_Cyrillic_100_BIN2 1251 Azeri-Cyrillic-100, binary code point comparison sort
Azeri_Cyrillic_100_CI_AI 1251 Azeri-Cyrillic-100, case-insensitive, accent-insensitive, kanatype-insensitive,
Azeri_Cyrillic_100_CI_AI_KS 1251 Azeri-Cyrillic-100, case-insensitive, accent-insensitive, kanatype-sensitive, wi
Azeri_Cyrillic_100_CI_AI_KS_WS 1251 Azeri-Cyrillic-100, case-insensitive, accent-insensitive, kanatype-sensitive, wi
Azeri_Cyrillic_100_CI_AI_WS 1251 Azeri-Cyrillic-100, case-insensitive, accent-insensitive, kanatype-insensitive,
Azeri_Cyrillic_100_CI_AS 1251 Azeri-Cyrillic-100, case-insensitive, accent-sensitive, kanatype-insensitive, wi
Azeri_Cyrillic_100_CI_AS_KS 1251 Azeri-Cyrillic-100, case-insensitive, accent-sensitive, kanatype-sensitive, widt
Azeri_Cyrillic_100_CI_AS_KS_WS 1251 Azeri-Cyrillic-100, case-insensitive, accent-sensitive, kanatype-sensitive, widt
Azeri_Cyrillic_100_CI_AS_WS 1251 Azeri-Cyrillic-100, case-insensitive, accent-sensitive, kanatype-insensitive, wi
*/ |
