Oh Yeah? Collate This!

So I decide to start a blog, and I figure my first blog entry will be a really cool and helpful stored procedure that people can use to produce T-SQL code that creates and populates sample tables, thus enabling them to paste this code into forum messages when asking for help. (How's that for a long bloated first sentence?) The concept of putting together this procedure was easy enough, but the reality ended up being something different.

I wanted to create something that would be as non-invasive as possible. I didn’t want to force a person to create my procedure in his/her own database. And then I noticed SQL will allow you to create a temporary stored procedure, just by putting a ‘#’ in front of the procedure’s name. Great! Looked like the answer.

But then I ran up against, of all things, collation problems. I was using AdventureWorks as a database to test out my procedure. Its collation is Latin1_General_CI_AS. However the collation of my server is SQL_Latin1_General_CP1_CI_AS. This created some unexpected bumps in the road.

You can see some of these potholes for yourself. First find out tempdb’s collation (which is inherited from the server’s collation when tempdb is created):

select databasepropertyex('tempdb','collation')
--My system returns SQL_Latin1_General_CP1_CI_AS

Now create a test database with a collation different from tempdb (I’m creating one below with a case-sensitive accent-insensitive collation just to be really contrarian) and (this is important!) make this new test database your current database:

if db_id('BSTest') is not null drop database BSTest
go
create database BSTest collate Latin1_General_CS_AI
go
use BSTest
go

Now, with BSTest as our current database context, try creating a laughably simple temporary procedure:

create procedure #TempProc1
as
declare @var nchar(1)
set @var='X'
if @var='X' print 'Duh!'

You will get the following error: Cannot resolve the collation conflict between "Latin1_General_CS_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Your collation names may differ).

Apparently there’s a problem comparing the variable @var to the constant of 'X'. It seems that @var inherits the collation of tempdb (since we’re creating the procedure there), but the constant inherits the collation of the current database context that we’re in (BSTest). They are incompatible.

Well, I guess that kinda sorta in a weird way makes a teensy-weensy bit of sense. I could certainly re-code the IF statement like so and it would work:

if @var = '' collate SQL_Latin1_General_CP1_CI_AS print 'Duh!'

But I can only do that because I know exactly what tempdb’s collation is. Alternately I can use database_default to convert @var to the collation of the current database’s (BSTest’s) collation:

if @var collate database_default = 'X' print 'Duh!'

Ultimately, for whatever reason, I decided to take care of it by doing something like the following instead:

create procedure #TempProc1
as
declare @var nchar(1)
       ,@emptystring nchar(1)
set @var=''
set @emptystring=''
if @var=@emptystring print 'Duh!'

Okay, that worked. But then I ran up against something like this:

create procedure #TempProc2
as
declare @var nchar(1)
if @var is null print 'Duh!'

The same type of collation error occurs! It appears that not all NULLs are created equal. The NULL in the code above is apparently a NULL with BSTest’s collation.

Swell. Now what? Well, how about the following? Seems straightforward enough:

create procedure #TempProc2
as
declare @var nchar(1)
       ,@emptystring nchar(1)
set @emptystring=''
if coalesce(@var,@emptystring)=@emptystring print 'Duh!'

Another error? Jeez! Now come on! What’s wrong with that? I’m comparing apples to apples, aren’t I? I get the same kind of collation error message, but now it says: Cannot resolve the collation conflict between ... in the is not operation. Huh? What IS NOT operation are they talking about?

I find out (thanks to my pal Hunchback at the MSDN T-SQL Forum) that SQL converts COALESCE to a CASE function behind the scenes. You can see this if you look at a query plan for the following:

select coalesce(Name,'') from sys.databases

If you look at the Properties of the Compute Scalar icon in the plan, you will see that it is essentially computing the following:

CASE WHEN [Name] IS NOT NULL THEN [Name] ELSE '' END

So that explains why the collation error message has a problem with an IS NOT operation. Luckily, if I use ISNULL instead of COALESCE, everything finally works fine. Whew!

Finally I had problems mixing variables and constants in SELECT commands:

create procedure #TempProc3
as
declare @var nchar(1)
set @var='X'
select 'Prefix'+@var
select 'Row#1' union all select @var

Again, the above throws an error on both SELECT commands, citing a collation conflict. I ended up solving this kind of problem by adding COLLATE database_default to the SELECT column:

create procedure #TempProc3
as
declare @var nchar(1)
set @var='X'
select 'Prefix'+@var collate database_default
select 'Row#1' collate database_default union all select @var

Note that I can specify the COLLATE clause in any of the SELECTs of a UNIONed query. It doesn’t matter which one. Just being specific about the collation in one SELECT controls the collation of the entire UNIONed query. Again, note that the database_default here is the database that has context (i.e. BSTest).

You can see all of this demonstrated here:

create procedure #TempProc4
as
declare @var nchar(1)
set @var='X'
--Create 4 temp files
select StringConstant='Whatever' into #Junk1
select StringVar=@var into #Junk2
select StringCombo='Whatever'+@var collate database_default into #Junk3 
select StringUnion='Whatever' collate database_default
into #Junk4 
union all 
select @var 
--Now let's look at what collation was created for the columns
--and compare them to the collations of our databases
select Name,Collation_Name 
from tempdb.sys.columns 
where Name like 'String%'
union all
select 'tempdb',databasepropertyex('tempdb','collation')
union all
select db_name(),databasepropertyex(db_name(),'collation')
go
exec #TempProc4
/*
Name            Collation_Name
--------------  ----------------------------
StringCombo     Latin1_General_CS_AI
StringConstant  Latin1_General_CS_AI
StringUnion     Latin1_General_CS_AI
StringVar       SQL_Latin1_General_CP1_CI_AS
tempdb          SQL_Latin1_General_CP1_CI_AS
bstest          Latin1_General_CS_AI
*/

So I think the lesson here is to use temporary procedures with caution… or use them if you’re a glutton for punishment. Are they worth it? They seem to be a big pain in the posterior, but this whole experience did make me learn something. I hope you learned something too.