Systems Engineering and RDBMS

Archive for April 30th, 2007

Case Sensitive Searches in SQL Server

Posted by decipherinfosys on April 30, 2007

Some time ago, we had blogged about doing case INsensitive searches in Oracle – you can read more on that post here. Oracle, by default is case sensitive whereas SQL Server installations by default are case-insensitive i.e. if you have a column called say FIRST_NAME and you want it to be unique, you can have SAM and sam as two values in it in Oracle where as in SQL Server, that will error out with a unique key violation.

There are of course, ways to change that behavior. We had covered the Oracle bit in the blog post mentioned above and in this one, we will see how you can achieve the same thing in SQL Server. In SQL Server, you can specify collation at the Instance, Database, table, column or the query level. One can choose from Windows collations or SQL Server collations. Windows collations are collations that are defined for SQL Server installations to support Windows locales and SQL Server collations are collations to match the code-page number and sort order combinations.

Suppose, that you do not have the option of making collation changes at the instance, database or column level. How can you make a query collation change to make the search case sensitive? Here is an example that illustrates that feature:

First, let’s see what is the instance level collation setting:

select serverproperty(‘collation’), serverproperty(‘sqlsortorder’)

—————————– —
SQL_Latin1_General_CP1_CI_AS 52

As you can see from above, this is a Case-Insensitive collation at the server level. Now, let’s do some searches based on some dummy data:

set nocount on

declare @table_demo table
(col1 varchar(20) )
insert into @table_demo values (‘SAM’)
insert into @table_demo values (‘sam’)
insert into @table_demo values (‘sam king’)
insert into @table_demo values (‘Sam King’)
insert into @table_demo values (‘SAm KINg’)

/*Normal case insensitive search*/
select * from @table_demo where col1 like ‘sam%’

sam king
Sam King

/*Case Sensitive search*/
select * from @table_demo where col1 like ‘sam%’ collate SQL_Latin1_General_CP1_CS_AS

sam king

As you can see from above, the case-sensitive search returns back only the two records with the matching case where-as the other one returns back all the records regardless of the case of the text. Typically, if you need this kind of behavior, you should account for it at the time of designing your applications – however, as is the very nature of growth, you might not have anticipated this at design time – an example is an install that is done in say Western European or Asian countries and the tempdb collation is thus the collation of the server whereas your user database collation is different that that server collation – if you have queries that are using temporary tables and doing joins with the data set coming from the user database tables, there will be collation mis-matches, the sort rules will be different – that is one place, you can take advantage of the fact that you can play with collation at the query level as well.

Posted in SQL Server | Leave a Comment »