Collation sequences

Owned by Richard Kennedy (Unlicensed)
Last updated: Jun 26, 2019
3 min read

Collation sequences are database settings that control how string values are ordered. This topic includes information about:

  • Pre-Version 68 collation sequences
  • Version 68 collation sequences
  • Collation sequence examples.


Collation sequences (Pre-Version 68)

Synergetic versions prior to version 68 require the SQL collation 'SQL_Latin1_General_CP1_CI_AS'.

SQL Server had to be installed with this specific collation sequence and this determined the sequence of master and temporary databases of the SQL Server instance.

This had the following limitations:

  • SQL Server instances had to be installed with the SQL_Latin1_General_CP1_CI_AS collation sequence
  • SQL Server instances with different collation sequences had to be reinstalled
  • Organisations could not switch to an accent insensitive collation sequence to change search functionality.


Collation sequences (Version 68 onwards)

Synergetic supports multiple collation sequences from Version 68 onwards. This allows organisations to:

  • choose to use the default collation sequence or switch to any Case Insensitive (CI) collation sequence for the Synergetic database
  • use different collation sequences for the Synergetic database and the SQL Server instance.

Tip: You can enable case sensitive/insensitive or accent sensitive/insensitive searching using alternative collation sequences.

Note: Synergetic Version 68 uses the Latin1_General_CI_AS collation sequence by default.


Collation sequence examples

Example 1: Resolving errors caused by comparing data across databases

Organisations with user triggers or stored procedures that utilise the tempdb database may encounter the errors if tempdb and the Synergetic database use different collation sequences.

Most issues arise when you are comparing table string fields across databases. The following query will not work because we are comparing string values across databases with different collation sequences.

This issue is resolved by specifying the collation on each column that will be compared with a field in the source database as follows.

Note: You only need to specify collation sequences on fields you are using for cross-database comparisons. For example, Given1 does not need a collation sequence as it is only being displayed.

Tip: Synergetic recommends specifying collation sequences with table variables as they may use the tempdb database with larger volumes of data.

Note: Temp tables created using SELECT INTO statements do not cause any collation issues because the statement copies the table structure including any collation sequences. Issues only arise when you CREATE then INSERT data.


Example 2: Using system tables

When you are using system tables you cannot change the collation sequence of the table. Instead, you must override the collation sequence on the field comparison as shown below.


Example 3: Contained databases with OPENXML

Organisations using contained databases with OPENXML may encounter errors similar to the following error.

For example, the following code causes a collation error in a contained database.

This type of error can be resolved by writing the query as follows.