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

Question

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?

Answer 1

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.

Answer 2

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: