how to keep two databases with different collation on one instence with respect to temp tables

Question

hi,

i want to keep two databses with diffrent collation on one instence of sqlserver but following is the problem i am getting.

sqlserver is on  Thai_CI_AS and one of the database is on Thai_CI_AS and other one is on SQL_Latin1_General_CP1_CI_AS

the one on SQL_Latin1_General_CP1_CI_AS is creating problem .

select * into #tempt from xyz

select * from #tempt t

join bridge b on t.name=b.name.

I found the tempdb is on Thai_CI_AS so this query was giving error

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Thai_CI_AS" in the equal to operation.

how can i resolve this problem.

is there any setting in master databse where i can tell that each database should use its own collation in tempdb rather than piking tempdb's collation.

2) can put something in connection string which can override the tempdb's collation

3) can put some setting in stored procedure which can override the tempdb's collation.

your sincerely

Answer 1

One option is to connect directly to a user (and not a login), using contained database authentication. When you do that, a temp table will get the current database collation instead of tempdb's collation. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/contained-database-authentication-server-configuration-option

Or specify COLLATE for the column(s) in your query.

Answer 2

As Tibor wrote, you can cast the collation in your queries

select *   
from #tempt t  
     join   
     bridge b   
         on t.name COLLATE Thai_CI_AS = b.name.  

Of course some work and may slow down your queries.

Answer 3

When you use SELECT INTO to create the temp tables, you will get the same collation as in the source databases. So the idea with using contained databases will not work. (And you need to create the database as contained in the first place.)

If you create the temp tables with CREATE TABLE, you can say:

   CREATE TABLE #temp (a varchar(20) COLLATE database_default NOT NULL)  

This specifies that the column should have default collation of the current database.

While this is a bit tedious, it is best practice to always specify this when you create temp tables, so that you don't run into to surprises when you move a database to a server with a different collation.

Answer 4

Hi @Rajesh Kumar Yadav ,

You can create a temp table, but if you have a different collation other than TempDB may have other problems

A simple way to get around this is to use database_default

Please check out this article:
Changing Database Collation and dealing with TempDB Objects

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.