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?


You must log in to answer this question.

Browse other questions tagged .