Collation and the temp tables

It’s a hack which I am going to describe in this post for the people who are working in tight delivery schedules. Hope everybody knows what is collation in SQL Server and how it cause issues if we use database objects in different collations in a query. Also note the task involved in changing an existing SQL Server instance to a different collation and how to change collation of system databases such as temp database or any other user database.

We were using SQL_Latin1_General_CP1_CI_AS collation till couple of months back and recently moved to Latin1_General_100_CI_AS_KS_WS collation. We created new database in Latin1_General_100_CI_AS_KS_WS and it worked in all the development machines without any issue .So we changed the testing servers to Latin1_General_100_CI_AS_KS_WS collation and it performed well. After some load testing we had to modify one SP which introduced temp tables. As it is related to load testing we first applied in the test server and it rocked. But when we take the same sp to development machines the a problem raised because we didn’t change the sql server instances of our development machines to Latin1_General_100_CI_AS_KS_WS which is a good time consuming process. That means the temp database is in different collation compared to our project database.

For example consider a simple scenario.We created a Address database in the Latin1_General_100_CI_AS_KS_WS collation where our sql server instance is in SQL_Latin1_General_CP1_CI_AS .We have a Person table (Id,Name) and Address Table (Id,PersonId,Address) which has person id as foreign key and we need to select details of some persons based on a particular person name list.Earlier we had a ‘in’ keyword based implementation and that we changed to temp table based implementation where the temp table is a table which has one column and it will be joined with the Address table to get the details. Ok its time to see some sql.

CREATE TABLE [dbo].[Person](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)

CREATE TABLE [dbo].[Address](
    [Id] [int] NULL,
    [PersonId] [int] NULL,
    [Address] [nvarchar](max) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_Person] FOREIGN KEY([PersonId])
REFERENCES [dbo].[Person] ([Id])

Here is the modified query to use the table variables which uses temp db.

IF NOT OBJECT_ID('tempdb..#Selected') IS NULL
    DROP TABLE #Selected;
CREATE TABLE #Selected
(
Name nvarchar(255)
);
--Logic to fill the #Selected table
select #Selected.Name,[Address].[Address]
from  #Selected join Person 
    on #Selected.name = Person.Name 
        join [Address] 
        on Person.Id =[Address].PersonId;

The error msg was “Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.”. This means the temp DB is still in SQL_Latin1_General_CP1_CI_AS collation and our DB is in Latin1_General_100_CI_AS_KS_WS collation which doesn’t allow us to do a comparison on strings.So as a hack or quick work around in development environment, we modified the query as follows which specifies the collation on the comparison.

IF NOT OBJECT_ID('tempdb..#Selected') IS NULL
    DROP TABLE #Selected;
CREATE TABLE #Selected
(
Name nvarchar(255)
);
--Logic to fill the #Selected table
select #Selected.Name,[Address].[Address]
from  #Selected join Person 
    on #Selected.name collate Latin1_General_100_CI_AS_KS_WS= Person.Name collate Latin1_General_100_CI_AS_KS_WS
        join [Address] 
        on Person.Id =[Address].PersonId;

Happy scripting…