Can SQL Server SQL_Latin1_General_CP1_CI_AS be safely converted to Latin1_General_CI_AS?

sql sql-server collation
Kram picture Kram · Jun 9, 2011 · Viewed 68.7k times · Source

We have a legacy database with some (older) columns using "SQL_Latin1_General_CP1_CI_AS" and more recent changes have used "Latin1_General_CI_AS".

This is a pain as joins need the additional COLLATE statement to work.

I'd like to bring everything up to "Latin1_General_CI_AS". From what I can gather they are more or less identical collations and I won't lose data during this process...

Does anyone know if this is the case?

Answer

Zarepheth picture Zarepheth · Feb 27, 2014

Here is a more complete answer:

https://www.olcot.co.uk/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as/

The key difference between these collations is in how they apply character expansion rules. Certain Latin characters may be expanded into multiple characters. The SQL_xxxx collations may ignore these character expansions when working with non-unicode text, but apply them for unicode text. As a result: joins, sorts, and comparisons may return different results when using one collation versus the other.

Example:

Under Latin1_General_CI_AS these two statements return the same set of records, as ß is expanded to ss.

SELECT * FROM MyTable3 WHERE Comments = 'strasse'
SELECT * FROM MyTable3 WHERE Comments = 'straße'

When using SQL_Latin1_General_CP1_CI_AS the above statements return different records, since the ß is treated as a different character than ss.

Related questions

Cannot Resolve Collation Conflict

I have moved one of our databases (DB1) from SQL Server 2008 to 2012 and when I run the stored procedures I get the following error Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_…

Read More
store arabic in SQL database

I tried to store Arabic string in SQL 2008 database but it converted to " question mark " why ? and what should I do ?

Read More
Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and SQL_Latin1*

I can't update temp table. This is my query CREATE TABLE #temp_po(IndentID INT, OIndentDetailID INT, OD1 VARCHAR(50), OD2 VARCHAR(50), OD3 VARCHAR(50), ORD VARCHAR(50), NIndentDetailID INT, ND1 VARCHAR(50), ND2 VARCHAR(50), ND3 VARCHAR(50), NRD VARCHAR(50), Quantity DECIMAL(15,3)) INSERT INTO #temp_…

Read More