July 31, 2015 at 4:47 am
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.
July 31, 2015 at 4:55 am
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
July 31, 2015 at 1:36 pm
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".
August 1, 2015 at 3:15 am
Ok, there's nothing at database level that we can set to force the temp tables to take precedence of current DB collation?.
August 2, 2015 at 7:55 am
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
August 2, 2015 at 8:50 am
I assume the issue your trying to avoid is the sort order problem with unicode as mentioned in the below link.
else there should be little need to change the collation in the first place.
August 3, 2015 at 8:45 am
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.
August 3, 2015 at 4:32 pm
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
Change is inevitable... Change for the better is not.
August 5, 2015 at 8:11 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply