NexusDB

The most efficient way of restricting a set of records based on a set of criteria is through the use of a range. Ranges are index based, so they are extremely fast. Ranges are set by specifying start and end values for the field or set of fields that are part of the TnxTable’s current index. Ranges are contiguous and start/end values are evaluated from left to right.

Suppose you got a couple of records with an index 'abc' on 2 integer fields. The Index is then a sorted list like this:

You can set a range on just the first field with a start and end value of 2.

This will limit the "visible"/accessable records in above index list, where Field1 is "between 2 and 2". Since there's no Field2 values supplied, there's no further restriction. Please note that the KeyExclusive property defines how the "between" is interpreted. If it is false (default) then the supplied values are included in the range, if it is false then they are excluded.

Assuming the default of KeyExclusive:=false you will get 3 records inside that range (you will get no records for KeyExclusive:=true):

You can of also restrict both fields:

This will look for a the first and last entry in above index list, where Field1 is "between 2 and 2" and then further restrict it to where "Field 2 is between 2 and 3". Which results in 2 records:

Since ranges are evaluated left to right it's impossible to only get all records with for example the value 2 in the 2nd field of the index.

Please note that setting a field value to NULL in the range, means that you want to set a range on all records where this field is null.