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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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