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

Take our Introduction to SQL Training course for free.

See the Course Outline and Register

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

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, XML, Windows, Java, Adobe, HTML5, JavaScript, Angular, and much more. Check out our complete course catalog.

Categories

Courses

Author: Nat Dunn

Nat Dunn founded Webucator in 2003 to combine his passion for technical training with his business expertise and to help companies benefit from both. His previous experience was in sales, business and technical training, and management. Nat has an MBA from Harvard Business School and a BA in International Relations from Pomona College.

Discuss