index not being used due to different collations – is there any work around?

I am running the following query:

SET ROWCOUNT 50

SELECT UL.*
                FROM COLA.dbo.tbl_UserLogin ul    
          INNER JOIN CABrochure.dbo.tbl_country co
                  ON ul.CountryCode      COLLATE DATABASE_DEFAULT = 
                     co.cola_countrycode COLLATE DATABASE_DEFAULT  

SELECT UL.*
                FROM COLA.dbo.tbl_UserLogin ul    
          INNER JOIN CRMReferences.dbo.tbl_country2 co
                  ON ul.CountryCode    = co.cola_countrycode 

SET ROWCOUNT 0

and when I look at the query execution plan I see this:

OBS. The index used (table tbl_country and tbl_country2) is the same, just by mistake I put a wrong name.

I have different collations in my databases.

The only difference between the CABrochure.dbo.tbl_country table and the second table, CRMReferences.dbo.tbl_country2 is the collation.

The first table is a synonym but that does not change anything.

I have created (and populated with all data from) CRMReferences.dbo.tbl_country2 same as the tbl_country table BUT I have used the compatible collation for the column cola_countrycode which is used in the inner join as you can see below.

IF OBJECT_ID('[dbo].[tbl_country2]') IS NOT NULL 
DROP TABLE [dbo].[tbl_country2] 
GO
CREATE TABLE [dbo].[tbl_country2] ( 
[co_code]           VARCHAR(2)                       NOT NULL,
[country_id]        INT                              NOT NULL,
[co_name]           VARCHAR(50)                      NOT NULL,
[co_recruiting]     BIT                                  NULL,
[co_rank]           INT                                  NULL,
[co_display]        CHAR(10)                             NULL,
[CRM_GuidId]        VARCHAR(100)                         NULL,
[cola_countrycode]  CHAR(2) COLLATE Latin1_General_CI_AS NULL)

ALTER TABLE [dbo].[tbl_country2] ADD  CONSTRAINT [PK_TBL_COUNTRY2] 
PRIMARY KEY CLUSTERED (  [co_code] ASC  )  

CREATE NONCLUSTERED INDEX I_cola_countrycode  
ON [dbo].[tbl_country2] (  [cola_countrycode] ASC  )  

The main table on this query:
dbo.tbl_UserLogin
this table lives in a database called cola whose collation is Latin1_General_CI_AS

USE [cola]
go
exec sp_gettabledef 'dbo.tbl_UserLogin'


IF OBJECT_ID('[dbo].[tbl_UserLogin]') IS NOT NULL 
DROP TABLE [dbo].[tbl_UserLogin] 
GO
CREATE TABLE [dbo].[tbl_UserLogin] ( 
[UserID]         NUMERIC(18,0)    IDENTITY(1,1) NOT NULL,
[UserName]       VARCHAR(50)      NULL,
[Email]          VARCHAR(255)     NULL,
[FirstName]      VARCHAR(50)      NOT NULL,
[middleName]     VARCHAR(50)      NULL,
[LastName]       VARCHAR(50)      NOT NULL,
[CountryCode]    VARCHAR(2)       NULL,
[Gender]         CHAR(1)          NULL,
[PasswordSalt]   INT              NULL,
[ApplicantID]    NUMERIC(18,0)    NULL,
[InterviewerID]  NUMERIC(18,0)    NULL,
[Password]       VARCHAR(50)      NULL,
[DateOfBirth]    DATETIME         NULL,
[ReligionDenom]  VARCHAR(30)      NULL,
[createdOn]      DATETIME         NOT NULL  
 CONSTRAINT [DF_tbl_UserLogin_createdOn] 
 DEFAULT (getdate()),
[RegionId]       NUMERIC(18,0) NULL,
[faceTimeId]     VARCHAR(50) NULL  
CONSTRAINT [DF__tbl_UserL__faceT__7E1394B9] DEFAULT (NULL),
CONSTRAINT   [PK_tbl_UserLogin]  PRIMARY KEY CLUSTERED    
             ([UserID] asc),
CONSTRAINT   [uc_Email]  UNIQUE NONCLUSTERED ([Email] asc) 
   WITH FILLFACTOR = 100,
    CONSTRAINT   [ck_uniqueApplicantID]        
         CHECK ([dbo].[validateApplicantIDExistance]([applicantID])<=(1)),
    CONSTRAINT   [ck_uniqueInterviewerID]      
        CHECK ([dbo].[validateInterviewerIDExistance]([InterviewerID])<=(1)))

    GO

CREATE NONCLUSTERED INDEX [idx_firstname] 
   ON [dbo].[tbl_UserLogin] ([FirstName] asc)
   INCLUDE ([ApplicantID], [CountryCode], [Email], [LastName])
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [idx_interviewerID] 
   ON [dbo].[tbl_UserLogin] ([InterviewerID] asc)
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [idx_lastname] 
   ON [dbo].[tbl_UserLogin] ([LastName] asc)
   INCLUDE ([ApplicantID], [CountryCode], [Email], [FirstName])
   WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tbl_UserLogin_ApplicantID] 
   ON [dbo].[tbl_UserLogin] ([ApplicantID] asc)
   INCLUDE ([UserID])
   WITH FILLFACTOR = 100

I don’t know if the different collations in different databases in this environment serve an specific purpose. I don’t know if I could make them all the same. I am trying to avoid going through the process of changing a collation of a database.

Other than that,

Is there any way I can do these joints, based on char or varchar columns of different collations and still use the indexes?

The key issue is the order of the rows based on the Collation, especially when a VARCHAR column is using a SQL Server Collation. Using a COLLATE keyword to change the run-time Collation doesn’t change the physical order of the rows in the index. The only fixes are to:

1. change the Collation of the CHAR / VARCHAR column(s) to use a Windows Collation. (this is the best option)
2. Use COLLATE to force the Collation to be the SQL Server (i.e. starting with SQL_) Collation.

After using the suggestion number 2 of Solomon Rutzky‘ answer above
I have changed my original query to convert to the SQL_% COLLATE as follows:

SET ROWCOUNT 50

SELECT UL.*
                FROM COLA.dbo.tbl_UserLogin ul    
          INNER JOIN CABrochure.dbo.tbl_country co
                  ON ul.CountryCode        
                     COLLATE SQL_Latin1_General_CP1_CI_AS    
                  = co.cola_countrycode 
                    COLLATE SQL_Latin1_General_CP1_CI_AS 


SELECT UL.*
                FROM COLA.dbo.tbl_UserLogin ul    
          INNER JOIN CRMReferences.dbo.tbl_country2 co
                  ON ul.CountryCode    = co.cola_countrycode 

SET ROWCOUNT 0

I got this much better execution plan that does a index seek
as you can see on the below picture: