Skip to main content

Index Definition Collation

Index Definition Collation

The CREATE INDEX command cannot specify an index collation type. The index uses the same collation as the field being indexed.

An index defined as part of class definition can specify a collation type. By default, an index on a given property (or properties) uses the collation type of the property data. For example, suppose you have defined a property Name of type %StringOpens in a new tab:

Class MyApp.Person Extends %Persistent [DdlAllowed]
{
Property Name As %String;
Index NameIDX On Name;
}

The collation for Name is SQLUPPER (the default for %StringOpens in a new tab). Suppose that the Person table contains the following data:

ID Name
1 Jones
2 JOHNSON
3 Smith
4 jones
5 SMITH

Then an index on Name will contain the following entries:

Name ID(s)
JOHNSON 2
JONES 1, 4
SMITH 3, 5

The SQL Engine can use this index directly for ORDER BY or comparison operations using the Name field.

You can override the default collation used for an index by adding an As clause to the index definition:

Class MyApp.Person Extends %Persistent [DdlAllowed]
{
Property Name As %String;
Index NameIDX On Name As SQLstring;
}

In this case the NameIDX index will now store values in SQLSTRING (case-sensitive) form. Using the data from the above example:

Name ID(s)
JOHNSON 2
Jones 1
jones 4
SMITH 5
Smith 3

In this case, the SQL Engine can take advantage of this index for any queries requiring case-sensitive collation.

In general, you should not have to change the collations of indexes. If you want to use a different collation, it is better to define it at the property level and let any indexes on the property pick up the correct collation.

If you are performing a property comparison using an indexed property, the property as specified in the comparison should have the same collation type as the corresponding index. For example, the Name property in the WHERE clause of a SELECT or in the ON clause of a JOIN should have the same collation as the index defined for the Name property. If there is a mismatch between the property collation and the index collation, the index may be less effective or may not be used at all. For further details, refer to Index Collation.

If your index is defined to use multiple properties, you can specify the collation of each individually:

Index MyIDX On (Name As SQLstring, Code As Exact);
FeedbackOpens in a new tab