I thought my database was collation agnostic

After having written several articles and posted them on my web site at www.complianceabc.com, I noticed that while the intent was to help, they had an underlying tone that I didn't like. All of the articles were my way of venting about things that I had seen in my travels that had made my life difficult. Essentially I was ranting and raving about things that annoyed me.

I've decided that I don't want to be that guy, I'd rather be the guy that helps, which was always my intention. A funny thing, on my way to the office I saw written on the back windshield of the car in front of me, the words, "pissing on my lawn does not make your lawn greener". While this may have many interpretations, I associate it with the ranting guy, the guy I don't want to be.

I'm not going to tell you to do anything or to not do anything. I'm just going to tell you what happened to me and how I got past it. Also this was not a PCS customer as the situation happened somewhere else. Working backwards, a customer had an issue with our application and sent me a copy of their database. I restored the database on our SQL Server and attempted to start the application to reproduce the customer's errors at our office. I was not able to start the application. 

This frustrated me because I know the application works. The first thing I always do is look in the log file for errors, but what I saw was a new and surprised me. 

The Error

The error in the log looked like this.

"Msg 468, Level 16, State 9, Procedure myStoredProcedure , Line 54

At first glance I was perplexed, but after a little digging the answer became obvious. When SQL Server is installed, there are many collations available.  owever, the primary characteristic that our application needs is case insensitivity. I don't know that any of the other attributes of collation really matter as far as the application is concerned. They might, but no other collation has ever been tested. 

Every time I have ever installed SQL Server, this collation seems to be the default "SQL_Latin1_General_CP1_CI_AS". Then again my regional settings on the server are usually Eastern Time zone and US English. To break the collation set down:

• Latin1 makes the server treat strings using charset Latin 1, basically ASCII
• CI case insensitive comparisons so 'ABC' would equal 'abc'
• AS accent sensitive, so 'ü' does not equal 'u'

After SQL Server 2005, it seems that the SQL group is deprecating the SQL collations and recommending Windows collations. This technet article from Microsoft explains what is happening.

Clearly the error is stating that there is a conflict between two collations. One would think that "SQL_Latin1_General_CP1_CI_AS " and "Latin1_General_CP1_CI_AS ", should be identical with the exception that one is a SQL collation and the other is a Windows collation.  The customer must have installed his/her SQL server using the Windows collation. When he/she installed our application, the resulting database used the SQL Server's collation, which is how the application is designed. Also, it is important to note here that the customer did not experience this issue. Collation was not their problem, they had a different problem, which I was still unable to test. 

Still this does not explain why I received the error. Per the title of this article, our application does not impose any particular collation, but we do require that the customer uses a collation that supports case insensitivity. So why, then, would a stored procedure within our application that does not specify anything about collation throw a collation exception? Many of our stored procedures use temp tables which reside in tempdb. That is, some of our stored procedures issue the command create table #myTempTable … 

When the customer's database which uses the collation "Latin1_General_CP1_CI_AS" was restored on our SQL server which uses the collation "SQL_Latin1_General_CP1_CI_AS" all temp tables created by stored procedures by default used the SQL Server's collation "SQL_Latin1_General_CP1_CI_AS" because tempdb inherits the SQL Server's default collation. The collation of the temp tables in tempdb did not match the database's collation of "Latin1_General_CP1_CI_AS".

Solution

My first reaction was to change the collation of my SQL Server, but this is a bad idea. If I did so, all of the other databases on that SQL Server would be dropped, or at least no longer referenced by the master database and no longer present in sys.databases. 

Then I thought it might be easier to change the collation of the database. This proved to be tricky because the foreign keys prevented me from changing tables that were part of the dependency chain. I would have needed to drop all the foreign keys and any other constraints and then re-create them. 

So to get around the problem we installed another SQL instance with the "Latin1_General_CP1_CI_AS" collation, restored the database on the new instance, pointed the application to that database, and we were done. This however is not practical because who knows from where the next backup will come. 

The Real Solution

The real solution was to locate the stored procedures and stored functions that create temp tables and append this string, "COLLATE DATABASE_DEFAULT", to the definition or each char, varchar, nchar, and nvarchar type 

Notice that we are still collation indifferent. We don't specify a particular collation but rather specify in the creation of the temp tables to use the collation of the database and not the collation of the SQL Server. 

For example:

    CREATE TABLE #myTempTable 
(
FooOne int,
FooTwo varchar(50) COLLATE DATABASE_DEFAULT,
FooThree nvarchar(50) COLLATE DATABASE_DEFAULT,
FooFour bit
)

You would also need to employ similar techniques for cross database queries. 

Also, as a side note, know with great benefit that CTE's (Common Table Expressions) are immune to this issue. You can test any of the AdventureWorks CTE examples to see for yourself. 

Hope you enjoyed my wild ride and of course I welcome your comments. If you have found a better way to achieve this, please share.