We are installing a new SQL Server 2012 Enterprise that is to be deployed in different countries around the world. All the servers is going to share data in real time, and the application is connecting to the server giving the fastest respone. So if I run the application in Norway, I could get the result from a server in Sweden. I’m having a struggle with selecting the best Collation for the server’s. The main office is in Norway some I would usually select the Norwegian_100 Collation, but when placing servers in different countries I believe that all the servers should have the same Collation and that this should be the most generic, Latin1_General_100.
It’s important that users in the different countries has
But when users from Norway access the server and sort data in a varchar/nvarchar column with Latin1_General_100, the application has to put a Collating select statement on that column to get the data sorted in Norwegian_100. Wouldn’t this prevent SQL Server from using the defined index optimally?
As an alternative, if there is 3 different Collations in use, we could define 3 columns for each varchar column, one for each collation. Then the application would be responsible for selecting from the correct column. This would lead to triple the amount of indexes that involves the varchar/nvarchar column and the storage space for varchar/nvarchar columns would triple.
Is there any best practices for this or anyone having any experience???