Monday, June 29, 2015

SQL Server Case SEnSitiviTy

Potentially fraught if someone changes the collation setting on you.


Collation can be set at various levels:
  1. Server
  2. Database
  3. 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

No comments: