We all know that SQL is a case insensitive language. Yes, it is true. But we think that even in searching the data in database is also case insensitive. But that's not true. There lies a feature called COLLATION which contradicts our assumption.
Collation comes into action when we use ORDER BY clause. There are several collations which define language and sorting according to the alphabet of that language. The default collation we see in the English version of SQL Server on the English version of Microsoft Windows is Latin collation. As English is derived from Latin, the sorting of the English records in the database is performed according to the English alphabet. This Latin collation of two types, case sensitive and case insensitive. The default collation taken by SQL Server during its installation is Case Insensitive Latin collation. If case sensitive Latin collation is selected then that doesn't allow case insensitive searching of the records.
Let's see it practically...
USE
AdventureWorks2012
GO
CREATE
TABLE
SENSITIVE_SEARCH
(NAME
VARCHAR(100))
GO
INSERT
INTO
SENSITIVE_SEARCH VALUES
('YASHWANTH'),('yashwanth'),('Yashwanth'),('yasHwanth')
GO
SELECT
*
FROM
SENSITIVE_SEARCH
Now, I've created a table and inserted the same name in different styles.
Now let's do a search with a predicate,
SELECT
*
FROM
SENSITIVE_SEARCH WHERE
NAME =
'YASHWANTH'
See the result. It's same as above. Though you change the style of the predicate column value the result lies same.
To achieve case sensitive search, you need to change the collation. Collation can be changed at instance level, database level and column level. In this case, only for this table we need to achieve case sensitive search. So, changing the column's collation is enough.
Before changing the collation, let's know how to know collation at different levels...
1) To know the collation of the server through Object Explorer, right click on the instance name and select Properties.
Through T-SQL, collation can be known as,
SELECT
SERVERPROPERTY('collation')
2) To know the collation of the database through Object Explorer, right click on the database and select Properties. Under Maintenance section, collation appears.
Through T-SQL, it can be known as,
SELECT
DATABASEPROPERTYEX('AdventureWorks2012','collation')
or connect to the database and run this command
SELECT
collation_name FROM
sys.databases
WHERE
name =
'AdventureWorks2012'
3) To know the table collation through Object Explorer, right click on the Table and select Properties. Under Extended Properties, collation appears.
4) Collation of a column can be known through Object Explorer by right clicking on the column and selecting Properties.
Through T-SQL, it can be known as,
SELECT
collation_name FROM
sys.columns
WHERE
object_id
=
OBJECT_ID('SENSITIVE_SEARCH')
AND
name =
'NAME'
Now we need to change the collation of the column NAME in the table SENSITIVE_SEARCH. To facilitate case sensitive search, we need to choose a suitable collation. To know the available collations supported by SQL Server, run the following query.
SELECT
*
FROM
sys.fn_helpcollations()
WHERE
name LIKE
'SQL%'
There are some characteristics to be noted while selecting a collation,
_CS - Case Sensitive
_CI - Case Insensitive
_AS - Accent Sensitive
_AI - Accent Insensitive
_KS - Kana Sensitive
_WS - Width Sensitive
In our present case, we require the following collation,
SQL_Latin1_General_CP1_CS_AS
To change the collation of our table's column to the above collation run the following command,
ALTER
TABLE
SENSITIVE_SEARCH ALTER
COLUMN
NAME VARCHAR(100)
COLLATE
SQL_Latin1_General_CP1_CS_AS
Now case sensitive search is enabled for the column NAME.
SELECT
*
FROM
SENSITIVE_SEARCH WHERE
NAME =
'YASHWANTH'
GO
SELECT
*
FROM
SENSITIVE_SEARCH WHERE
NAME =
'Yashwanth'
To know more about collations in SQL Server, follow the official documentation of Microsoft.
