|
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 collation conflict - sql collation - sql server collate
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)
------------
/******
Alter Database Collation - Change Database Collation
NOTE: only the default collation is changed - other collations remain in DB.
Tables must be rebuilt one by one for new collation.
******/
ALTER DATABASE YourDBName COLLATE SQL_Latin1_General_CP1_CI_AS
------------
-- 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
-- Database populate table
-- T-SQL insert select
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.
*/
------------
|
