none
Server Collation SQL_Latin1_General_CP1_CI_AS versus Latin1_General_CI_AS

    Question

  • Hi,
    Why does SQL Server 2005 sometimes install with a server collation of :

    Server Collation SQL_Latin1_General_CP1_CI_AS

    and other times a server collation of :
    Latin1_General_CI_AS

    What causes this ???

    What is the difference between the two ?

    Are there any problems restoring databases from one to the other ?

    Do the two different collations cause problems with applications ?
    Wednesday, June 10, 2009 4:47 AM

Answers

  • As you might know that the collation decides the sorting, data saving in pages. Qingsong said "In most of case, people will use Windows collation as the collation, except us-English which still use sql_latin1_general_cp1_ci_as." and you could visit his blog to see detailed information.

    If your database is different to the instance collation, then you can have issues when joining to temporary tables or to tables in other databases, to get around this you can specify the collation of columns when creating temporary tables or force a collation using the COLLATE clause when comparing (joining) two text columns. Using the option database_default for the collation will make this independent of what the database collation actually is. See more about the COLLATE clause in Books Online.

    It is better to stick to a single collation globally. Otherwise you will experience problems when working with temporary tables. And although it's always easier if collation was consistent through out everywhere you have your application, but if you are installing one a shared instance at a customer it is not always possible. Therefore in general it is best not to force or assume a collation except where it is necessary e.g. you may want a binary collation to force order and case sensitivity.

    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.

    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    Monday, June 15, 2009 10:08 AM
  • BTW, I got the exact same error for a client when we join results of the SP with the the table. Could it be something related to the way I wrote the SP (in the SP I'm using temp table).

    Just in case I added COLLATE SQL_Latin1_General_CP1_CI_AS to join conditions that seemed to resolve the problem. 



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, July 15, 2011 10:13 PM
  • Changing the collation on database level didn't do the trick: what else is necessary to change the collation for the database to "SQL_Latin1..."?

    As noted above, that changed only the default. Even within a table you could have different collations. Collation is a column property, therefore you would need to change the collation for each column:

    http://www.sqlusa.com/bestpractices/changecollation/

    Quick fix: add COLLATE DATABASE_DEFAULT on the right hand side where error detected.

    Related link: http://sqlusa.com/bestpractices2005/collatedatabasedefault/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    Sunday, August 15, 2010 6:41 AM
  • Take a look at this article:

    http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as

    Here are the article's conclusions:

    "So to answer the question that I was asked regarding these two collations, I would have to say this: With varchar based data, there are small but significant differences with the sorting and comparison of data if you used the collation SQL_Latin1_General_CP1_CI_AS or if you used Latin1_General_CI_AS. Character expansion is one such difference and this difference is caused by the fact that the rules that both collations use for sorting/comparing are different. The rules for nvarchar data sorting/comparisons seem to be identical though from the tests that I have been able to do. Mixing the collations at column, database or instance level could impact on your performance quite heavily or cause collation conflict errors and mixing comparisons of varchar and nvarchar data as well can seriously impact on performance especially if you are using the older SQL collation. Keeping tabs on your collation settings across your SQL platform is very important.

    "There are various recommendations or rules of thumbs on what collation you should use for new instances as there are pros and cons for both but the general consensus tends to lean towards using the newer windows collations rather than the older SQL collations. But as always with SQL development, you need to evaluate all options and then test those options to see what works best within your infrastructure."

    I have decided to re-install all new servers and objects to match collations.  Fun!

    Friday, July 20, 2012 4:00 PM

All replies

  • Qingsong from MSSQL team wrote this in his msdn blog http://blogs.msdn.com/qingsongyao/default.aspx (Collation, DateTime, SParse Column and XML)

    "I am a Unicode Fan, and always want recommend people using Unicode. So when I knew this parameter,  my first question is that WOW, set the value to false has potential data corruption. When we convert the UTF-16 encoded string into a codepage encoded string, we may face the issue of some character is not defined in the code page, and will be replace as question mark (?).  Then, after I read this Article, I knew the reason of doing so is to enabling index seek when the target data type in SQL Server is varchar type.  Note, the Article ONLY apply when the collation of your column is a SQL Colaltion, but NOT Windows collation.  In most of case, people will use Windows collation as the collation, except us-English which still use sql_latin1_general_cp1_ci_as.  So If the target type is nvarchar type or your collation is Windows Collation,  don't set the parameter to false."

    Hope this helps.
    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    Wednesday, June 10, 2009 6:07 AM
  • I'm afraid I don't see how this answers my questions.....
    Wednesday, June 10, 2009 10:14 AM
  • As you might know that the collation decides the sorting, data saving in pages. Qingsong said "In most of case, people will use Windows collation as the collation, except us-English which still use sql_latin1_general_cp1_ci_as." and you could visit his blog to see detailed information.

    If your database is different to the instance collation, then you can have issues when joining to temporary tables or to tables in other databases, to get around this you can specify the collation of columns when creating temporary tables or force a collation using the COLLATE clause when comparing (joining) two text columns. Using the option database_default for the collation will make this independent of what the database collation actually is. See more about the COLLATE clause in Books Online.

    It is better to stick to a single collation globally. Otherwise you will experience problems when working with temporary tables. And although it's always easier if collation was consistent through out everywhere you have your application, but if you are installing one a shared instance at a customer it is not always possible. Therefore in general it is best not to force or assume a collation except where it is necessary e.g. you may want a binary collation to force order and case sensitivity.

    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.

    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    Monday, June 15, 2009 10:08 AM
  • Hi,

     

    my problem is, that we have "Latin1" Databases,  that at one customer have conflicts with a "SQL_Latin1" server and its tempdb.

    Changing the collation on database level didn't do the trick: what else is necessary to change the collation for the database to "SQL_Latin1..."?

     

    Thank You very much in advance.

    Friday, August 13, 2010 12:32 PM
  • First, I'm not sure if this is a new question or some answer to a previous thread.  If you want to ask a question, you should start a new thread.

    In your case, changing the collation on a database level will only change the default collation to be used for new columns/new tables; it will change nothing to existing tables/columns or when you directly specify the collation when creating a table or a column.

    In the case of the problem with the tempdb, you must use the database_default option when creating your temporary tables in the tempdb database while also making sure that the current database is set to the proper database (and not set to the tempdb database as it is too often the case) and that the default collation for the current database is the correct collation to use.

    See the following the article for all the details:
    http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1368.entry

    The other possibility would be to rebuild the database using the correct collation.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <chirruped> wrote in message news:decf7221-80a9-46ed-8c85-0972b516490a@communitybridge.codeplex.com...

    Hi,



    my problem is, that we have "Latin1" Databases, that at one customer have conflicts with a "SQL_Latin1" server and its tempdb.

    Changing the collation on database level didn't do the trick: what else is necessary to change the collation for the database to "SQL_Latin1..."?



    Thank You very much in advance.

    Saturday, August 14, 2010 10:57 PM
  • Changing the collation on database level didn't do the trick: what else is necessary to change the collation for the database to "SQL_Latin1..."?

    As noted above, that changed only the default. Even within a table you could have different collations. Collation is a column property, therefore you would need to change the collation for each column:

    http://www.sqlusa.com/bestpractices/changecollation/

    Quick fix: add COLLATE DATABASE_DEFAULT on the right hand side where error detected.

    Related link: http://sqlusa.com/bestpractices2005/collatedatabasedefault/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    Sunday, August 15, 2010 6:41 AM
  •  I believe this is due to the systems (Windows OS) regional setting at the time of SQL’s installation.

    SQL_Latin1_General_CP1_CI_AS is English (United States) and Latin1_General_CI_AS is correct for English (United Kingdom) and number of other countries as per the following link

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

    Friday, August 27, 2010 7:34 AM
  • BTW, I got the exact same error for a client when we join results of the SP with the the table. Could it be something related to the way I wrote the SP (in the SP I'm using temp table).

    Just in case I added COLLATE SQL_Latin1_General_CP1_CI_AS to join conditions that seemed to resolve the problem. 



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, July 15, 2011 10:13 PM
  • Take a look at this article:

    http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as

    Here are the article's conclusions:

    "So to answer the question that I was asked regarding these two collations, I would have to say this: With varchar based data, there are small but significant differences with the sorting and comparison of data if you used the collation SQL_Latin1_General_CP1_CI_AS or if you used Latin1_General_CI_AS. Character expansion is one such difference and this difference is caused by the fact that the rules that both collations use for sorting/comparing are different. The rules for nvarchar data sorting/comparisons seem to be identical though from the tests that I have been able to do. Mixing the collations at column, database or instance level could impact on your performance quite heavily or cause collation conflict errors and mixing comparisons of varchar and nvarchar data as well can seriously impact on performance especially if you are using the older SQL collation. Keeping tabs on your collation settings across your SQL platform is very important.

    "There are various recommendations or rules of thumbs on what collation you should use for new instances as there are pros and cons for both but the general consensus tends to lean towards using the newer windows collations rather than the older SQL collations. But as always with SQL development, you need to evaluate all options and then test those options to see what works best within your infrastructure."

    I have decided to re-install all new servers and objects to match collations.  Fun!

    Friday, July 20, 2012 4:00 PM