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
    Answerer
  • 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
    Moderator
  • 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
    Moderator
  • 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
    Answerer
  • 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
    Answerer
  • 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
    Moderator
  •  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
    Moderator
  • 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
  • Hi, 

    This is the difference between the collection 

    https://msdn.microsoft.com/en-in/library/ms143508(v=sql.105).aspx

    Default Collations in SQL Server Setup

    In Control Panel, find the Windows system locale name on the Advanced tab in Regional and Language Options. In Windows Vista, use theFormats" tab. The following table shows the corresponding collation designator to match collation settings with an existing Windows locale:

    Windows locale

    Windows LCID

    SQL LCID

    Default collation

    Afrikaans (South Africa)

    0x0436

    0x0409

    Latin1_General_CI_AS





    Bashkir (Russia)

    0x046d

    0x046d

    Latin1_General_CI_AI

    Basque (Basque)

    0x042d

    0x0409

    Latin1_General_CI_AS

    Belarusian (Belarus)

    0x0423

    0x0419

    Cyrillic_General_CI_AS

    Bengali (Bangladesh)

    0x0845

    0x0445

    Not available at server level

    Bengali (India)

    0x0445

    0x0439

    Not available at server level

    Bosnian (Bosnia and Herzegovina, Cyrillic)

    0x201a

    0x201a

    Latin1_General_CI_AI

    Bosnian (Bosnia and Herzegovina, Latin)

    0x141a

    0x141a

    Latin1_General_CI_AI

    Breton (France)

    0x047e

    0x047e

    Latin1_General_CI_AI

    Bulgarian (Bulgaria)

    0x0402

    0x0419

    Cyrillic_General_CI_AS

    Catalan (Catalan)

    0x0403

    0x0409

    Latin1_General_CI_AS

    Chinese (Hong Kong SAR, PRC)

    0x0c04

    0x0404

    Chinese_Taiwan_Stroke_CI_AS

    Chinese (Macao SAR)

    0x1404

    0x1404

    Latin1_General_CI_AI

    Chinese (Macau)

    *0x21404

    0x21404

    Latin1_General_CI_AI

    Chinese (PRC)

    0x0804

    0x0804

    Chinese_PRC_CI_AS

    Chinese (PRC)

    *0x20804

    0x20804

    Chinese_PRC_Stroke_CI_AS

    Chinese (Singapore)

    0x1004

    0x0804

    Chinese_PRC_CI_AS

    Chinese (Singapore)

    *0x21004

    0x20804

    Chinese_PRC_Stroke_CI_AS

    Chinese (Taiwan)

    *0x30404

    0x30404

    Chinese_Taiwan_Bopomofo_CI_AS

    Chinese (Taiwan)

    0x0404

    0x0404

    Chinese_Taiwan_Stroke_CI_AS

    Corsican (France)

    0x0483

    0x0483

    Latin1_General_CI_AI

    Croatian (Bosnia and Herzegovina, Latin)

    0x101a

    0x041a

    Croatian_CI_AS

    Croatian (Croatia)

    0x041a

    0x041a

    Croatian_CI_AS

    Czech (Czech Republic)

    0x0405

    0x0405

    Czech_CI_AS

    Danish (Denmark)

    0x0406

    0x0406

    Danish_Norwegian_CI_AS

    Dari (Afghanistan)

    0x048c

    0x048c

    Latin1_General_CI_AI

    Divehi (Maldives)

    0x0465

    0x0465

    Not available at server level

    Dutch (Belgium)

    0x0813

    0x0409

    Latin1_General_CI_AS

    Dutch (Netherlands)

    0x0413

    0x0409

    Latin1_General_CI_AS

    English (Australia)

    0x0c09

    0x0409

    Latin1_General_CI_AS

    English (Belize)

    0x2809

    0x0409

    Latin1_General_CI_AS

    English (Canada)

    0x1009

    0x0409

    Latin1_General_CI_AS

    English (Caribbean)

    0x2409

    0x0409

    Latin1_General_CI_AS

    English (India)

    0x4009

    0x0409

    Latin1_General_CI_AS

    English (Ireland)

    0x1809

    0x0409

    Latin1_General_CI_AS

    English (Jamaica)

    0x2009

    0x0409

    Latin1_General_CI_AS

    English (Malaysia)

    0x4409

    0x0409

    Latin1_General_CI_AS

    English (New Zealand)

    0x1409

    0x0409

    Latin1_General_CI_AS

    English (Philippines)

    0x3409

    0x0409

    Latin1_General_CI_AS

    English (Singapore)

    0x4809

    0x0409

    Latin1_General_CI_AS

    English (South Africa)

    0x1c09

    0x0409

    Latin1_General_CI_AS

    English (Trinidad and Tobago)

    0x2c09

    0x0409

    Latin1_General_CI_AS

    English (United Kingdom)

    0x0809

    0x0409

    Latin1_General_CI_AS

    English (United States)

    0x0409

    0x0409

    SQL_Latin1_General_CP1_CI_AS

    English (Zimbabwe)

    0x3009

    0x0409

    Latin1_General_CI_AS

    Estonian (Estonia)

    0x0425

    0x0425

    Estonian_CI_AS

    Faroese (Faroe Islands)

    0x0438

    0x0409

    Latin1_General_CI_AS

    Filipino (Philippines)

    0x0464

    0x0409

    Latin1_General_CI_AS

    Finnish (Finland)

    0x040b

    0x040b

    Finnish_Swedish_CI_AS

    French (Belgium)

    0x080c

    0x040c

    French_CI_AS

    French (Canada)

    0x0c0c

    0x040c

    French_CI_AS

    French (France)

    0x040c

    0x040c

    French_CI_AS

    French (Luxembourg)

    0x140c

    0x040c

    French_CI_AS

    French (Monaco)

    0x180c

    0x040c

    French_CI_AS

    French (Switzerland)

    0x100c

    0x040c

    French_CI_AS

    Frisian (Netherlands)

    0x0462

    0x0462

    Latin1_General_CI_AI

    Galician (Spain)

    0x0456

    0x0409

    Latin1_General_CI_AS






    Saturday, October 22, 2016 12:16 PM