How to Check Case-Sensitivity in SQL Server

  • google plus

In Brief...

SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine if a database or database object is to check its "COLLATION" property and look for "CI" or "CS" in the result.

  • CI = Case Insensitive
  • CS = Case Sensitive

Instructions

  1. To check if a server is case sensitive, run this query:
    SELECT SERVERPROPERTY('COLLATION')
    A common result is:
    SQL_Latin1_General_CP1_CI_AS
    The CI indicates that the server is case insensitive.
  2. To check if a specific SQL Server database is case sensitive, run this query:
    SELECT collation_name
    FROM sys.databases
    WHERE name = 'your_database_name'
    Again, this will output something like:
    SQL_Latin1_General_CP1_CI_AS
  3. To check all databases on the server, just leave out the WHERE clause and include name in the SELECT list:
    SELECT name, collation_name
    FROM sys.databases
  4. To check the collation for all columns in a SQL Server database table, run this query:
    SELECT COLUMN_NAME, COLLATION_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'your_table_name' AND CHARACTER_SET_NAME IS NOT NULL

Another command for checking the case-sensitivity and other properties of the database server is:

EXECUTE sp_helpsort

This will return something like:

Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Finally, to see all collations supported by your SQL Server installation, run this:

SELECT name, description FROM sys.fn_helpcollations()

The result will be something like this:
SQL Server Collation

Author: Nat Dunn

Nat Dunn founded Webucator in 2003 to combine his passion for web development with his business expertise and to help companies benefit from both.

Discuss