# 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
WHERE ProductID < 700
UNION ALL
SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
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
/*
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
*/

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
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
*/
DROP TABLE tempdb.dbo.Product

```

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
Sunday, July 25, 2010 8:07 AM

• 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/
Sunday, July 25, 2010 9:15 AM
• Binary collation yields indeed different results.  But how about CS vs CI? Shouldn't they be different for the given sample?
Kalman Toth
Why? Bike still come before Blade no matter what case you use.
If there were two Bikes, {BIKE, bike}, "bike" would come before "BIKE" if using case sensitive collation.

```DECLARE	@Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(100) NOT NULL
)

INSERT @Sample VALUES ('bike')
INSERT @Sample VALUES ('BIKE')

SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CI_AS
SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CS_AS
```
Sunday, July 25, 2010 9:17 AM

### All replies

• 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/
Sunday, July 25, 2010 8:48 AM
• 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
Sunday, July 25, 2010 9:08 AM
• 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/
Sunday, July 25, 2010 9:15 AM
• Binary collation yields indeed different results.  But how about CS vs CI? Shouldn't they be different for the given sample?
Kalman Toth
Why? Bike still come before Blade no matter what case you use.
If there were two Bikes, {BIKE, bike}, "bike" would come before "BIKE" if using case sensitive collation.

```DECLARE	@Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(100) NOT NULL
)

INSERT @Sample VALUES ('bike')
INSERT @Sample VALUES ('BIKE')

SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CI_AS
SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CS_AS
```
Sunday, July 25, 2010 9:17 AM
• 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
UNION ALL
SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
UNION ALL
SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
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
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
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