|
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 |
