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_c

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:

• change the Collation of the CHAR / VARCHAR column(s) to use a Windows Collation. (this is the best option)

• Use COLLATE to force the Collation to be the SQL Server (i.e. starting with SQL_) Collation.

StackExchange Database Administrators Q#216432, answer score: 3