|
Execute the following Microsoft SQL Server Transact-SQL script in SSMS Query Editor demonstrat the use of COLLATE for collation conflict resolution.
-- SQL Server collation conflict - T-SQL collate - T-SQL COLLATE DATABASE_DEFAULT
USE tempdb
GO
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
-- Populate new table
INSERT Product
SELECT *
FROM AdventureWorks.Production.Product
GO
-- The following query fails
SELECT TOP 15 ap.*
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color = dp.Color -- Collation conflict
GO
/*
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.
*/
-- The following still fails
SELECT TOP 15 ap.ProductNumber + dp.ProductNumber -- Collation conflict in append
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color = dp.Color COLLATE database_default
GO
-- Error: Cannot resolve collation conflict for column 1 in SELECT statement.
-- The following query works - notice the double use of collate
SELECT TOP 6
Collated=ap.ProductNumber COLLATE DATABASE_DEFAULT + ' : '+ dp.ProductNumber
FROM AdventureWorks.Production.Product ap
INNER JOIN dbo.Product dp
ON ap.Color = dp.Color COLLATE DATABASE_DEFAULT
ORDER BY newid()
GO
/*
Collated
RW-M423 : FW-R762
FR-R38R-60 : FR-R38R-48
RW-R820 : BK-M18B-44
BK-R19B-52 : CA-6738
BK-T79Y-50 : BK-T18Y-50
BK-R89B-48 : FW-T905
*/
-- Cleanup
drop table [dbo].[Product] |
