Collation Tables

A “built-in” collation table created by the sortkey function. This function exists in versions of SAP ASE later than 11.5.1. You can use either the collation name or the collation ID to specify a built-in table.

An external collation table that uses the Unilib library sorting functions. You must use the collation name to specify an external table. These files are located in $SYBASE/collate/unicode.

Both of these methods work equally well, but a “built-in” table is tied to a SAP ASE database, while an external table is not. If you use an SAP ASE database, a built-in table provides the best performance. Both methods can handle any mix of English, European, and Asian languages.

In-line – this uses sortkey as part of the order by clause and is useful for retrofitting an existing application and minimizing the changes. However, this method generates sort keys on-the-fly, and therefore does not provide optimum performance on large data sets of moe than 1000 records.

Pre-existing keys – this method calls sortkey whenever a new record requiring multilingual sorting is added to the table, such as a new customer name. Shadow columns (binary or varbinary type) must be set up in the database, preferably in the same table, one for each desired sort order such as French, Chinese, and so on. When a query requires output to be sorted, the order by clause uses one of the shadow columns. This method produces the best performance since keys are already generated and stored, and are quickly compared only on the basis of their binary values.