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 work with SQL collation?

Execute the following SQL Server T-SQL script in SSMS Query Editor to demonstrate the various aspects of SQL collate and SQL collation.

 

USE AdventureWorks;

 

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation');

go

-- Result: SQL_Latin1_General_CP1_CI_AS

 

SELECT * FROM Production.Product

WHERE Name = 'road-650 Red, 58'

GO

-- 1 result

 

SELECT * FROM Production.Product

WHERE Name = 'road-650 Red, 58' COLLATE SQL_Latin1_General_CP1_CS_AS

GO

/* No result due to Case Sensitive (CS) comparison of 'road-650 Red, 58' to

   'Road-650 Red, 58' */

 

-- Case sensitive sort

SELECT * FROM Production.Product

ORDER BY Name COLLATE SQL_Latin1_General_CP1_CS_AS

go

 

USE master;

go

-- Create Case Sensitive(CS) and Accent Sensitive (AS) database

CREATE DATABASE zzzTest_CP1_CS_AS collate SQL_Latin1_General_CP1_CS_AS

go

 

SELECT SERVERPROPERTY ('collation')

go

 

SELECT DATABASEPROPERTYEX('zzzTest_CP1_CS_AS', 'Collation');

go

-- Result: SQL_Latin1_General_CP1_CS_AS

 

USE zzzTest_CP1_CS_AS;

go

 

-- Create table with mixed collation (non-default)

CREATE TABLE Product

(ProductID int identity(1,1) primary key,

 ProductName varchar(30) collate SQL_Latin1_General_CP1_CI_AS,

 ProductNameGerman varchar(30) collate German_PhoneBook_CI_AS_KS_WS,

 ProductNameHungarian varchar(30) collate SQL_Hungarian_CP1250_CI_AS,

 ModifiedDate datetime default (getdate()))

go

 

SELECT name, collation_name from sys.columns

WHERE [object_id]=object_id('Product')

go

/* Results

name  collation_name

ProductID   NULL

ProductName SQL_Latin1_General_CP1_CI_AS

ProductNameGerman German_PhoneBook_CI_AS_KS_WS

ProductNameHungarian    SQL_Hungarian_CP1250_CI_AS

ModifiedDate      NULL

*/

-- Populate table

INSERT Product (ProductName, ProductNameGerman, ProductNameHungarian)

VALUES ('Mountain Bike', 'Bergfahrrad', 'Hegyi Kerékpár')

GO

 

SELECT p.* FROM AdventureWorks.Production.Product awp

INNER JOIN Product p

ON awp.Name = p.ProductName

go

-- Executes ok, no match

 

SELECT p.* FROM AdventureWorks.Production.Product awp

INNER JOIN Product p

ON awp.Name = p.ProductNameGerman

go

/* Error message

Msg 468, Level 16, State 9, Line 2

Cannot resolve the collation conflict between "German_PhoneBook_CI_AS_KS_WS"

and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

*/

 

SELECT p.* FROM AdventureWorks.Production.Product awp

INNER JOIN Product p

ON awp.Name = p.ProductNameGerman COLLATE SQL_Latin1_General_CP1_CI_AS

go

-- Executes OK, no match

 

-- Drop test database

USE master;

DROP DATABASE zzzTest_CP1_CS_AS

go

 

-- Find all collations

 

USE master;

SELECT * FROM sys.fn_HelpCollations()

go

-- Results: list of 1011 collations

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