Binaryworld - How to perform case-sensitive ordering on case-insensitive servers? ... [ SQL Server -> Performance ]

This script demonstrates the COLLATION capabilities in SQL Server 2000.

-- Ordering on case insensitive server
SELECT 'a' AS String
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'A'
UNION ALL
SELECT 'B'
ORDER BY String;
/*
String
------
a
A
b
B
*/

/*
    Case sensitive sorting by modifying the collation of the data.
    This technique can be used to convert data from one collation to
    another also. The COLLATE clause can be used in comparisions also.
*/
SELECT *
FROM (
    SELECT 'a'
    UNION ALL
    SELECT 'b'
    UNION ALL
    SELECT 'A'
    UNION ALL
    SELECT 'B'
) AS c( String )
ORDER BY CAST( String as varchar ) COLLATE SQL_Latin1_General_CP850_CS_AS
/*
String
------
A
a
B
b
*/

-- Tip from BOL:
-- To see a list of valid collations, do:
SELECT * FROM ::fn_helpcollations()
-- Sample Output:
/*
name                 description
------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_Latin1_General_CP850_CI_AS    Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 42 on Code Page 850 for non-Unicode Data
SQL_Latin1_General_CP850_CS_AS    Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 41 on Code Page 850 for non-Unicode Data
SQL_Latin1_General_Pref_CP1_CI_AS   Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 53 on Code Page 1252 for non-Unicode Data
SQL_Latin1_General_Pref_CP437_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 33 on Code Page 437 for non-Unicode Data
SQL_Latin1_General_Pref_CP850_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 43 on Code Page 850 for non-Unicode Data
*/