Collation can be set at various levels:
- Server
- Database
- Column
So you could have a Case Sensitive Column in a Case Insensitive database. I have not yet come across a situation where a business case could be made for case senstivity of a single column of data, but I suppose there could be.
Check Server Collation
SELECT SERVERPROPERTY('COLLATION')
Check Database Collation
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
Check Column Collation
select table_name, column_name, collation_name
from information_schema.columns
where table_name = @table_name
What the Result Means
Typically, you'll get
SQL_Latin1_General_CP1_CI_AS.
The CI part means CASE INSENSITIVE. SQL_Latin1_General_CP1_CI_AS.
If it was case SENSITIVE, it would be 'CS' instead of 'CI':
SQL_Latin1_General_CP1_CS_AS