MSSQL : Collation - a way out of case-insensitiveness

Sql Server Databases by default are case-insensitive, both for the object names as well as the data contained in them.

To change a database to case sensitive one, its collation needs to be changed.

To check for the existing collation, check the properties of database and look for a row with heading "Collation"
by default collation is set to "SQL_Latin1_General_CP1_CI_AS". This collation renders database as case-insensitive.

To make your database case-sensitive (as some third party software would expect them to be), we need to change the Collation for the Database.

There are many collations available. you can check them by executing the following statement
select * from ::fn_helpcollations()

Collation "Latin1_General_BIN" is found to work fine for the purpose of making Database case-sensitive.

To change collation, execute an alter statement against the Database

Alter Database TESTDB collate Latin1_General_BIN

Note that the existing tables and their columns would still follow the earlier collation unless recreated or rebuilt.

Collation on specific Tables and their columns as also be changed using 'Alter Table alter column varchar(10) collate Latin1_General_BIN null