Question Regarding SQL Collation Types 2

[llevon]

Hi,

I have noticed that when installing SQL server 2000 and I choose a custom setup and dont actually change anything that the SQL collation will always be:
latin1_general_CI_AS

If I install SQL 2000 and choose typical for the setup then the resulting SQL collation becomes: SQL_LATIN1_GENERAL_CP1_CI_AS

Is there any difference between these two collation types? Our new software states that the collation must be the latter but our database has a collation of the former.

Regards,

Llev

 

[hmckillop]

Big difference if you use things like table variables or the tempdb.

Note The Setup program does not set the instance default collation to the Windows collation Latin1_General_CI_AS if the computer is using the U.S. English locale. Instead, it sets the instance default collation to the SQL collation SQL_Latin1_General_Cp1_CI_AS. This may change in a future release.
[/code]
Which is effectively an admission of guilt that this behaviour is wrong.

If the server has a collation of SQL_Latin1_General_CP1_CI_AS for the model database which is what tempdb gets built from I think, certainly tempdb has a collation of SQL_Latin1_General_CP1_CI_AS whereas you set your database with a collation Latin1_General_CI_AS you will see problems with table variables. I think because with a table variable you could end up putting the data in tempdb it must will use the tempdb default collation unless one is specified.
If you dont use temp or table variables dont worry too much, but it is an area for concern.

PS and the only way to rectify it that I know off is to re-install SQL Server.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[llevon]

Thanks for that,


Following your advice I have since found the following info on changing the collation of the server without a reinstall of SQL and thought I should share it with everyone.

http://www.leyton.org/techiemusings/2004/08/12/sql-server-and-collation-settings

Thanks,


Llev

 

[mrdenny]

FYI yes tempdb is recreated from model each time the SQL Server is restarted.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)