SQL Server- Case Sensitive Search in SQL Server

Today I was working on text search functionality in my application. It was not a very big deal until I had not a requirement for Case Sensitive search functionality. This was new thing (at least for me). I Googled it and find number of search results with same solution. So here I am just adding one more search result for Google.

In previous posts, I explained Check Primary Key Existence in Table, Reset Identity Column in SQL Server, Insert Values into Identity column, Identity Column in SQL Server , STUFF Function, LEN Function, UNICODE Function, LEFT Function, CHARINDEX Function, Simple script to backup all SQL Server databases, Table-Valued Parameters and some other articles related to SQL Server , ASP.Net, C#.

In  SQL Server ,  Installation by default are case insensitive .This means that SQL Server ignores the case of the characters and treats the string 'Dot Net World' equal to the string 'dot net world'.
Let's see a practical example. Suppose we have a table say "BlogName" with following records-

Now If we execute the following query-

SELECT  Name
FROM  BlogName
WHERE  (Name LIKE '%net%')

It will return all record because all rows contains same data and search is case insensitive by default.

Now question is how to make it case sensitive? Answer is very simple. You have to change the collation of above query as shown below-

SELECT     Name
FROM         BlogName
WHERE     (Name LIKE '%net%' COLLATE Latin1_General_CS_AS)

Now, execute the above modified query and see the result-

This is the result which we want.By above method, we change column collation for temporary use. but we can change it's collate permanently. by using following Query-

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure-

EXEC sp_help BlogName

Here is the result of above query-

I hope this will be helpful for you. I would like to have any feedback from you. Your valuable feedback, question, or comments about this article are always welcome.