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.

 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 was explained about how to

 Get List of All Table Names in Database  and COLLATE keyword

I explained  and some other articles related to Now 

In this Article will see how to search In SQL Server- Case Sensitive Search .

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.