Table Field/Property Definition Collation
Within SQL, collation can be assigned as part of field/property definition. The data type used by a field determines its default collation. The default collation for string data types in tables that use row storage is SQLUPPER; however, in tables that use columnar storage, the default collation is EXACT. Non-string data types do not support collation assignment.
You can specify collation for a field in CREATE TABLE and ALTER TABLE:
CREATE TABLE Sample.MyNames (
LastName CHAR(30),
FirstName CHAR(30) COLLATE SQLstring)
Note:
When specifying collation for a field using CREATE TABLE and ALTER TABLE, the % prefix is optional: COLLATE SQLstring or COLLATE %SQLstring.
You can specify collation for a property when defining a table using a persistent class definition:
Class Sample.MyNames Extends %Persistent [DdlAllowed]
{
Property LastName As %String;
Property FirstName As %String(COLLATION = "SQLstring");
}
Note:
When specifying collation for class definitions and class methods do not use the % prefix for collation type names.
In these examples, the LastName field takes default collation (SQLUPPER, which is not case-sensitive), the FirstName field is defined with SQLSTRING collation, which is case-sensitive.
If you change the collation for a class property and you already have stored data for that class, any indexes on the property become invalid. You must rebuild all indexes based on this property.
