Execute the following script in Query Editor to create a collation conflict between two EmailAddresses: one is CI case insensitive, the other is CS case sensitive. The first SELECT JOIN without the "COLLATE" gives error 468. All the SELECT JOINs with "COLLATE" will work.

USE tempdb
GO

CREATE TABLE [dbo].[Contact](
[ContactID] [int] NOT NULL,
[NameStyle] [int] NOT NULL CONSTRAINT [DF_Contact_NameStyle] DEFAULT ((0)),
[Title] [nvarchar](8) NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL,
[Suffix] [nvarchar](10) NULL,
[EmailAddress] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Contact_EmailPromotion] DEFAULT ((0)),
[Phone] [nvarchar](25) NULL,
[PasswordHash] [varchar](40) NOT NULL,
[PasswordSalt] [varchar](10) NOT NULL,
[AdditionalContactInfo] [xml] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Contact_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Contact_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Contact_ContactID] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT dbo.Contact
SELECT * from AdventureWorks.Person.Contact
GO

SELECT a.*
FROM AdventureWorks.Person.Contact a
JOIN dbo.Contact b
ON a.EmailAddress = b.EmailAddress
GO

/*

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

*/

-- All below will work

SELECT a.*
FROM AdventureWorks.Person.Contact a
JOIN dbo.Contact b
ON a.EmailAddress = b.EmailAddress COLLATE DATABASE_DEFAULT
GO

SELECT a.*
FROM AdventureWorks.Person.Contact a
JOIN dbo.Contact b
ON a.EmailAddress COLLATE DATABASE_DEFAULT = b.EmailAddress
GO

SELECT a.*
FROM AdventureWorks.Person.Contact a
JOIN dbo.Contact b
ON a.EmailAddress COLLATE SQL_Latin1_General_CP1_CS_AS = b.EmailAddress
GO

SELECT a.*
FROM AdventureWorks.Person.Contact a
JOIN dbo.Contact b
ON a.EmailAddress = b.EmailAddress COLLATE SQL_Latin1_General_CP1_CS_AS
GO

SELECT a.*
FROM AdventureWorks.Person.Contact a
JOIN dbo.Contact b
ON a.EmailAddress = b.EmailAddress COLLATE SQL_Latin1_General_CP1_CI_AS
GO