Sql-server – Server default collation change or not change

collationconfigurationmigrationsql serversql-server-2008-r2

The default collation of our new server is SQL_Latin1_General_CP1_CI_AS. However the collation of the other databases which are used as the source to migrate data into the target databases on this server are Latin1_General_CI_AS.

To avoid getting the error while we load data from database 1 (collation:Latin1_General_CI_AS) to database 2 (collation:SQL_Latin1_General_CP1_CI_AS), I changed the collation of database 2 (the target database) to Latin1_General_CI_AS.

However I'm wondering if it might any side-affect?
What is the reason that the server default collation is SQL_Latin1_General_CP1_CI_AS? Is it any better than other collation, in terms of performance, etc?

Thanks for your help.

Best Answer

I worked out this issue and share it here, in case someone needs it in future.

The default collation setting is determined by the server locale. For most English speaking countries is it : Latin1_General_CI_AS

If the server locale setting has been left as default which is English (United States), the collation at time of MS SQL Management Studio would be set to SQL_Latin1_General_CP1_CI_AS.

enter image description here You can check the collation for each local in the following url:

http://msdn.microsoft.com/en-us/library/ms143508.aspx

Related Question