temporary tables (# tables) & tempdb

Question

temporary tables (# tables) & tempdb

GonnaCatchIT

SSCrazy

Points: 2797
More actions
July 31, 2015 at 4:47 am

#306212

Hi Team,
I have a Database say DB1 which has collation "SQL_Latin1_General_CP1_CI_AS".
The tempdb in the sql server is set to collation "Latin1_General_CI_AS".
I have SP's which create temp tables say :#temp1 , it sets to collation of tempb as its all created in their.
Is their a setting or way out to tell the #table to use collation of Database DB1 ("Latin1_General_CI_AS") instead if tempDB ?.
I have below challenges:
> tempdb collation cannot be changed as it's used by other applications.
> Cant make any code changes in SP's (no time, resource etc)
I have over 100 sp's and I cant change code to change the collations in each SP.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No.

You'll need to either change the procedures and explicitly set the collation on any varchar/char column, or change the procedures and change the temp tables to table variables (not generally recommended as may impact performance) or rebuild the system DBs with the collation you need

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

You'll have to specify COLLATE on every column to which it applies.

For example:

CREATE TABLE #test ( col1 varchar(30) NOT NULL COLLATE Latin1_General_CI_AS, col2 int NOT NULL, col3 datetime NOT NULL, col4 varchar(40) COLLATE Latin1_General_CI_AS ) --...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 3

Ok, there's nothing at database level that we can set to force the temp tables to take precedence of current DB collation?.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 5

I assume the issue your trying to avoid is the sort order problem with unicode as mentioned in the below link.

http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as

else there should be little need to change the collation in the first place.

Jayanth Kurup[/url]

Chris Wooding SSCarpal Tunnel Points: 4556 More actions · Answer 6

If your problem is with joins or comparisons between columns in the temp tables and those in your database, you can specify which collation to use at that point, eg.

Select * from #TempTable T inner join dbo.MyTable M on T.ID = M.ID collate Latin1_General_CI_AS

or

Select * from #TempTable where ID = (Select ID from dbo.MyTable) collate Latin1_General_CI_AS

It still means changing your stored procedures though.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

GonnaCatchIT (7/31/2015)
Hi Team,
I have a Database say DB1 which has collation "SQL_Latin1_General_CP1_CI_AS".
The tempdb in the sql server is set to collation "Latin1_General_CI_AS".
I have SP's which create temp tables say :#temp1 , it sets to collation of tempb as its all created in their.
Is their a setting or way out to tell the #table to use collation of Database DB1 ("Latin1_General_CI_AS") instead if tempDB ?.
I have below challenges:
> tempdb collation cannot be changed as it's used by other applications.
> Cant make any code changes in SP's (no time, resource etc)
I have over 100 sp's and I cant change code to change the collations in each SP.

Hmmm... since both collations use Case Insensitive and Accent Sensitive and both collations are pretty darned fasted, I wonder if it would actually make any difference to the applications if the collation of TempDB were changed. You could test it on a dev system.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 8

Jeff Moden (8/3/2015)
Hmmm... since both collations use Case Insensitive and Accent Sensitive and both collations are pretty darned fasted, I wonder if it would actually make any difference to the applications if the collation of TempDB were changed. You could test it on a dev system.

would depend on whether he has other databases on that server.