Tip: COLLATE for “Cannot resolve collation conflict” error

Some data professionals are VERY well versed with collation, and some never ever have to touch the concept. That second group tends works in shops that leave everything on default.  A huge portion of my career has been spent as a member of that “Custom collation? What custom collation?” crowd, and so I rarely get an error like this:

Msg 451, Level 16, State 1, Line 2
Cannot resolve collation conflict for column 1 in SELECT statement.

But for the few occasions I do, it’s good to know about the COLLATE clause. In today’s example, a single column of a table has a different collation set, like this:

CREATE TABLE Allthings
    (
      thingName NVARCHAR(300) COLLATE French_CS_AS ,
      thingType NVARCHAR(100) ,
      thingDeliciousness TINYINT
    );

INSERT  INTO Allthings
VALUES  ( 'Fancy wine', 'Bordeaux', 5 );

If we run this statement, we’ll totally get that error from above, because we’re trying to concatenate different collations, and SQL can’t decide which one to use:

SELECT  'This wine: ' + thingName + ' which is a ' + thingtype
        + ' has a deliciousness index of '
        + CAST(thingDeliciousness AS VARCHAR(10))
FROM    Allthings;

The solution, in this case, is simple enough: decide which collation should reign supreme, and convert the outlier to that collation in your query.

SELECT  'This wine: ' + thingName + ' which is a ' + thingtype COLLATE French_CS_AS
        + ' has a deliciousness index of '
        + CAST(thingDeliciousness AS VARCHAR(10))
FROM    Allthings;

To read up more on collation:

• Collations and Unicode Support
• COLLATE
• Collation Precedence

Happy days,
Jen McCown
MidnightDBA.com/Jen