CA Process Automation: New collation for SQL Server 2012 and later

Products

• CA Process Automation Base

Issue/Introduction

As of Sql Server 2012, the default collation for English has changed to SQL_Latin1_General_CI_AS.

The installation for CA Process Automation still sets the collation to SQL_Latin1_General_CP1_CI_AS, however this can be modified on the install screens.

Can I use the Sql Server collation SQL_Latin1_General_CI_AS for CA Process Automation installations instead of the default SQL_Latin1_General_CP1_CI_AS?

Environment

CA Process Automation versions 4.2 Sp02 through currentMicrosoft Sql Server 2012, 2014, 2016

Resolution

Short answer - Yes. The collation Latin1_General_CI_AS can be used for CA Process Automation.

Long answer:

There is not a lot of difference between the two collations:

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 

 

SQL_Latin1_General_CP1_CI_AS was the normal default for US English SQL Server databases as this is a SQL Collation, though For SQL Server 2012 the default has changed to the Windows collation. 

If your Database Server was created using the collation SQL_Latin1_General_CI_AS, then you need to maintain that collation across the database instances. Do not change the instance collation to be something different than the server.

Latin1_General_CI_AS is a Windows collation and can use an index when comparing unicode and non unicode data, where SQL_Latin1_General_CP1_CI_AS is a SQL collation and cannot do this. What this means is that and index search is done using Latin1_General_CI_AS which is much quicker than the index scan done using Latin1_General_CP1_CI_AS. An index scan can also cause performance issues on larger database instances. 

You should also take into consideration if your user base will be using only English or there will be a mix of languages. Latin1_General_CI_AS does character expansions, where SQL_Latin1_General_CP1_CI_AS does not. You would need to discuss this further with your DBA since this will really depend on what the DB Server collation is set as, and what your needs are based on SQL vs Windows collation. 

In general, Latin1_General_CI_AS will actually be the better choice, but if your server collation is still set to Latin1_General_CP1_CI_AS, do not change the instance collation to be different.