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?