Let the default collation of the SEDE databases match the dominant collation used for the nvarchar columns

Question

Today I was working on a SEDE query when I was met with a somewhat unexpected error message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The joy of collation. This is the repro:

Create table #foo (bar nvarchar(25));

insert into #foo values ('haskell');

select * 
from tags t
inner join #foo f on f.bar = t.tagname

A quick inspection of the server and database collation

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));
SELECT name, collation_name FROM sys.databases;

revealed that the collation is set to SQL_Latin1_General_CP1_CI_AS.

Turns out the nvarchar columns of the table are the culprit:

TagName    SQL_Latin1_General_CP1_CS_AS

The work around for this issue is to be explicit about the collation, either in the create table statement or in the where/join clause.

But if there are no side-effects I didn't anticipate for wouldn't it make sense to create each database with the default collation of SQL_Latin1_General_CP1_CS_AS? That would be one confusion less for unexpecting users of SEDE.

It would need a small change in sp_Refresh_Database.sql:

Exec('Create Database [' + @TempDBName + ']
      ON  PRIMARY (NAME = N''' + @DBName + ''', FILENAME = N''' + @DataPath + @BaseFileName + '.mdf'' , SIZE = 4096KB , FILEGROWTH = 102400KB)
      LOG ON (NAME = N''' + @LogFileName + ''', FILENAME = N''' + @LogPath + @BaseFileName + '_log.ldf'' , SIZE = 1024KB , FILEGROWTH = 10%)
      COLLATE SQL_Latin1_General_CP1_CS_AS');

Can this be considered for the next push of functionality to SEDE?

Answer 1

While this change would solve the issue with joining on #temp table columns, the specific query referenced in the question would still break, but in a different way:

Msg 208, Level 16, State 1
Invalid object name 'tags'.

This is because the database-level collation enforces how entity names are checked against the metadata. With a CS collation, those checks are case sensitive.

Since you're probably not the only person who has typed tags instead of Tags, I suspect that will break a lot of existing queries out there, while changing the database collation won't actually fix any that are currently working. Many SEDE queries would have to be updated to reference object and column names in their explicit case just to keep working.

Collation is a nasty business and, unfortunately, there isn't a silver bullet.

Hopefully, recent work has reduced the need for #temp tables a little bit, but I bet "joining to #temp tables" - even if it worked flawlessly without explicit collate clauses - is a far lower frequency than "type entity names however I feel like." :-)