|
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/bestpractices2005/collatedatabasedefault/
Collation Precedence (Transact-SQL)
|
