Yes, a SQL Server database can be case sensitive. Case sensitive here means that SQL Server will return different result set for CASE, Case, CaSe etc. and it will treat the mentioned strings as 3 different strings. A case sensitive database has a case sensitive collation. In this blog we’ll look at case sensitive searches. A list of collation is given here

Let’s consider an example

The above query does a search on Person.Firstname column. The output of above query is given below.

1_Case sensitive search in SQL Server

The firstname in above results is Terri whereas the search value is terri. The SQL Server does a case insensitive search. Let’s now change the collation of Person.Firstname column to case sensitive collation.

Let’s now do the search and observe the result.

2_Case sensitive search in SQL Server

The query returns zero results as SQL server is now doing a case sensitive search. Let’s change the case of the search value to that of the value in Person.Firstname column and do a search

3_Case sensitive search in SQL Server

The SQL server does a case sensitive search and founds 2 matching rows.

Let’s now do a case insensitive search on a case sensitive column without changing the column collation

4_Case sensitive search in SQL Server

The word COLLATE sets the collation of Person.Firstname column to case insensitive and thus SQL server performs case insensitive search returning 2 rows.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook