docs.intersystems.com
InterSystems IRIS Data Platform 2019.2  /  Using InterSystems SQL  /  Collation

Using InterSystems SQL
Index Definition Collation
Previous section           Next section
InterSystems: The power behind what matters   
Search:  


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 %String:
Class MyApp.Person Extends %Persistent [DdlAllowed]
{
Property Name As %String;
Index NameIDX On Name;
}
The collation for Name is SQLUPPER (the default for %String). 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 indices. If you want to use a different collation, it is better to define it at the property level and let any indices 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 in the “Defining and Building Indices” chapter of the SQL Optimization Guide.
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);


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-10-21 06:33:58