SQL Server Collations

• PearlJammer1

  SSCrazy Eights

  Points: 8485

  More actions

  November 25, 2020 at 12:26 pm

  #3813834

  So can anybody tell me if they have experienced issues when running a SQL instance level collation at Latin1_General_CI_AS when any of their databases on that instance run under SQL_Latin1_General_CP1_CI_AS.

  Am I correct in saying the SQL_Latin1_General_CP1_CI_AS is the 'old style' collation?

  The reason I ask is that we have been installing a new environment using the Latin1_General_CI_AS and some, but not all, of the databases are set to SQL_Latin1_General_CP1_CI_AS. Also, from the old environment I have seen this setup configuration and also vice-versa with SQL_Latin1_General_CP1_CI_AS as the instance default and Latin1_General_CI_AS as the database and the 2 different collations seem to have been living 'side by side' for a number of years with no reported issues. So is it more of a "you could have issues" but more than likely you wont have issues as these 2 collations are very similar - if not the same!! I appreciate if one of the collations, for example, was set to _CS and one was _CI it might pose a bigger problem when using tempdb.

  Any advice or thought greatly appreciated!

  Thanks

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

• Mr. Brian Gale

  SSC-Insane

  Points: 24799

  More actions

  November 25, 2020 at 5:06 pm

  #3813939

  Looks like someone asked about this on the MSDN forums and got a few answers:

  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/196b4586-1338-434d-ba8c-49fa3c9bdeeb/server-collation-sqllatin1generalcp1cias-versus-latin1generalcias

  The important bits from the above forum post (the whole thing is a good read mind you):

  You should see little difference if the collation is SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS, but both have instances where they are faster or slower than the other.

  Latin1_General_CI_AS :- Latin1-General, case-insensitive, accent-

  sensitive, kanatype-insensitive, width-insensitive

  SQL_Latin1_General_CP1_CI_AS:- Latin1-General, case-insensitive,

  accent-sensitive, kanatype-insensitive, width-insensitive for Unicode

  Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

  You can get more idea from fn_helpcollations.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.