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 HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to resolve collation conflict in SELECT?

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]

Exam Prep 70-461
Exam 70-461
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