Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

SQL Server Case Sensitive Collations and DMVs

MSSQLTips author K. Brian Kelley By:   |   Read Comments (3)   |   Related Tips: More > 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:

Information Schema Views

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


Last Update: 3/22/2013


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, May 23, 2013 - 3:26:45 PM - K. Brian Kelley Read The Tip

Bodhi, Microsoft recommended to use catalog views here:

http://msdn.microsoft.com/en-us/library/ms189082(v=sql.90).aspx

Typically they are lumped in with the phrase "dynamic management view" or DMVs.

Note that's from the SQL Server 2005 Books On-line.


Thursday, May 23, 2013 - 9:08:30 AM - Jorge Segarra Read The Tip

@Bodhi - DMVs tend to provide more information than the information_schema views. By having us use DMVs in favor of direct system objects it also allows them to make changes while minimizing impact to scripts you may have that key off DMVs. Also as noted here (http://stackoverflow.com/questions/4195064/sql-server-difference-between-catalog-views-information-schema-views-vs-dmvs, DMVs give you information from internal SQL structures as opposed to objects from disk. DMVs also give you more insights now (i.e. IO stats, pefmon counters from within SQL Server, etc.)


Tuesday, May 07, 2013 - 5:05:59 PM - Bodhi Densmore Read The Tip

"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. "

Where did Microsoft tell us to stop using the information_schema views in favor of the DMVs?




 
Sponsor Information