DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to resolve string concatenation collation conflict?

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to create tables with different collation and construct queries with collation conflict and collate conflict resolution:

USE tempdb

GO

 

-- SQL select into create table

SELECT ContactID,

       FirstName,

       LastName

INTO   Contact_AS_Accent_Sensitive

FROM   AdventureWorks.Person.Contact

GO

-- (19972 row(s) affected)

 

-- Select into create table with different collation

SELECT ContactID,

       FirstName = FirstName COLLATE SQL_Latin1_General_CP1_CI_AI,

       LastName = LastName COLLATE SQL_Latin1_General_CP1_CI_AI

INTO   Contact_AI_Accent_Insensitive

FROM   AdventureWorks.Person.Contact

GO

-- (19972 row(s) affected)

 

-- String concatenation collation conflict in SELECT item concatenation

-- SQL Server string concatenation with the + operator

SELECT [Name] = s.FirstName + ' ' + i.LastName

FROM     Contact_AS_Accent_Sensitive s

         JOIN Contact_AI_Accent_Insensitive i

           ON s.ContactID = i.ContactID

 /* Msg 451, Level 16, State 1, Line 1

Cannot resolve collation conflict for column 1 in SELECT statement.

*/

        

-- String concatenation collation conflict in ORDER BY

SELECT [Name] = s.FirstName + ' ' + i.LastName

FROM     Contact_AS_Accent_Sensitive s

         JOIN Contact_AI_Accent_Insensitive i

           ON s.ContactID = i.ContactID

ORDER BY [Name]

GO

/* Msg 451, Level 16, State 1, Line 5

Cannot resolve collation conflict for column 1 in ORDER BY statement.

*/

 

 

-- Collation conflict resolved with COLLATE DATABASE_DEFAULT in SELECT

SELECT [Name] = s.FirstName + ' ' + i.LastName COLLATE DATABASE_DEFAULT

FROM     Contact_AS_Accent_Sensitive s

         JOIN Contact_AI_Accent_Insensitive i

           ON s.ContactID = i.ContactID

ORDER BY [Name]

GO

/* Partial results

 

Name

A. Leonetti

A. Scott Wright

A. Wright

Aaron Adams

*/

 

-- Collation conflict resolved with specific collation in SELECT

SELECT   [Name] = s.FirstName + ' ' + i.LastName COLLATE SQL_Latin1_General_CP1_CI_AI

FROM     Contact_AS_Accent_Sensitive s

         JOIN Contact_AI_Accent_Insensitive i

           ON s.ContactID = i.ContactID

ORDER BY [Name]

GO

 

/* Partial results

 

Name

A. Leonetti

A. Scott Wright

A. Wright

Aaron Adams

*/

-- Cleanup

DROP TABLE tempdb.dbo.Contact_AS_Accent_Sensitive

DROP TABLE tempdb.dbo.Contact_AI_Accent_Insensitive

------------

 

Related articles:

http://www.sqlusa.com/bestpractices/changecollation/

http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/

Collation Precedence (Transact-SQL)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE