What issues to anticipate having different collations between development and production SQL Servers?

Question

Subquestioning "Unable to update sys.columns - any other approach?" vaguely mentioning the problems on deployment to server with a different collation.

The problem is that by default SQL Server collation is determined (during setup) by Windows Regional and Language Options --> Advanced --> "Select a language to match the language version of the non-Unicode program you want to use" in Control Panel.

So, this difference in collations between dev and production/client SQL Servers is quite common situation.
For example, but just for example, I am having Cyrillic_General_CI_AS collation having come from Windows at my local SQL Server and I have clients having servers with Hebrew collation.

So, what issues/problems should developer anticipate having to develop with one collation and possibly, sometimes, even not knowing which collation the client/customer is having on SQL Server?

Update:

Let's say that typical situation is that I do not develop the database from scratch or install production SQL Server at client. The typical situation is that I either connect/deploy to SQL Server on shared or dedicated server and/or receive backup of database.

Update2:
@u07ch,
my dev machine is running on en-us Windows XP Pro SP3 (English) and en-us SQL Server (in English).
Plz see my question from where default collation of SQL Server is coming from ("Select a language to match the language version of the non-Unicode program you want to use").

I could not understand how one "never use temporary tables or anything else that uses TEMPd".
TempDB is used for most operations in SQL Server, even for storing intermediate results from selects.

@Damien_The_Unbeliever,
I do not feel that CS_AS is awkward. IMHO. it is CI-AI that is awkward.

If to script (by hand or by SSMS) then I inevitably bump against Collations are scripted either for all columns or for none

Update3:
My question explicitly says, and my updates reiterate, that I do NOT use any Cyrillic collations (or even codepages) in anyway but my SQL Server and databases have Cyrillic_General_CI_AS as default collation due to SQL Server default setup linked to dependences on Windows configuration.
The rationale under this is, BTW, difficult to explain or understand but this is simply status quo in SQL Server setups...
But the result is that this dependence practically ensures different (default) collations of SQL Servers in different countries. Which is the essence of this question...

Answer 1

If your customers don't have a SQL server in hebrew already and you are installing from scratch then the SQL Server collation is configurable by the DBA during installation by selecting the custom install option; you eventually get to a collation designer tab in 2005/8.

If its too late for that then if you never use temporary tables or anything else that uses TEMPdb, or you are all unicode or if you are blessed with SQL logic that casts all the collations back as it runs then I think you won't need to worry about it at all. Assuming these aren't the case (as they are in our databases) then you will need to run the database in the local collation on the SQL server in order to avoid collation conflicts. To do that you need to get the SQL Script out with collation settings and run that into the new server so that it picks up the local collation.

The final thing to worry about is if you need to get the data back from the client to your local system running cyrillic you will either need a hebrew collation server or to DTS / SSIS all the data from a backup into a local collation copy in your offices in order to run the data.

Answer 2

If you know that you're going to be dealing with different collations, then the best piece of advice I've heard is to make sure that the collation you're developing with is as awkward as possible. So I'd change your development setup to use a case sensitive, accent sensitive collation (CS_AS, not CI_AS) at the very least.

This way, any collation related assumptions that you're making in your code should show up in development, rather than when releasing to production.

I'd also make sure that any scripts are written by hand (or you turn off the scripting options in SSMS that include collations), and any releases to production are done by script, rather than by e.g. Import/Export wizard or by restoring databases. That way, the production database should have the collation options desired by the end users.