KuljeetSingh.com

Sep 30, 2012

How to perform case sensitive searches in SQL Server?

We all know that SQL server is case insesitive for the searches. Case Insensitive search means Sql server don’t differenciate between upper and lower case of words and would return as per spelling. But more often we have to make some case sensitive searches in the query. Here are few methods to perform this case sensitive search:

First Method and I think the most popular method is to cast and convert the field in varbinary.
For e.g.
Select Cust_Name from dbo.customers where cast(Cust_Id as varbinary(10)) = cast(@CustId as varbinary(10))
The above query will work perfect for the case sensitive search, but there would be performance loss because the field is used in function and hence instead of index seek query would scan the whole index to match the output.

Second Method is to attach the collate in query while searching through the queries.
For e.g.
Select Cust_Name from dbo.customers where Cust_Id = @CustId Collate SQL_Latin1_General_CP1_CS_AS
SQL_Latin1_General_CP1_CS_AS is collate which marks the column / query for case sensitive search. By default it's SQL_Latin1_General_CP1_CI_AS for each column. Again this query also scan the index table. Both the above query can work perfectly for small database where number of records are limited.

Third Method is to attach collate to column permanently to the table.
For e.g.
Alter table Customers
alter column Cust_Id varchar(10) collate SQL_Latin1_General_CP1_CS_AS
go

Select Cust_Name from dbo.customers where Cust_Id = @CustId

The above query will work perfectly for the case sensitive searches without any performance loss. This method is useful where we know about the field that will always have case sensitive search only.

All the above methods can be used to make case sensitive search in SQL Server.

Hope, it may be useful.

 
?

Blogs | About Me | My Projects | Contact Me
【丰胸】虽然,这些药物对于乳房而言粉嫩公主,能够起到一定的丰胸效果,可朋友们这些药物的后遗症,也会在我们服完后慢慢显现出来丰胸产品,譬如:机体中的雌激素水平被打破了,就容易导致我们的月经变得不正常丰胸食物,从而使得我们的腰跟着变得圆嘟嘟,而严重的话还可能会导致我们的子宫出血,肝脏受损丰胸效果,所以说若是采用药物来进行丰胸,大家还是小心谨慎些为好。