Execute the following script in Query Editor to demonstrate the use of COLLATE DATABASE_DEFAULT in a SELECT statement:

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

-- This one fails
SELECT TOP 15 ap.*
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color = dp.Color
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.

-- This one still fails
SELECT TOP 15 ap.ProductNumber+dp.ProductNumber
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.

-- This one works - notice the double use of collate
SELECT TOP 15 ap.ProductNumber COLLATE DATABASE_DEFAULT +dp.ProductNumber
FROM AdventureWorks.Production.Product ap
JOIN dbo.Product dp
ON ap.Color = dp.Color COLLATE DATABASE_DEFAULT
GO