SQL Server - mapping - difference between Latin1_General_CI_AS and Latin1_General_CS_AS

Question

How can I see the difference between

SELECT * 
FROM (VALUES ('A'),('B'),('Y'),('Z'), ('a'),('b'),('y'),('z')) V(C)
ORDER BY C COLLATE Latin1_General_CS_AS

and

SELECT * 
FROM (VALUES ('A'),('B'),('Y'),('Z'), ('a'),('b'),('y'),('z')) V(C)
ORDER BY C COLLATE Latin1_General_CI_AS

? There is no difference for this character set.

Greetings Bartosh

Answer 1

Add an integer id column to your value set and arrange it after ordering with C.

SELECT * 
FROM (VALUES  (1,'a'),(2,'b'),(3,'y'),(4,'z'),(5,'A'),(6,'B'),(7,'Y'),(8,'Z'),(9,'a'),(10,'b'),(11,'y'),(12,'z')) V(ID,C)
ORDER BY C COLLATE Latin1_General_CS_AS,ID

SELECT * 
FROM (VALUES (1,'a'),(2,'b'),(3,'y'),(4,'z'),(5,'A'),(6,'B'),(7,'Y'),(8,'Z'),(9,'a'),(10,'b'),(11,'y'),(12,'z')) V(ID,C)
ORDER BY C COLLATE Latin1_General_CI_AS,ID

For the first table, which is random, 'a' <> 'A', so they are processed separately. Our order first places a lower case, and then orders them by identifier (1, 9), and then follows upper case A.

ID  C
1   a
9   a
5   A

"a" = "A", , 3 a ( A)

ID  C
1   a
5   A
9   a

b, y z.