|
Execute the following T-SQL example
scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate collation conflicts and resolutions by COLLATE DATABASE_DEFAULT. SQL Server sample databases AdventureWorks & AdventurWorks2008 are used in the demos.
-- Cannot resolve collation conflict for - SQL Server collation conflict
Typical string operations collate database default usages:
equal match, concatenation & IN match
... WHERE ( or ON) a.StringColAlpha = b.StringColOmega COLLATE DATABASE_DEFAULT
SELECT NewString = a.StringColAlpha + b.StringColOmega COLLATE DATABASE_DEFAULT ...
... WHERE ap.Color COLLATE DATABASE_DEFAULT IN (SELECT dp.Color FROM Product dp)
------------
-- REPLACE string operation with collation conflict
SELECT REPLACE(o.TYPE + o.NAME, ' ','~')
FROM sys.objects o
/*
Msg 446, Level 16, State 9, Line 2
Cannot resolve collation conflict for replace operation.
*/
-- SQL collate fixes the collation conflict issue between the TYPE & NAME columns
SELECT REPLACE(o.TYPE + o.NAME COLLATE DATABASE_DEFAULT, ' ','~')
FROM sys.objects o
-- (658 row(s) affected)
------------
-- EQUAL TO operation with collation conflict - Cannot Resolve Collation Conflict
SELECT NAME
FROM sys.objects
WHERE NAME NOT IN (SELECT TYPE
FROM sys.objects)
/*
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
*/
-- SQL COLLATE DATABASE_DEFAULT fixes issue
SELECT NAME
FROM sys.objects
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN (SELECT TYPE
FROM sys.objects)
-- (658 row(s) affected)
------------
-- UNICODE / NCHAR functions - Use them instead of ASCII / CHAR for unicode
-- The Hungarian letter is UNICODE (2 bytes required)
SELECT UNICODE(N'Ő'), ASCII (N'Ő'), CONVERT(BINARY(2), N'Ő')
-- 336 79 0x5001
SELECT UNICODE('O'), ASCII ('O'), CONVERT(BINARY(2), 'O')
-- 79 79 0x4F00
SELECT CONVERT(CHAR(1), N'Ő') -- O - information is lost in conversion
SELECT CONVERT(NCHAR(1), N'Ő') -- Ő - information is kept in conversion
----------
-- Finding the code page of a collation
select name, CodePage = COLLATIONPROPERTY(name, 'CodePage'),
description
from sys.fn_HelpCollations()
where name = 'SQL_Latin1_General_CP1_CI_AS'
go
/* name CodePage description
SQL_Latin1_General_CP1_CI_AS 1252 Latin1-General, case-insensitive,
accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data */
----------
-- A column must be the same collation
SELECT City = 'New York City' COLLATE SQL_Latin1_General_CP1253_CI_AI
UNION
SELECT City = 'Los Angeles' COLLATE SQL_Latin1_General_CP1253_CI_AS
/* Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1253_CI_AS" and "SQL_Latin1_General_CP1253_CI_AI" in the UNION operation. */
------------
/******
* Alter Database Collation - Change Database Collation
* NOTE: only the default collation is changed - other collations remain in DB.
* Tables must be rebuilt (create new table, move data) one by one for new collation.
* ALTER TABLE can be used to change the collation of a column.
******/
ALTER DATABASE YourDBName COLLATE SQL_Latin1_General_CP1_CI_AS
------------
-- SQL Server change collation of a column to Case Sensitive (CS)
USE AdventureWorks2008;
ALTER TABLE Production.Product ALTER COLUMN Color nvarchar(15)
COLLATE SQL_Latin1_General_CP1_CS_AS
------------
/******
Collation conflict can occur when tempdb collation different from current database.
******/
-- T-SQL check collation of a column - SQL Server sample database AdventureWorks2008
USE AdventureWorks2008;
SELECT ColumnName=name, Collation=collation_name
FROM sys.columns
WHERE name='Color'
AND OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'Product'
AND SCHEMA_NAME(schema_id)='Production')
-- Color SQL_Latin1_General_CP1_CI_AS
------------
-- SQL Server list all columns with different collation from database default collation
USE AdventureWorks2008;
CREATE TABLE TestOnly(
text varchar(128) COLLATE SQL_Latin1_General_CP1253_CI_AI );
GO
SELECT
ObjectType = o.type,
SchemaName = SCHEMA_NAME(schema_id),
Objectname = o.name,
ColumnName = c.name,
CollationName = c.collation_name
FROM sys.objects o
INNER JOIN sys.columns c ON c.object_id = o.object_id
WHERE c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'COLLATION')
AND o.type IN('U', 'V')
/*
ObjectType SchemaName Objectname ColumnName CollationName
U dbo TestOnly text SQL_Latin1_General_CP1253_CI_AI */
DROP TABLE TestOnly
------------
-- Implicit conversion collation conflict in string concatenation
create table #Standard(text varchar(16))
create table #Mohawk (text varchar(16) collate Mohawk_100_CI_AS_KS_WS)
insert into #Standard values('SQLUSA')
insert into #Mohawk values('Kanien''kehá:ka')
select s.text +' '+ m.text from #Standard s cross join #Mohawk m
go
/* Msg 457, Level 16, State 1, Line 7
Implicit conversion of varchar value to varchar cannot be performed because the
collation of the value is unresolved due to a collation conflict.
*/
select s.text +' '+ m.text COLLATE DATABASE_DEFAULT
from #Standard s cross join #Mohawk m
go
/* SQLUSA Kanien'kehá:ka */
drop table #Standard
drop table #Mohawk
------------
-- SQL Server collation conflict on SELECT COUNT DISTINCT
USE tempdb;
SELECT ProductID, Color=Color COLLATE SQL_Latin1_General_CP1253_CI_AI
INTO Product
FROM AdventureWorks.Production.Product
GO
SELECT ColorCount=COUNT(DISTINCT Color)
-- SELECT ColorCount=COUNT(DISTINCT Color COLLATE DATABASE_DEFAULT)
FROM ( SELECT Color=Color
FROM AdventureWorks2008.Production.Product
UNION ALL
SELECT Color FROM Product ) x
/* Msg 446, Level 16, State 11, Line 1
Cannot resolve collation conflict for DISTINCT operation. */
------------
------------
SQL collation conflict - Create demo table in tempdb - collation database default
------------
USE tempdb
GO
SET NOCOUNT ON;
-- Create a table with some columns of different collation
CREATE TABLE [dbo].[Product](
[ProductID] [int] NOT NULL,
[Name] [nvarchar](125) NOT NULL,
[ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
[MakeFlag] [bit] NOT NULL CONSTRAINT [DF_Product_MakeFlag] DEFAULT ((1)),
[FinishedGoodsFlag] [bit] NOT NULL CONSTRAINT [DF_Product_FinishedGoodsFlag] DEFAULT ((1)),
[Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[SafetyStockLevel] [smallint] NOT NULL,
[ReorderPoint] [smallint] NOT NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Weight] [decimal](8, 2) NULL,
[DaysToManufacture] [int] NOT NULL,
[ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductSubcategoryID] [int] NULL,
[ProductModelID] [int] NULL,
[SellStartDate] [datetime] NOT NULL,
[SellEndDate] [datetime] NULL,
[DiscontinuedDate] [datetime] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Product_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO
-- SQL Server database populate table - T-SQL insert select
-- SQL Server sample database AdventureWorks
INSERT Product
SELECT * FROM AdventureWorks.Production.Product
GO
-- The following IN set operation fails due to collation conflict
-- SQL collate sql_latin1_general_cp1_ci_as - transact sql collate
SELECT TOP 100 ap.*
FROM AdventureWorks.Production.Product ap
WHERE ap.Color IN (SELECT dp.Color
FROM Product dp)
GO
/* Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI"
and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
*/
-- Inserting COLLATE DATABASE_DEFAULT before IN results in successful query
-- Microsoft SQL Server T-SQL TOP function
SELECT TOP 100 ap.*
FROM AdventureWorks.Production.Product ap
WHERE ap.Color COLLATE DATABASE_DEFAULT IN (SELECT dp.Color
FROM Product dp)
GO
/* The advantage of using COLLATE DATABASE_DEFAULT is that it works without
using specific hard-to-remember collation name.
String append and string compare of different collations require the use of
COLLATE clause. */
-- The following JOIN fails due to collation conflict
SELECT TOP 15 ap.*
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color = dp.Color
GO
-- Msg 468, Level 16, State 9, Line 1
-- Error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
-- This JOIN succeeds with specific collation
SELECT TOP 15 ap.*
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color = dp.Color COLLATE SQL_Latin1_General_CP1_CI_AS
GO
-- The following JOIN also succeeds
SELECT TOP 15 ap.*
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color COLLATE SQL_Latin1_General_CP1_CI_AS = dp.Color
GO
-- MSSQL collation database property check
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
GO
-- SQL_Latin1_General_CP1_CI_AS
-- This JOIN with COLLATE database default works with no need for specific collation
SELECT TOP 15 ap.*
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color = dp.Color COLLATE database_default
GO
-- This JOIN also works with collate database default on the left hand side of =
SELECT TOP 15 ap.*
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color COLLATE database_default = dp.Color
GO
-- This query fails in the SELECT string concatenation
SELECT TOP 20 [Colors] = 'Color: ' + ap.Color +' '+ dp.color
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color COLLATE database_default = dp.Color
GO
-- Msg 451, Level 16, State 1, Line 1
-- Cannot resolve collation conflict for column 1 in SELECT statement.
-- This query works by applying collate database default after the second column
SELECT TOP 20 [Colors] = 'Color: ' + ap.Color +' '+ dp.color COLLATE database_default
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color COLLATE database_default = dp.Color
GO
-- This query also works using collate database default after the first column
SELECT TOP 20 [Colors] = 'Color: ' + ap.Color COLLATE database_default +' '+ dp.color
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color COLLATE database_default = dp.Color
GO
-- This query fails in the SELECT string concatenation
-- It also fails in the WHERE filter
SELECT TOP 20 ProductName=dp.Name,
[Colors] = 'Color: ' + ap.Color +' '+ dp.color
FROM AdventureWorks.Production.Product ap
INNER JOIN dbo.Product dp
ON dp.ProductID = ap.ProductID
WHERE ap.Color = dp.Color
GO
-- The following queries succeed with double use of COLLATE DATABASE_DEFAULT
SELECT TOP 20 ProductName=dp.Name,
[Colors] = 'Color: ' + ap.Color +' '+ dp.color COLLATE database_default
FROM AdventureWorks.Production.Product ap
INNER JOIN dbo.Product dp
ON dp.ProductID = ap.ProductID
WHERE ap.Color COLLATE database_default = dp.Color
GO
-- SQL collate select - SQL collate where
SELECT TOP 20 ProductName=dp.Name,
[Colors] = 'Color: ' + ap.Color COLLATE database_default +' '+ dp.color
FROM AdventureWorks.Production.Product ap
INNER JOIN dbo.Product dp
ON dp.ProductID = ap.ProductID
WHERE ap.Color = dp.Color COLLATE database_default
GO
-- SQL collation table - SQL collation column
SELECT ColumnName = name, collation_name
FROM AdventureWorks.sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM AdventureWorks.sys.objects
WHERE type = 'U'
AND name = 'Address')
AND collation_name is not null
/* Results
ColumnName collation_name
AddressLine1 SQL_Latin1_General_CP1_CI_AS
AddressLine2 SQL_Latin1_General_CP1_CI_AS
City SQL_Latin1_General_CP1_CI_AS
PostalCode SQL_Latin1_General_CP1_CI_AS
*/
-- Collation information - SQL database collation
USE AdventureWorks;
SELECT databasepropertyex(db_name(),'collation') AS Collation
GO
-- Result: SQL_Latin1_General_CP1_CI_AS
-- SQL table column collations
-- SQL information_schema views
SELECT TableSchema = TABLE_SCHEMA,
TableName = TABLE_NAME,
TableColumn = COLUMN_NAME,
ColumnCollation = COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
ORDER BY TableSchema,
TableName
GO
-- SQL collation case sensitive: "CS" suffix in name
/* Partial results
TableSchema TableName TableColumn ColumnCollation
Sales CreditCard CardType SQL_Latin1_General_CS_AS
Sales CreditCard CardNumber SQL_Latin1_General_CS_AS
Sales Currency CurrencyCode SQL_Latin1_General_CS_AS
*/
------------
-- ALL SQL related collations
------------
USE MASTER;
-- List all sql related collations
SELECT name, description = LEFT(description, 30)
FROM sys.fn_HelpCollations()
WHERE name LIKE '%SQL%'
GO
/* Partial results
name description
SQL_Latin1_General_CP1253_CI_AI Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 124 on Code Page 1253 for non-Unicode Data
SQL_Latin1_General_CP1253_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 114 on Code Page 1253 for non-Unicode Data
SQL_Latin1_General_CP1253_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 113 on Code Page 1253 for non-Unicode Data
*/
------------
-- COLLATION conflict between operands in the REPLACE function
CREATE VIEW vCollate
AS
SELECT Today = REPLACE(CONVERT(VARCHAR,getdate(),120)
COLLATE SQL_Latin1_General_CP1_CS_AS,
'-' COLLATE SQL_Latin1_General_CP1_CI_AS,
'/')
GO
/*
Msg 468, Level 16, State 9, Procedure vcollate, Line 7
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
"SQL_Latin1_General_CP1_CS_AS" in the replace operation.
*/
------------
-- Related article link:
SQL Server Collation Fundamentals
|
