Let's assume the following simple example:
- User_id (indexed, primary unique)
I am curious about the different approaches to sorting records on non-indexed columns:
- Using JetOpenTempTable to create an in-memory non-clustered index "by hand", then iterating over the temp table and manually seeking to each row in the "Users"-table
- Using JetOpenTempTable and add all relevant columns, then iterating over the temp table and outputting each sorted row
(JetOpenTempTable can forward-only sort on a text column that is max 127 chars with codepage=unicode)
Today we sort non-indexed columns by creating a sort string, and then adding the sort string, row primary key and table name to an array. This array is then quicksorted based on the sort string (in-memory).
Afterwards we sequentially process each item from the sorted array, and perform a primary key seek to each item in the array (similar to a non-clustered index).