SQL Error: Cannot resolve collation conflict for DISTINCT operation

I'd never run into this one before and am not likely to again, but the fix is pretty simple and worth posting for my own reference (and I hope it helps anyone googling for it too).

The problem occurs in SQL Server 2005 or SQL Server 2008 when you try and select a DISTINCT range of values from a sub-query that uses a UNION or UNION ALL to union two columns with different collations. The problem may occur if the collations are set differently at a database level while unioning two tables from different databases, and also at the column level within the same database. As with other "Cannot resolve collation conflict for XXX operation" errors, the fix is the same - convert the offending columns to the same collation using COLLATE DATABASE_DEFAULT:

--Step 1: create two similar tables with columns with different collations
DECLARE @temp1 TABLE (
    [Test1] NVARCHAR(100) COLLATE SQL_Latin1_General_Cp1250_CS_AS
)
DECLARE @temp2 TABLE (
    [Test2] NVARCHAR(100) COLLATE Icelandic_CS_AS
)

--Step 2: put in some values
INSERT INTO @temp1 VALUES(N'Falcon')
INSERT INTO @temp1 VALUES(N'Eagle')
INSERT INTO @temp1 VALUES(N'Tomcat')

INSERT INTO @temp2 VALUES(N'Mirage')
INSERT INTO @temp2 VALUES(N'Viggen')
INSERT INTO @temp2 VALUES(N'Harrier')

--Step 3: THIS WILL CAUSE A "Cannot resolve collation conflict for DISTINCT operation" ERROR
--Comment the following out to run step 4
--get distinct values from both tables, joined using UNION ALL in a subquery
SELECT DISTINCT [Test]
FROM (
      SELECT [Test] = [Test1] FROM @temp1
      UNION ALL
      SELECT [Test2] FROM @temp2
     ) I

--Step 4: the fix is to convert the columns to same collation using COLLATE DATABASE_DEFAULT
SELECT DISTINCT [Test]
FROM (
      SELECT [Test] = [Test1] COLLATE DATABASE_DEFAULT FROM @temp1
      UNION ALL
      SELECT [Test2] COLLATE DATABASE_DEFAULT FROM @temp2
     ) I
 

Tags: , ,

«February»
SunMonTueWedThuFriSat
31123456
78910111213
14151617181920
21222324252627
28123456
78910111213