DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find all collations?

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

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE