SQL Server Performance Forum – Threads Archive
Collation comparison
Hi everyone, I’ve just started work in a new company where all their SQL servers have been installed with codepage SQL_Latin1_General_Cp1_CI_AS except for one server which is installed with codepage Latin1_General_CI_AS. As far as I can see, both these collations use: • Latin 1 General dictionary sorting rules• Codepage 1252
• Case-Insensitive
• Accent-Sensitive Do these collations have an identical effect on SQL Server? If not – what#%92s the difference? Thanks,
LesleyG
There is a difference ‘cos if you do a char join between these two collations, it’ll fail. i’ve had this very problem myself. i think it comes from chosing the default options during setup, but the servers have different Regional Settings – e.g. one has UK and another has US. A right royal pain in the you-know-what.
Tom Pullen
DBA, Oxfam GB
… and you may know in SQL Server 2000 you can have collation settings on column level to server level for the future data handling. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks folks. Thomas has put his finger right on it; my server is exhibiting precisely this behaviour. The application on this server works perfectly well at the moment, but apparently some queries were altered in the past to take this behaviour into account. However, I want to put a second application on the server and want to be sure that it will work exactly the same way as it currently does on a server with a different collation. So, as unpleasant as the task appears, it looks like I’ll have to rebuild this server with the correct collation. LesleyG
Are you going to restore the database(s) onto it after you’ve rebuilt it? You can alter the collation of every single char column one by one, and othe default collation of the the user databases, without having to empty everything, but it’d be a hassle I’m sure. Good luck, good job you’re the person for the task, eh Lesley?!!! Tom Pullen
DBA, Oxfam GB
Rather than rebuuilding server you can redefine a new database with similar collation and use DTS to poll data. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks for the suggestions! The reason for rebuilding master & changing the collation is really so that the collation of tempdb is changed because I want to restore a database from another server and this database has queries which compare char values in tempdb. There’s only 1 user database currently on the server and it’s working fine at the mo (the default db collation and most of the column collations have been altered to the correct collation). So…as I don’t really have to empty & rebuild the database, my plan was to be lax & just detach & reattach the existing db. So my plan was to: -Put the user db in DBO
-Backup all user & system dbs
-Detach the user db
-Rebuild the master database, changing the collation in the process
-Restart the server
-Reattach the user db Can either of you 5 star guys see any problems with this? thanks,
Lesley
You clearly know what you’re doing, despite so far only having 1 star. I think your plan is definitely 5-star! When I’ve had this problem before it’s always been tempdb tables that stuff up. Tom Pullen
DBA, Oxfam GB
Tip from the trenches: running a db that was created using a different collation than the default one for the current instance of SQL Server means you’ll run into at least 2 issues: (1) Stored procedures that use CREATE TABLE #MyTable and then run a query joining #MyTable to one of the db’s user tables on a varchar/char column, or comparing them in a WHERE statement, will fail on that query. If you can’t be certain of the production server’s collation, add the COLLATE DATABASE_DEFAULT clause for each char/varchar column of all CREATE TABLE #MyTable definitions. (2) Any query that joins a user table to a system table, or compares columns in a WHERE statement. This can be avoided by adding the COLLATE DATABASE_DEFAULT after the system table column name.
Are you using linked servers to run queries between the two servers currently? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
No, the two servers are not linked. I need to transfer a database from serverA (default collation1) to serverB (default collation2). But as I need the database to behave in the same way as it currently does on serverA, I am planning to change the default collation on serverB to collation1. I’m pretty sure I’ve got it under control, but thanks for your comments and suggestions. thanks,
Lesley
]]>