The SQL DISTINCT clause is case insensitive by default

Solution:  The COLLATE clause can make any string operation case sensitive.

The solution turned out to be pretty easy, use a COLLATE clause with a case sensitive collation.  Here's a sample query with both case senstive and case insensitve collations.

Case insensitive:

select distinct (Item) COLLATE sql_latin1_general_cp1_cs_as
    FROM (
           select  'abcd' item
 union all select  'ABCD'
 union all select  'defg'
 union all select  'deFg') items

 

Case sensitive:

All that is different in the next query is the name of the collation:

select distinct (Item) COLLATE sql_latin1_general_cp1_ci_ai
    FROM (
           select  'abcd' item
 union all select  'ABCD'
 union all select  'defg'
 union all select  'deFg') items