Documentation

Collations and language support

A collation specifies the sort order and presentation format of data. Babelfish maps SQL Server collations to comparable collations provided by Babelfish. Babelfish predefines Unicode collations with culturally-sensitive string comparisons and sort orders, and provides a way to translate the collations in your SQL Server DB to the closest-matching Babelfish collation. Locale-specific collations are provided for different languages and regions.

Some collations specify a code page that corresponds to a client-side encoding. Babelfish will automatically translate from the server encoding to the client encoding depending on the collation of each output column.

For detailed information about PostgreSQL collation behavior, visit the PostgreSQL website.

Babelfish supports deterministic and non-deterministic collations:

• A collation that is deterministic will consider two characters as equal if, and only if, they have the same byte sequence. For example, x is not equal to X for a deterministic collation. Collations that are deterministic are case-sensitive (cs) and accent-sensitive (as).

• A non-deterministic collation does not require an identical match. Case-insensitivity (ci) is an example of a non-deterministic characteristic. To have x compare equal to X, choose a non-deterministic collation that supports case-insensitivity.

Babelfish and SQL Server follow a naming convention for collations that describe the collation attributes, as shown in the table below:

PostgreSQL doesn’t support the LIKE clause on non-deterministic collations, but Babelfish supports it for ci_as collations. Babelfish doesn’t support LIKE on ai collations. Pattern matching operations on non-deterministic collations are also not supported.

To establish Babelfish collation behavior, set the following parameters:

The following collations can be used in server_collation_name or for column definitions. Note that only case insensitive collations are supported.

For column definitions, you can also use case sensitive or accent insensitive versions of the above collations. The following binary collations are also supported for columns:

You can use the following collations for column definitions, if you want to support more than one locale in a single database:

Managing collations

The ICU library provides collation version tracking to ensure that indexes that depend on collations can be reindexed when a new version of ICU becomes available. You can use the following query to identify all collations in the current database that need to be refreshed and the objects that depend on them:

SELECT 	pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation", 
	pg_describe_object(classid, objid, objsubid) AS "Object" 
FROM 	pg_depend d JOIN pg_collation c 
	ON refclassid = 'pg_collation'::regclass 
		AND refobjid = c.oid 
WHERE 	c.collversion < > pg_collation_actual_version(c.oid) 
ORDER BY 1, 2;

Predefined collations are stored in the sys.fn_helpcollations table. You can use the following command to display information about a collation (such as its lcid, style, and collate flags). To retrieve the list, connect a psql client to the PostgreSQL port (by default, 5432) and enter:

postgres=# SET search_path = public, pg_temp, sys;
SET
postgres=# \dO

Connect to the T-SQL port (by default 1433) and enter:

SELECT * FROM fn_helpcollations();

Collation limitations and behaviors

Babelfish uses the ICU library for collation support. The following section lists some of the known limitations and behavior variations of Babelfish collations.

Using CHARINDEX() with a non-deterministic collation

Note: the following limitation applies only to Babelfish version 1.x.x. This issue has been resolved in version 2.1.0.

CHARINDEX() cannot currently be used when the applicable collation is non-deterministic. Babelfish (by default) uses a non-deterministic case-insensitive collation, so you may encounter a run-time error saying “nondeterministic collations are not supported for substring searches”. Until this is resolved, this issue can be worked around in two ways:

• You can explicitly convert the expression to a case-sensitive collation and case-fold both arguments by applying LOWER() or UPPER(). For example: SELECT CHARINDEX('x', a) from t1 becomes: SELECT CHARINDEX(LOWER('x'), LOWER(a collate sql_latin1_general_cp1_cs_as)) from t1
• Create a SQL function named f_charindex(), and replace CHARINDEX() calls with calls to this function:

create function f_charindex(@s1 varchar(max), @s2 varchar(max)) returns int
as
begin
declare @i int = 1
while len(@s2) >= len(@s1)
begin
   if lower(@s1) = lower(substring(@s2,1,len(@s1))) return @i
   set @i += 1
   set @s2 = substring(@s2,2,999999999)
end
return 0
end
go

Unicode sorting rules

In SQL Server, “SQL” collations (those that start with the letters SQL_) sort Unicode-encoded data (nchar and nvarchar) one way, but non-Unicode encoded data (char and varchar) a different way. Babelfish databases will always be UTF-8 encoded and will always apply Unicode sorting rules consistently, regardless of the data type.

Secondary-equal collations

The default ICU Unicode secondary-equal (ci_as) collation sorts punctuation marks and other non-alphanumeric characters before numeric characters, and numeric characters before alphabetic characters; however, within the set of punctuation or special characters, the order may be different from that of the corresponding collation in SQL Server.

Tertiary collations

SQL collations, such as sql_latin1_general_pref_cp1_ci_as, support the TERTIARY_WEIGHTS function and the ability to sort strings that compare equally in a ci_as collation to be sorted upper case first: ABC, ABc, AbC, Abc, aBC, aBc, abC, and finally abc. Thus the DENSE_RANK() OVER (ORDER BY column) analytic function would assess these strings as having the same rank but would order them upper case first within a partition.

A similar result can be obtained with Babelfish by adding a COLLATE clause to the ORDER BY clause that specifies a tertiary cs_as collation that specifies @colCaseFirst=upper. Unfortunately the colCaseFirst modifier applies only to strings that are tertiary-equal (rather than secondary-equal like a ci_as collation); therefore, tertiary SQL collations cannot be emulated using a single ICU collation. As a workaround, we recommend that you use a different collation for GROUP BY than you use for ORDER BY.

PostgreSQL supports exactly one version of each collation

PostgreSQL supports exactly one version of each collation, whereas SQL Server can support multiple versions of the same collation, typically by modifying the collation name with a version suffix such as _80 or _100.

Character expansion

A character expansion treats a single character as equal to a sequence of characters at the primary level. SQL Server’s default ci_as collation supports character expansion, whereas ICU collations support character expansion only for accent-insensitive collations.

Character reordering options are currently more restricted in PostgreSQL than in ICU, and the order of special characters can’t be made to follow the order of special characters in SQL Server exactly.

When character expansion is required, then an ai collation must be used for comparisons, but such collations are not currently supported by the LIKE operator. A column declared using the default ci_as collation can use the LIKE operator without character expansion being performed, which is typically the desired behavior, while preserving the option to explicitly collate any equality comparisons to use a ci_ai collation to do character expansion. If you create a functional index on a ci_as column with an explicit ci_ai collation, then such an index can be exploited by the optimizer to perform equality and grouping operations efficiently.

char and varchar encoding

When collations that begin with SQL are used for char or varchar data types, the sort order for characters above ASCII 127 is determined by the specific code page for that SQL collation. For SQL collations, strings declared as char or varchar may sort in a different way from strings declared as nchar or nvarchar.

PostgreSQL encodes all strings with the database encoding, so will convert all characters to UTF-8 and sort using Unicode rules.

Since SQL collations sort nchar and nvarchar data types using Unicode rules, Babelfish encodes all strings on the server using UTF-8, and sorts nchar and nvarchar strings the same way it sorts char and varchar strings, using Unicode rules.

Supplementary Character

The SQL Server functions NCHAR, UNICODE, and LEN support characters for code-points outside the Unicode Basic Multilingual Plane (BMP), whereas non-SC collations use surrogate pair characters to handle supplementary characters. For Unicode data types, SQL Server can represent up to 65,535 characters using UCS-2, or the full Unicode range (1,114,111 characters) if supplementary characters are used. Supplementary characters aren’t supported for use in metadata, such as in names of database objects, and collations without SC in the name behave differently in SQL Server depending on whether the encoding is UTF-16 or UCS-2.

Kana-Sensitive

When Japanese Kana characters Hiragana and Katakana are treated differently, the collation is called Kana sensitive (KS). ICU supports the Japanese collation standard JIS X 4061. The now deprecated colhiraganaQ [on | off] locale modifier may provide the same functionality as KS collations, but KS collations of the same name as SQL Server will not be provided by Babelfish in the current release.

Width-Sensitive

When a single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently, the collation is called width-sensitive (WS). WS collations with the same name as SQL Server won’t be provided by Babelfish in the current release.

Variation-Selector Sensitivity

Variation-Selector Sensitivity (VSS) collations distinguish between ideographic variation selectors in Japanese collations Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140. A variation sequence is made up of a base character plus an additional variation selector. If you don’t the select the _vss option, the variation selector isn’t considered in the comparison.

VSS collations will not be provided by Babelfish in the current release.

bin and bin2

A bin2 collation sorts characters according to code point order. The byte-by-byte binary order of UTF-8 preserves Unicode code point order, so this is also likely to be the best-performing collation. If Unicode code point order works for an application, using a bin2 collation should certainly be considered, but could result in data being displayed on the client in an order that is culturally unexpected. New mappings to lower-case characters are added to Unicode as time progresses, so the lower function may perform in a different way on different versions of ICU. This is a special case of the more general collation versioning problem rather than something specific to the bin2 collation. Babelfish will provide the bbf_latin1_general_bin2 collation to collate in Unicode code point order. In a BIN collation only the first character is sorted as a wchar, and remaining characters are sorted byte-by-byte, effectively in code point order according to its encoding. This does not follow Unicode collation rules and it won’t be supported by Babelfish.