Dynamic Sort Collation Puzzle: CS vs CI

Question

Why is there no difference in results for CS (case sensitive) and CI (case insensitve) sorting?

USE tempdb;
SELECT ProductID, ProductName=Name, ListPrice, Color, New_ID=NEWID()
INTO Product
FROM AdventureWorks2008.Production.Product
WHERE ProductID < 700
UNION ALL
SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
FROM AdventureWorks2008.Production.Product
WHERE ProductID >= 700
ORDER BY New_ID

DECLARE @SQL nvarchar(max)='SELECT ProductID, ProductName, ListPrice,
 Color FROM Product
 ORDER BY ProductName '
SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CS_AS'
PRINT @SQL
EXEC sp_executeSQL @SQL 
/*
1	Adjustable Race	0.00
879	all-purpose bike stand	159.00
712	awc logo cap	8.99
3	BB Ball Bearing	0.00
2	Bearing Ball	0.00
877	bike wash - dissolver	7.95
316	Blade	0.00....
*/

SET @SQL='SELECT ProductID, ProductName, ListPrice,
 Color FROM Product
 ORDER BY ProductName '
SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CI_AS'
PRINT @SQL
EXEC sp_executeSQL @SQL 
GO
/* ProductID	ProductName	ListPrice	Color
1	Adjustable Race	0.00
879	all-purpose bike stand	159.00
712	awc logo cap	8.99
3	BB Ball Bearing	0.00
2	Bearing Ball	0.00
877	bike wash - dissolver	7.95
316	Blade	0.00.....
*/
DROP TABLE tempdb.dbo.Product

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 1

Specify BINARY collation, see if that helps

DECLARE

 

@SQL nvarchar(max)

SET

 

@SQL='SELECT ProductID, ProductName, ListPrice,

Color FROM Product

ORDER BY ProductName '

SET

 

@SQL=@SQL + 'COLLATE Latin1_General_BIN'

--SQL_Latin1_General_CP1250_CS_AS

PRINT

 

@SQL

EXEC

 

sp_executeSQL @SQL

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Binary collation yields indeed different results.  But how about CS vs CI? Shouldn't they be different for the given sample?

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 3

Just plaing with it

CaseSensitivity can be either CI (case insensitive) or CS (case sensitive)

AccentSensitivity can be either AI (accent insensitive) or AS (accent sensitive)

BIN means that binary sort order is used, instead of text order

CREATE TABLE T1
(
  c1 VARCHAR(1) COLLATE Latin1_General_CI_AS,
  c2 VARCHAR(1) COLLATE Latin1_General_CS_AS
)

INSERT T1 VALUES('b', 'b')
INSERT T1 VALUES('B', 'B')
INSERT T1 VALUES('b', 'b')
INSERT T1 VALUES('B', 'B')
INSERT T1 VALUES('a', 'a')
INSERT T1 VALUES('A', 'A')
INSERT T1 VALUES('a', 'a')
INSERT T1 VALUES('A', 'A')

SELECT * FROM T1 ORDER BY c2

c1   c2
---- ----
a    a
a    a
A    A
A    A
b    b
b    b
B    B
B    B

SELECT * FROM T1 ORDER BY c1

c1   c2
---- ----
a    a
A    A
a    a
A    A
b    b
B    B
b    b
B    B

SELECT * FROM T1 ORDER BY c1 COLLATE Latin1_General_CS_AS

c1   c2
---- ----
a    a
a    a
A    A
A    A
b    b
b    b
B    B
B    B

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Thanks Uri & Peso. Following sample shows the difference in CS, CI and BIN sorting:

-- Create test table with SELECT INTO
USE tempdb;
SELECT ProductID, ProductName=Name, ListPrice, Color, New_ID=NEWID()
INTO Product
FROM AdventureWorks2008.Production.Product
UNION ALL
SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
FROM AdventureWorks2008.Production.Product
UNION ALL
SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
FROM AdventureWorks2008.Production.Product
ORDER BY New_ID
/****** CASE SENSITIVE COLLATION - CS ******/
DECLARE @SQL nvarchar(max)='SELECT ProductID, ProductName, ListPrice,
 Color FROM Product
 ORDER BY ProductName '
SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CS_AS'
PRINT @SQL
EXEC sp_executeSQL @SQL 
/*
ProductID	ProductName
1	adjustable race
1	adjustable race
1	Adjustable Race
879	all-purpose bike stand
879	all-purpose bike stand
879	All-Purpose Bike Stand....
*/
/****** CASE INSENSITIVE COLLATION - CI ******/
SET @SQL='SELECT ProductID, ProductName, ListPrice,
 Color FROM Product
 ORDER BY ProductName '
SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CI_AS'
PRINT @SQL
EXEC sp_executeSQL @SQL 
/* ProductID	ProductName
1	adjustable race
1	Adjustable Race
1	adjustable race
879	All-Purpose Bike Stand
879	all-purpose bike stand
879	all-purpose bike stand
.....
*/
/****** BINARY COLLATION ******/
SET @SQL ='SELECT ProductID, ProductName, ListPrice,
 Color FROM Product
 ORDER BY ProductName '
SET @SQL=@SQL + 'COLLATE Latin1_General_100_BIN'
PRINT @SQL
EXEC sp_executeSQL @SQL 
GO
/* ProductID	ProductName
712	AWC Logo Cap
1	Adjustable Race
879	All-Purpose Bike Stand
3	BB Ball Bearing
2	Bearing Ball
*/
DROP TABLE tempdb.dbo.Product

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM