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
Turns out the nvarchar columns of the table are the culprit:
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?