Collation Sequences

Owned by Julie Main (Deactivated)
Last updated: Oct 29, 2018 by James Overell
2 min read

Background

A collation sequence is a database setting that controls how string values are ordered.

In Synergetic V67 and all prior versions the collation sequence was required to be the SQL collation 'SQL_Latin1_General_CP1_CI_AS'.  The SQL server must have been installed with that specific collation sequence and that determined the collation sequence of the master and temp databases of that SQL Server instance.

This caused the following limitations at a client organisation:

  • The Synergetic databases had to be installed on a SQL instance configured with the specific SQL collation sequence required by Synergetic.
  • If the host SQL Server instance was installed with a different collation sequence the instance could not be used and had to be reinstalled.
  • The client organisation could not change to an accent insensitive collation sequence to change search functionality.

Alongside these limitations, it seems that SQL_ collation sequences are no longer being updated and will eventually be phased out in favour of Windows collation sequences.  Some of the newer Unicode characters are not in the SQL collation but do exist in Windows ones. 

Changes in V68

Changes have been made to support different collation sequences to remove these limitations and future proof Synergetic.

In V68, the collation sequence of the Synergetic database can be changed to any Case Insensitive (CI) collation sequence and is also permitted to be different to the collation sequence of the SQL Server instance.

By default, the Synergetic database in V68 will be changed to the Windows collation sequence Latin1_General_CI_AI

Benefits of Alternate Collation Sequences

The main noticeable difference of using alternate collation sequences is when performing searching:

  • You can have case sensitive/insensitive searching.
  • Accent insensitive searching, e.g. looking for "Andre" will reveal results for Andre and André
 

For most client organisations this change will be invisible.  However, if the organisation has created their own user triggers or user stored procedures that use the tempdb and the Synergetic database uses a different collation sequence to tempdb, they may encounter an error such as the below:

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

If this type of error is encountered the collation sequence will need to be explicitly specified as outlined in the next section.

Further information: