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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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