SQL Server check case-sensitivity?

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 sensitivity 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_namefrom INFORMATION_SCHEMA.COLUMNSwhere table_name = @table_name

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable (     mycolumn VARCHAR(10) ) GO SET NOCOUNT ON INSERT mytable VALUES('Case') GO SELECT mycolumn FROM mytable WHERE mycolumn='Case' SELECT mycolumn FROM mytable WHERE mycolumn='caSE' SELECT mycolumn FROM mytable WHERE mycolumn='case' 

You can alter your query by forcing collation at the column level:

SELECT myColumn FROM myTable      WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE' SELECT myColumn FROM myTable      WHERE myColumn COLLATE Latin1_General_CS_AS = 'case' SELECT myColumn FROM myTable      WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case' -- if myColumn has an index, you will likely benefit by adding -- AND myColumn = 'case' SELECT DATABASEPROPERTYEX('<database name>', 'Collation') 

As changing this setting can impact applications and SQL queries, I would isolate this test first. From SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:

EXEC sp_help 'mytable' 

The second recordset should contain the following information, in a default scenario:

Column_Name Collation

mycolumn SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:

ALTER TABLE mytable     ALTER COLUMN mycolumn VARCHAR(10)     COLLATE Latin1_General_CS_AS GO SELECT mycolumn FROM mytable WHERE mycolumn='Case' SELECT mycolumn FROM mytable WHERE mycolumn='caSE' SELECT mycolumn FROM mytable WHERE mycolumn='case' 

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

ALTER TABLE mytable     ALTER COLUMN mycolumn VARCHAR(10)     COLLATE SQL_Latin1_General_CP1_CI_AS 

If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match):

SELECT mycolumn FROM mytable WHERE     mycolumn = 'case' AND     CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) SELECT mycolumn FROM mytable WHERE     mycolumn = 'case' AND     CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) SELECT mycolumn FROM mytable WHERE     mycolumn = 'case' AND     CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) -- if myColumn has an index, you will likely benefit by adding -- AND myColumn = 'case' 

SQL server determines case sensitivity by COLLATION.

COLLATION can be set at various levels.

1. Server-level
2. Database-level
3. Column-level
4. Expression-level

Here is the MSDN reference.

One can check the COLLATION at each level as mentioned in Raj More's answer.

Check Server Collation

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

Check Column Collation

select table_name, column_name, collation_namefrom INFORMATION_SCHEMA.COLUMNSwhere table_name = @table_name

Check Expression Collation

For expression level COLLATION you need to look at the expression. :)

It would be generally at the end of the expression as in below example.

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;

Collation Description

For getting description of each COLLATION value try this.

SELECT * FROM fn_helpcollations()

And you should see something like this.

You can always put a WHERE clause to filter and see description only for your COLLATION.

You can find a list of collations here.