Q: Setting collation to a temporary table, and not individual fields...

[Martin Arvidsson, Visual Systems AB]

Hi!

I have a customer who has a SQL 2005 server with SQL_Latin_PC1_CI_AS
collation as default.

How ever, our database is in Swedish_Finnish_CI_AS and in a couple of sprocs
we use
temporary tables.

Now. Is there a way to create the temporary table and specify collation for
every field in the temp table
or is the only way to declare it after every field definition?

Like:

Create table #Temp (
My_Field Char(10) Collate Swedish_Finnish_CI_AS null,
.... etc
)

Regards
Martin

[vinu]

Martin

Yes you can do that. see the e.g bellow


-- i am creating a table Finnish_Swedish_CS_AS collation
Create table #Temp (
My_Field Char(10) Collate Finnish_Swedish_CS_AS null,
My_Field1 Char(10) /*-- by defaule this column will use tempdbs
collation settings whic is Latin1_General_CI_AS*/
)

-- my defaule tempdb collation is Latin1_General_CI_AS
Create table #Temp1 (
My_Field Char(10)

)

insert into #Temp values('aa','aa')
insert into #Temp1 values('aa')


select * from #Temp t inner join #Temp1 t1 on t.My_Field=t1.My_Field

when i execute the above select i get an error message


Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"Finnish_Swedish_CS_AS" in the equal to operation.


this proves you can create a temp table columns with different collation
settings

but this select works

select * from #Temp t inner join #Temp1 t1 on t.My_Field1=t1.My_Field



vinu
http://oneplace4sql.blogspot.com/

Post by Martin Arvidsson, Visual Systems AB
Hi!
I have a customer who has a SQL 2005 server with SQL_Latin_PC1_CI_AS
collation as default.
How ever, our database is in Swedish_Finnish_CI_AS and in a couple of
sprocs we use
temporary tables.
Now. Is there a way to create the temporary table and specify collation
for every field in the temp table
or is the only way to declare it after every field definition?
Create table #Temp (
My_Field Char(10) Collate Swedish_Finnish_CI_AS null,
.... etc
)
Regards
Martin

[Martin Arvidsson, Visual Systems AB]

Sorry, should have been more clear in my description.

As i know, you have to set collate after every field definition.
But! Can you set the collation for the whole table at once, so that every
field gets the collation i want.
That is not setting collate for individual field?

Regards
Martin

Post by Martin Arvidsson, Visual Systems AB
Martin
Yes you can do that. see the e.g bellow
-- i am creating a table Finnish_Swedish_CS_AS collation
Create table #Temp (
My_Field Char(10) Collate Finnish_Swedish_CS_AS null,
My_Field1 Char(10) /*-- by defaule this column will use tempdbs
collation settings whic is Latin1_General_CI_AS*/
)
-- my defaule tempdb collation is Latin1_General_CI_AS
Create table #Temp1 (
My_Field Char(10)
)
insert into #Temp values('aa','aa')
insert into #Temp1 values('aa')
select * from #Temp t inner join #Temp1 t1 on t.My_Field=t1.My_Field
when i execute the above select i get an error message
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"Finnish_Swedish_CS_AS" in the equal to operation.
this proves you can create a temp table columns with different collation
settings
but this select works
select * from #Temp t inner join #Temp1 t1 on t.My_Field1=t1.My_Field
vinu
http://oneplace4sql.blogspot.com/

Post by Martin Arvidsson, Visual Systems AB
Hi!
I have a customer who has a SQL 2005 server with SQL_Latin_PC1_CI_AS
collation as default.
How ever, our database is in Swedish_Finnish_CI_AS and in a couple of
sprocs we use
temporary tables.
Now. Is there a way to create the temporary table and specify collation
for every field in the temp table
or is the only way to declare it after every field definition?
Create table #Temp (
My_Field Char(10) Collate Swedish_Finnish_CI_AS null,
.... etc
)
Regards
Martin

[vinu]

Martin

I don't think it is possible, because temp table inherit the collation
settings of tempdb.

vinu
http://oneplace4sql.blogspot.com/

Post by Martin Arvidsson, Visual Systems AB
Sorry, should have been more clear in my description.
As i know, you have to set collate after every field definition.
But! Can you set the collation for the whole table at once, so that every
field gets the collation i want.
That is not setting collate for individual field?
Regards
Martin

Post by Martin Arvidsson, Visual Systems AB
Martin
Yes you can do that. see the e.g bellow
-- i am creating a table Finnish_Swedish_CS_AS collation
Create table #Temp (
My_Field Char(10) Collate Finnish_Swedish_CS_AS null,
My_Field1 Char(10) /*-- by defaule this column will use tempdbs
collation settings whic is Latin1_General_CI_AS*/
)
-- my defaule tempdb collation is Latin1_General_CI_AS
Create table #Temp1 (
My_Field Char(10)
)
insert into #Temp values('aa','aa')
insert into #Temp1 values('aa')
select * from #Temp t inner join #Temp1 t1 on t.My_Field=t1.My_Field
when i execute the above select i get an error message
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"Finnish_Swedish_CS_AS" in the equal to operation.
this proves you can create a temp table columns with different collation
settings
but this select works
select * from #Temp t inner join #Temp1 t1 on t.My_Field1=t1.My_Field
vinu
http://oneplace4sql.blogspot.com/

Post by Martin Arvidsson, Visual Systems AB
Hi!
I have a customer who has a SQL 2005 server with SQL_Latin_PC1_CI_AS
collation as default.
How ever, our database is in Swedish_Finnish_CI_AS and in a couple of
sprocs we use
temporary tables.
Now. Is there a way to create the temporary table and specify collation
for every field in the temp table
or is the only way to declare it after every field definition?
Create table #Temp (
My_Field Char(10) Collate Swedish_Finnish_CI_AS null,
.... etc
)
Regards
Martin

[vinu]

See the following link

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1186718,00.html#


vinu
http://oneplace4sql.blogspot.com/

Post by Martin Arvidsson, Visual Systems AB
Martin
I don't think it is possible, because temp table inherit the collation
settings of tempdb.
vinu
http://oneplace4sql.blogspot.com/

Post by Martin Arvidsson, Visual Systems AB
Sorry, should have been more clear in my description.
As i know, you have to set collate after every field definition.
But! Can you set the collation for the whole table at once, so that every
field gets the collation i want.
That is not setting collate for individual field?
Regards
Martin

Post by Martin Arvidsson, Visual Systems AB
Martin
Yes you can do that. see the e.g bellow
-- i am creating a table Finnish_Swedish_CS_AS collation
Create table #Temp (
My_Field Char(10) Collate Finnish_Swedish_CS_AS null,
My_Field1 Char(10) /*-- by defaule this column will use tempdbs
collation settings whic is Latin1_General_CI_AS*/
)
-- my defaule tempdb collation is Latin1_General_CI_AS
Create table #Temp1 (
My_Field Char(10)
)
insert into #Temp values('aa','aa')
insert into #Temp1 values('aa')
select * from #Temp t inner join #Temp1 t1 on t.My_Field=t1.My_Field
when i execute the above select i get an error message
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and
"Finnish_Swedish_CS_AS" in the equal to operation.
this proves you can create a temp table columns with different collation
settings
but this select works
select * from #Temp t inner join #Temp1 t1 on t.My_Field1=t1.My_Field
vinu
http://oneplace4sql.blogspot.com/

Post by Martin Arvidsson, Visual Systems AB
Hi!
I have a customer who has a SQL 2005 server with SQL_Latin_PC1_CI_AS
collation as default.
How ever, our database is in Swedish_Finnish_CI_AS and in a couple of
sprocs we use
temporary tables.
Now. Is there a way to create the temporary table and specify collation
for every field in the temp table
or is the only way to declare it after every field definition?
Create table #Temp (
My_Field Char(10) Collate Swedish_Finnish_CI_AS null,
.... etc
)
Regards
Martin

[Razvan Socol]

Hello, Martin

It is not possible in SQL Server 2005 (or 2008) to specify a table-level
default collation. However, you might want to vote for this suggestion, as
a better alternative solution to your problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Feedback
ID=358309

[Erland Sommarskog]

Post by Martin Arvidsson, Visual Systems AB
I have a customer who has a SQL 2005 server with SQL_Latin_PC1_CI_AS
collation as default.
How ever, our database is in Swedish_Finnish_CI_AS and in a couple of
sprocs we use temporary tables.
Now. Is there a way to create the temporary table and specify collation
for every field in the temp table or is the only way to declare it after
every field definition?
Create table #Temp (
My_Field Char(10) Collate Swedish_Finnish_CI_AS null,
.... etc
)

No, there isn't.

But a few comments.

First, there is an obvious mismatch here. SQL_Latin1_General_CP1_CI_AS is
a collation that is good for English, Dutch, German and a few more
languages. But it's not good for Swedish or Finnish. If your customer is
located in Sweden or Finland, your customer is likely to have installed
SQL Server incorrectly, and would benefit from making a proper
reinstallation. If your customer is located is elsewhere, you are the one
with the incorrect collation, and should you should change.

I would also like to point out here that SQL collations have their own
set of troubles, and there is all reason to avoid them. The most likely
reason why they have this collation is that when someone installed Windows,
they did not care to change the systen locale from US English, and with
US English as the system locale, the default collation is
SQL_Latin1_General_CP1_CI_AS.

Obviously, as a vendor you often have to adapt to the customer, so have
reason to reconsider how should ship your databases. While the best way
when you want to copy databases between servers is to use BACKUP/RESTORE,
I think that when you install a product, the database should be built
from scripts, either at installation, or when you build the install package.
But only in the former case, you can adapt to the customer's preferred
collation. The important thing here is that if you build the database from
scripts under source control, you know *what* you have shipped.

Finally, if your short-term solution is to add all these COLLATE clause,
don't specify the collation name, but say:

COLLATE database_default

then you don't have to change, if you build the database with, say,
Danish_Norwegian_CI_AS when your business expands westwards.