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?
Best Answer
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 aCOLLATE
keyword to change the run-time Collation doesn't change the physical order of the rows in the index. The only fixes are to:CHAR
/VARCHAR
column(s) to use a Windows Collation. (this is the best option)COLLATE
to force the Collation to be the SQL Server (i.e. starting withSQL_
) Collation.