Novick Software
SQL Server Consulting Design Programming Tuning

  andy novick is a sql server mvp

 

 

Tips and Tricks for:  SQL Server

 

The Problem:   The DISTINCT clause is case insensitive

By default the DISTINCT clause is case insensitive, at least on a case inseneitive instance of SQL Server.  I ran into a situation where I was looking for all casings of a particular column, in order to fix them. 


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. The data is all in the example, so give them a try.

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

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
 


RSS as HTML

Personal Blog

 
New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage

Upcoming
Presentations:

SQL PASS
Nov 7, '12
Biggest
Loser: DB
Edition


Full Schedule