-- http://www.resquel.com/ssb
-- 2007-03-19
CREATE DATABASE Sort_Collate_Demo_ReSQueL_Blog
GO
USE Sort_Collate_Demo_ReSQueL_Blog
GO
CREATE TABLE Latin1_CS_AS (
	RowNum int,
	TextData nchar(10) COLLATE Latin1_General_CS_AS)
GO
CREATE TABLE GerPB_BIN2 (
	RowNum int,
	TextData nchar(10) COLLATE German_PhoneBook_BIN2)
GO
INSERT Latin1_CS_AS VALUES (1, 'ABC')
INSERT Latin1_CS_AS VALUES (2, 'ÄBC')
INSERT Latin1_CS_AS VALUES (3, 'abc')
INSERT Latin1_CS_AS VALUES (4, 'äbc')
INSERT Latin1_CS_AS VALUES (5, 'ABc')
INSERT Latin1_CS_AS VALUES (6, 'ÁBC')
INSERT Latin1_CS_AS VALUES (7, 'abC')
INSERT Latin1_CS_AS VALUES (8, 'ábc')

INSERT GerPB_BIN2
SELECT * FROM Latin1_CS_AS
GO
SELECT RowNum, TextData FROM Latin1_CS_AS ORDER BY TextData
GO
SELECT RowNum, TextData FROM GerPB_BIN2 ORDER BY TextData
GO
SELECT RowNum, TextData FROM GerPB_BIN2 ORDER BY TextData COLLATE Latin1_General_CS_AS
GO
-- This batch will raise an error.
SELECT * FROM GerPB_BIN2 G INNER JOIN Latin1_CS_AS L ON G.TextData = L.TextData 
GO
SELECT * FROM GerPB_BIN2 G INNER JOIN Latin1_CS_AS L ON G.TextData COLLATE Latin1_General_CS_AS = L.TextData 
GO
/* Cleanup your system, run the following lines
USE master
GO
DROP DATABASE Sort_Collate_Demo_ReSQueL_Blog
GO
*/