SQL Server Case Sensitive Collations and DMVs

By: K. Brian Kelley   |   Updated: 2013-03-22   |   Comments (3)   |   Related: > Dynamic Management Views and Functions

Problem

We recently instituted a new database and it has a case-sensitive collation due to the application requirements. We have several monitoring scripts that check the health of our SQL Servers and their databases by querying particular dynamic management views (DMVs) and they were working just fine. However, when we instituted the scripts against this new database, they failed. We eventually figured out that one of our DBAs had queried sys.objects, but did so as SYS.OBJECTS and that's what caused the failure. I know that the case sensitivity applies to user tables and stored procedures, but does the case sensitivity apply to SQL Server "internal" objects as well?  Check out this tip to learn more.

Solution

The short answer is yes, it does. When inside a database with a case-sensitive collation, every object functions under the same rules. To see this, let's create a test database with a binary collation, meaning among other things, case-sensitivity is enforced.

CREATE DATABASE [CaseSensitive]
COLLATE Latin1_General_BIN;
GO 
USE [CaseSensitive];
GO 

SQL Server Information Schema Views

If you're familiar with information schema views, you probably know they were the preferred way to get metadata on objects up through SQL Server 2000. As of SQL Server 2005, we were to use the DMVs provided with SQL Server. Information schema views in SQL Server have always been all uppercase. In a case insensitive database, the fact that the schema and the object name are all uppercase doesn't matter. However, in a case sensitive database, it does matter. To see this, execute both of these queries:

-- This won't work
SELECT table_name, table_type FROM information_schema.tables;
GO 
-- This will
SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;
GO

When you ran the first query, you should have received this error:

This is because the object name is actually INFORMATION_SCHEMA.TABLES. So if you don't reference the object correctly, SQL Server won't find it. If you are using information schema views in any of your scripts and you have case sensitive databases, ensure that the object names as well as any columns you specify are in all uppercase. Otherwise, you'll get an error.

SQL Server Dynamic Management Views and Functions

Surely, since DMVs and Dynamic Management Functions (DMFs) are built-in to SQL Server and don't follow any particular SQL standard, it should not matter how you call these objects, right? SYS.OBJECTS and sys.objects should both work, right? They do if you're in a case insensitive collation for the database. However, if the database is set to a case-sensitive collation, you need to refer to DMVs and DMFs in all lowercase. To see this, execute the following queries in the CaseSensitive database:

-- This won't work
SELECT [name], type_desc FROM SYS.OBJECTS;
GO 
-- This will
SELECT [name], type_desc FROM sys.objects;
GO

As you might expect, DMFs suffer the same fate. As a result, this fails:

-- This won't work in a case-sensitive collation DB
SELECT q.execution_count, t.text
FROM sys.Dm_Exec_Query_Stats q
CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;
GO
-- This will work in a case-sensitive collation DB
SELECT q.execution_count, t.text
FROM sys.dm_exec_query_stats q
CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;
GO 

However, if you were to run the same query in the master database, everything is fine, assuming you didn't set the collation on your master database to a case sensitive collation. This query demonstrates such:

-- Switch to master (assuming a default, case-insensitive collation)
-- And it works just fine
USE master;
GO 
SELECT q.execution_count, t.text
FROM sys.Dm_Exec_Query_Stats q
CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t;
GO

The Moral of the Story

When you write scripts, especially ones that will run automatically, pay attention to the case of the object and column names. The majority of the time how you name the objects won't matter because you'll be dealing with case insensitive collations. However, the rare cases that you do have case sensitive collation databases, if you don't pay attention to case you'll get burned. The problem is that since you're so used to however you name the objects working, you'll likely be scratching your head for a bit trying to figure out why your tried and true scripts are suddenly broken. A simple rule to follow is to use all UPPERCASE when dealing with information schema views (if you still use them) and to use all lowercase when you use DMVs and DMFs.

Next Steps

• Learn how to determine database collation by using T-SQL.
• Understand the trick to cross database joins when the databases use different collations.
• Know the method to force a case sensitive comparison when you need it, even if the database is case insensitive.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips