I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have them configured as linked servers.

Currently, both databases use the SQL_Latin1_General_CP1_CI_AS collation. However, one of the databases will need to be changed to Latin1_General_CI_AS soon in order for our vendor to continue fully supporting it.

My expectation is that we could manage this at the linked server level by setting "Use Remote Collation" to false. However, I've read that we should expect performance to plummet, if we take that approach. Is that correct? Short of testing this in an internal development environment (which would prove difficult for a variety of reasons), is there a way for us to estimate/calculate the sort of performance hit we would experience?

Thanks for your feedback! Hopefully this question isn't too ambiguous. I've done a bit of research on the topic already but am hard time finding a concrete response.

  • I have provided a detailed answer showing the effects of using different collations SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. Best is to use avoid collation conversion. Let me know if you have any further questions after reading the answer. Performance is also affected whether you are pulling or pushing data using linked server. – Kin Apr 7 '15 at 21:07
  • Changing the database's collation does not automatically change the collation of all existing columns. You have a lot of work ahead of you. Good luck. – Michael Green Apr 8 '15 at 1:46
  • The Use Remote Collation setting applies to WHERE and ORDER BY, but I believe JOIN clauses will require the same collation regardless of the setting. You'll need to add a COLLATE clause for joins. – Dan Guzman Apr 10 '15 at 1:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.