Previous icon

Next icon

Collation Sequences

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

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:

Collation sequences (Version 68 onwards)

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

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.

Collation example (Erroneous example)

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

Collation example (Resolved example)

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.

Collation example (System tables example)

Example 3: Contained databases with OPENXML

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

Collation error

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

Collation example (Erroneous XML example)

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

Collation example (Working XML example)

Last modified: 24/05/2017 12:29:43 PM

See Also

Single database structure

Database naming

Database synonyms

Database schemas

Contained databases

Updating Crystal Reports for the single database structure

Updating MS Query queries for the single database structure

Maintaining public finance role security access in the single database structure

© 2017 Synergetic Management Systems. Published 20 July 2017.

Top of page