.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Server Collation SQL_Latin1_General_CP1_CI_AS versus Latin1_General_CI_AS

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
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_ASWhat 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 ?

View Complete Post

More Related Resource Links

On SQL_Latin1_General_CP1_CI_AS SQL Server 2008 Default Collation


Collation is one of the most confusing features of SQL Server. BOL 2008 states that backward compatibility is the only reason to go with SQL collation. I thought previously it is just smart to stay with the default Microsoft offers unless there is a real good reason to choose a different collation.

So is the default SQL_Latin1_General_CP1_CI_AS  (Case Insensitive, Accent Sensitive) a good choice? (Assume the installation is brand new SQL Server 2008)

Related links:

SQL Server offers the SQL_Latin1_General_CP1_CI_AS collation as the default collation for server installations.

For backward compatibility, the default English-language (US) collation is SQL_Latin1_General*.

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

what's the right collation for sql server 2008 to ignore french accent?

I want to  sql server 2008 collation using english as default(in Canada). when search for text, french accent can be ignore. For example, À,Â,Ä, à, â, ä will be treated same as a or A, but the data still is stored as different(such as for key). so what is the right collation need to be selected when install sql server 2008?. If existing instance collation is not the right one, how to change it to the right one?

MSSQL 2008 R2: Change Server Collation Steps, with user DBs already of desired collation

On the default instance of SQL Server 2008 R2, I want to change the server collation from the usual US default SQL_Latin1_General_CP1_CI_AS to an application vendor's required collation, Latin1_General_BIN.  I understand that changing the server collation is essentially the same as changing the collation of databases master and model -- is that a fair statement? But my main question is, since there are user databases on the server that I would like to preserve, can I not simply detatch the user DBs prior to performing the server collation change, and then reattach?  The database collation of the user databases is already set to Latin1_General_BIN.  This article, Setting and Changing the Server Collation, at http://msdn.microsoft.com/en-us/library/ms179254.aspx says that to preserve the user databases, I should script all DB objects out, and bulk-copy all data out, because the DBs will have to be recreated and repopulated.  Can someone explain why I ought not simply detach and reattach the user DBs, given that they are already of the desired collation (i.e., the same collation that I will be changing the server collation to)? Your help much appreciated. -Tom

Collation SQL 2005 sql_latin1_general_cp1_ci_as

Hi, Our application uses SQL 2005 database with Latin1_General_CI_AS as collation. There are nearly a million records in this database. I need to move my data to another SQL 2005 server which has 'SQL_Latin1_General_CP1_CI_AS ' as collation. I know there would be lots of issues. Do you know what are the likely issues that we would face. Since there are lots of modules dependent on this database, it would be difficult to test each and every component if all works ok or not. Is this advisable to move to another database with this non standard SQL_Latin1_General_CP1_CI_AS  collation?   thanks sANDY  

SQL Server 2008 collation design techniques


Can anyone provide some insight into design techniques when multiple languages are required within SQL Server?

Let me tell you about our situation. We currently have a database that holds French content. New languages are going to be added (Chinese, Arabic etc.). We have to make a decision to determine what would be the best approach to deal with all those languages.


Here are the approaches we identified:


o   I database per language:

Changing Clustered SQL Server 2005 Default Server Collation


We are attempting to change the default collation of an SQL Server 2005 Clustered environment. We have created an "unattended install" script to perform this, and have modified it several times as errors have arisen repeatedly. The last time, however, we used the following script:


We are running this script from the primary (Active) node of the cluster. We did notice that the bootstrap logs created from this final execution did include logs from both nodes. When I look in the Summary.txt file, it tells me that Setup succeeded with the installation. After the script executed, we rolled the cluster from primary to secondary, and rebooted the primary node. We then rolled the cluster back to the primary, and rebooted the secondary node. Once that was complete, we cycled the SQL Server service from the SQL Configuration Manager. After all those steps, we opened SSMS client to look at the properties of master, and the collation was still CI_AI.

We are losing valuable time in

SQL Server 2008 R2 Setup: How to set Collation during interactive setup?



Did I miss something when running SQL 2008 R2 Setup.exe on Win2008 Server R2?  I did not notice my opportunity to set the server collation to something other than my regional default.  Was it on a tab that I missed?

Are my choices either (a) set collation at the OS level through Control Panel...Regional...Advanced, or (b) run setup.exe at the command prompt while specifying the desired server collation?  Because I get that impression from reading this: http://msdn.microsoft.com/en-us/library/ms144260.aspx

Have I read that correctly?




Cannot change the collation sequence of a Database in SQL Server 2008 R2


I tried two steps below separately: restricted user ran for 30 minutes then I stopped the execution; They obviously could not run simultaneously. I only want to operate at the database and not the server level. Our other key db is already in the desired collation. We'd like to have equijoins work on tables with the same collation sequences, and not have to use ETL or individual ALTERs for each table involved.

Second point: is CP1 below a 2000 only collation? That appears to be a patently false assumption in another forum posting. It appears to be a valid 2008 R2 collation is the point.




COLLATE SQL_Latin1_General_CP1_CI_AS ;


Many Regards,







i have 2 data bases source(2005), destination (2008). i took a back up of database from 2005(SQL_Latin1_General_CP1_CI_AS) and restored in 2008(Latin1_General_CI_AS). now while running sp's, or any other queries, i am getting this error.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I donot want to re install my server as it is a big task , and i already restored DB's which are also a lot . so can anyone please sugegst a solution apart from the above two(reinstalling SQL, re-restoring DB's).




collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS"


Hello Everyone,


I am getting the following issue when u am running my query. Can anyone please tell me what i need to do on this to resolve.


Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation



Anil Kumar

SQL Server Express Nulls versus empty strings??



I have a SQL Server column FileLocation which is declared
varchar(150), not null

Some of the values look like nulls to me ..
len(FileLocation) gives zero length

Isnull(ltrim(rtrim(FileLocation)), 'test')

..does not bring back 'test' - why is this?

Do I need a patch for my sql server version? a service pack?


Sql server collation


we would like to update to SQL server 2005 or 2008. Now we are using SQL server express (several instances). Our databases have different collation.
Can we set up databases with different collation within one instance of SQL server 2005 or 2008?



Timer Jobs - All Servers versus Any Server

I have numerous OOB timer job definitions that have an entry for All Servers and another entry for Any Server.  The only documentation I can find on the differences is that All Servers means the job runs on each server, whereas Any Server means it runs on one server.  Do I need both of these running?  If so, why?

Should I change my Server Collation ???


My server collation is SQL_Latin1... as all my databases were upgrade from earlier version.

Should I plan a change to the collation to a window's based collation?

SQL Server Collation on German OS


How do i install SQL Server 2005 on a German OS with SQL collation as SQL_Latin1_General_CP1_CI_AS.

(By default the collation is set as Latin1_General_CI_AS)

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend