We are happy to release next version of kbmMemTable!
- Compiles with newer versions of Delphi including 13 and MIMERPascal (MIMER Polyglot Compiler)
- Full text search indexing with configurable tokenizer and stemming
- Significant performance updates in SQL statements
- Significant additional SQL statement coverage incl. all JOIN types
- Optional automatic journal based data persistence
- Improvements and fixes.
Give users a search box over a table of support tickets, and they’ll soon want to find an exact phrase, search by the start of a word, or leave closed tickets out of the results. With kbmMemTable 8.25.00, one full-text index can handle all three. This release also brings faster indexed queries, SQL transactions with savepoints, and an optional journal that can restore committed changes when a table reopens.
Full-text search that follows your data
You can make ticket titles and descriptions searchable with a single kbmMemSQL statement:
CREATE FULLTEXT INDEX ticket_text ON tickets (title, description)
The index includes existing tickets immediately and stays up to date as tickets are added, edited or deleted. The CONTAINS predicate supports exact phrases such as "connection reset", prefixes such as time*, and exclusions such as timeout -printer. If you work through the dataset API, FullTextSearch returns the match count, and FullTextFindFirst and FullTextFindNext let you walk the matches in the table’s current index order.
One particularly handy feature is the ability to build a full-text index from an existing filtered index. If you already have an index of open tickets, you can use it as the basis for your search. Close a ticket and it drops out of that search. Reopen it and it becomes searchable again.
A 100,000-row lookup in 0.6 ms
kbmMemSQL now uses an index seek when an eligible WHERE condition tests the leading column of an index. This works for equality checks and ranges, including BETWEEN. The query leaves the source table’s active index and cursor in place, so a lookup can run while a user is browsing the same dataset.
On the 100,000-row test reported in the release history, a point lookup fell from 40–60 ms to 0.6 ms. A range count fell from 43–64 ms to 6–8 ms. Existing queries can benefit as soon as a suitable index is present. Date and time columns are currently outside the scope of this optimization.
Transactions you can control from SQL
BEGIN, COMMIT, ROLLBACK and named savepoints now work across ExecSQL calls on the same TkbmMemSQL instance. An import can establish a savepoint before processing a batch, roll back that batch if validation fails, and still commit the earlier work. Participating memtables must have versioning enabled with VersioningMode set to mtvmAllSinceCheckPoint.
For applications that need to retain changes between runs, the optional TkbmMemTableJournal writes committed changes in checksummed batches. When the table reopens, it replays complete batches. SyncMode controls when writes are flushed to disk.
More expressive SQL for updates and reports
Imports and synchronization routines gain INSERT ... ON CONFLICT DO UPDATE, UPDATE ... FROM, DELETE ... USING and MERGE. A RETURNING clause can expose both OLD and NEW values, so an application can report exactly what an update changed.
For reports, window functions now support ROWS, RANGE and GROUPS frames, named windows, and frame exclusions. You can use a window result directly in an expression such as amount - LAG(amount) OVER (ORDER BY entry_id) to show the change from the previous entry. Applications working with money or other fixed-scale quantities can also select standard decimal semantics for exact NUMERIC and DECIMAL arithmetic, comparisons and aggregates.
One change to check when upgrading is the default frame for ordered window aggregates. SUM(amount) OVER (ORDER BY entry_id) now produces a running total, including rows tied on the ordering value. Earlier versions returned the total for the whole partition. If a report needs the grand total on every row, use SUM(amount) OVER (). If it needs a total per account, use SUM(amount) OVER (PARTITION BY account_id).
kbmMemTable is the premier high performance, high functionality in memory dataset for Delphi, C++Builder and Lazarus/FreePascal with kbmMemTable Professional topping the scales as being the worlds fastest!
It is running on all IDE supported platforms including Win32/64, Linux64, OSX, IOS and Android.
If you have an up to date Service and Update (SAU) subscription, then you can immediately visit https://portal.components4developers.com to download the latest kbmMemTable release.
If not, please visit our shop at http://www.components4developers.com and extend your SAU with another 12 months.
Whats new in 8.25.00 Nov 20 2026
--------------------------------
- Added FULL TEXT INDEXES to kbmMemTable. An index that maps words to the records
containing them, rather than ordering references to records, and which is what
answers the CONTAINS predicate in kbmMemSQL.
AddFullTextIndex(Name,Fields[,Tokenizer][,Stemming][,BaseIndexName]) declares one,
DeleteFullTextIndex removes it, and the FullTextIndexes property exposes them.
It is built from whatever the table already holds and maintained from then on,
through insert, edit and delete.
- Index names are ONE namespace across ordinary and full text indexes. Declaring
either under a name the other already uses is refused, and DeleteIndex removes
whichever kind holds the name, so a caller never has to say which was meant.
- A full text index may be DERIVED FROM an ordinary index, which scopes it to the
records that index holds - so deriving from a filtered index gives a full text
index over the filtered rows only, including records moving in and out of scope
as they are edited. The reverse does not exist: an ordinary index derives from
another by narrowing its ordered reference list, and a full text index has no
such list. An ordinary index cannot be deleted while one is derived from it.
- Added dataset level searching: FullTextSearch(Query) or FullTextSearch(IndexName,
Query) returns how many records matched and remembers which, after which
FullTextFindFirst/FindNext/FindPrior/FindLast walk them in the current index
order, FullTextMatch tests the current record, FullTextCount reports the size,
and FullTextSearchTo copies the matches into another dataset the way GroupBy and
Distinct produce a derived one. A held result is discarded as soon as the data
changes, since it identifies records by address.
- Added SQL DDL for full text indexes:
CREATE FULLTEXT INDEX [IF NOT EXISTS] name ON table (fields)
[TOKENIZER UNICODE|ASCII|TRIGRAPH|NONE] [STEMMING NONE|PORTER] [FROM index]
DROP INDEX name ON table removes either kind.
The eight words this introduced remain usable as ordinary identifiers, so SQL
that parsed before the feature existed still parses.
- Query syntax for CONTAINS: word, "two adjacent words", prefix*, -word or NOT word,
a OR b, and a b meaning both.
- Tokenizers are UNICODE (letters and digits, as SQLite unicode61), ASCII (as
UNICODE but splitting on anything outside 7 bit) and TRIGRAPH (overlapping three
character windows, as SQLite trigram), with optional Porter stemming. These
mirror what a database full text index can be declared with, so the same CONTAINS
selects the same rows whether the data is held in memory or in a database.
- CONTAINS is answered from a declared index when one covers its fields, computing
the matching records once per query and reducing each row to a hash probe instead
of re-tokenizing it. Without an index it still answers correctly by scanning, and
both routes return identical rows.
NOTE: with an index declared, a bare CONTAINS searches the fields the index was
declared over rather than every text field - the declaration is the statement of
what is searchable, as an fts5 column list is in SQLite.
- Maintenance is O(1) amortized for insert, edit and delete, and a table without a
full text index pays a single pointer test per record change.
- Added TkbmCustomMemTable.CurrentRecordPointer, the record behind the current row as
opposed to the buffer it is delivered in.
- kbmMemSQL: ORDER BY and GROUP BY take any expression - a column, a column
number, a select list alias, or a computed value such as UPPER(name), a CASE,
or (in a grouped query) an aggregate. A select list item that is the same
expression as a GROUP BY item reads the grouped value. A constant
(ORDER BY 'x') is refused with a clear message.
- kbmMemSQL: ORDER BY ... NULLS FIRST / NULLS LAST per item. Without it NULL
is the highest value, as before, unless soOrderByNullFirst makes it the
lowest. FIRST, LAST and NULLS remain usable as identifiers.
- kbmMemSQL: new functions SUBSTRING/SUBSTR, CONCAT, FLOOR, CEILING/CEIL,
EXTRACT(part FROM d), DATEADD(part, n, d), DATEDIFF(part, a, b) (boundaries
crossed, as SQL Server), ROUND and TRUNC with a number of digits, and the
aggregate STRING_AGG/GROUP_CONCAT.
- Fixed: ORDER BY a column that is not selected, with a computed select list
item (SELECT UPPER(name) ... ORDER BY salary), repeated the last row's values
in every row.
- Fixed: in a query with HAVING, computed select list items (SUM(x)*2,
UPPER(dept)) read wrong values.
- Fixed: GROUP BY a column number naming a computed item gave wrong groups.
- Fixed: a select list item reading a column that is not grouped on was only
refused when it had an alias; now it always is, as SQL requires.
- Fixed: a CASE (or LIKE/IN/BETWEEN) around an aggregate in a grouped query was
refused ("Unexpected node class").
- Fixed: ROUND rounded negative values towards zero (ROUND(-2.7) gave -2).
- kbmMemSQL: INTERSECT ALL and EXCEPT ALL (bag semantics: min(m,n) and
max(m-n,0) copies of a row).
- Fixed: INTERSECT/EXCEPT whose result had a repeated row could come out
EMPTY: rows were deleted while the result was walked, and deleting the last
one left the cursor on the row before it, which was judged again.
- Fixed: INTERSECT/EXCEPT compared text case-insensitively ('south' matched
'South') and NULL as ''; they now compare as = does, with NULL equal to NULL.
- Fixed: aggregates without GROUP BY over no rows gave no row; now one row
(COUNT 0, the others NULL).
- Changed: the ORDER BY direction moved from TkbmSQLFieldNode to
TkbmSQLCustomNode (Descending, plus the new NullsOrder), since any expression
can now be ordered by. Code reading TkbmSQLFieldNode(x).Descending still
compiles.
- Added to TkbmSQLCustomNode: ChildCount/GetChild (an expression's children)
and SameExpression (structural equality).
- kbmMemSQL: a WHERE condition column = value, <, <=, >, >= or BETWEEN on a
column that one of the table's indexes starts with now seeks that index
instead of reading every row (100,000 rows: a point lookup 0.6 ms instead of
40-60 ms, a range count 6-8 ms instead of 43-64 ms). The table is left as
it was: its IndexName, cursor and row order do not change. Without ORDER BY
the rows may come in index order. kbmSQLUseIndexSeek:=false turns the seek
off. Date and time columns are not sought yet.
- kbmMemSQL: a scan of a memtable reads its records directly in the current
index's order instead of through First/Next (kbmSQLUseDirectScan).
- Added TkbmCustomMemTable.IndexSearch: searches an index for a key on one
field, giving the position of the first or last match, or the insertion
point.
- kbmMemSQL: window frames - ROWS or RANGE, BETWEEN UNBOUNDED PRECEDING /
n PRECEDING / CURRENT ROW / n FOLLOWING / UNBOUNDED FOLLOWING, for SUM,
COUNT, AVG, MIN, MAX and the new FIRST_VALUE, LAST_VALUE and NTH_VALUE.
A RANGE offset needs one numeric ORDER BY column and may be fractional.
- kbmMemSQL: LAG/LEAD take an offset and a default: LAG(x, 2, 0). Both may be
per-row expressions; a negative offset looks the other way.
- kbmMemSQL: a window function may be used inside a select list expression:
x * 100.0 / SUM(x) OVER (), x - LAG(x) OVER (ORDER BY d).
- Changed: SUM(x) OVER (ORDER BY y) (and the other aggregates) is the running
value, the SQL standard default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW, including the current row's peers). It was the total of the
whole partition. Without ORDER BY inside OVER nothing changes.
- Fixed: LAG/LEAD ignored their offset argument (LAG(x, 2) was LAG(x, 1)).
- Fixed: an unknown function with OVER (FOO() OVER (...)) ran as ROW_NUMBER;
it is refused. So are COUNT(DISTINCT x) OVER (...), which counted every
row, and a wrong number of window function arguments.
- Fixed: a window function inside an expression read 0, so
100*x/SUM(x) OVER () gave INF.
- Fixed: a window function over a text column (MIN(name) OVER (...),
LAG(name) OVER (...)) returned empty strings.
- kbmMemSQL: transactions in SQL - BEGIN [TRANSACTION|WORK] / START
TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT name, RELEASE [SAVEPOINT] name,
ROLLBACK TO [SAVEPOINT] name - on kbmMemTable's nested transactions. The
state lives in TkbmMemSQL (Transactions.Active/Depth), so a transaction may
span ExecSQL calls. The tables taking part are the open memtables known at
BEGIN that keep versions since the last checkpoint (EnableVersioning,
VersioningMode=mtvmAllSinceCheckPoint). Inside a transaction, a write to
any other table and any change of structure are refused, since neither
could be rolled back. Every BEGIN and SAVEPOINT is a level of its own, so a
nested BEGIN ... ROLLBACK undoes only its own work. Freeing the TkbmMemSQL
rolls back what it left open.
- Fixed: COUNT(*) over a join (inner, outer, comma) raised "Ambiguous
wildcard/RecNo/RecID table reference"; so did a window COUNT(*) over one.
- Fixed: SELECT * over a join raised the same error. It is now every
table's columns in FROM order; a name used by two tables is prefixed with
its table (a_id, b_id), as for any two selected columns of the same name.
- RECNO / ROWID without a table over several tables is still refused; the
message now says to name the table (t.RECNO).
- Fixed: a negated constant (SELECT -7, WHERE x > -15, ABS(-7)) was folded
into a constant of type boolean, so it read as True.
- Window frames were checked against SQLite with 1,967 differential queries
(ROWS and RANGE frames, value functions, LAG/LEAD, errors): no differences
apart from deliberate ones. RANGE with an offset over a text column is
refused, as PostgreSQL does. A NULL LAG/LEAD offset gives NULL, as
PostgreSQL does; SQLite gives the default.
- Added optional TkbmMemTableJournal persistence. It writes committed changes
as checksummed batches, replays complete batches on open and can checkpoint
the table by atomically replacing the journal. SyncMode controls when
changes are flushed to disk.
- Added LockMode=mtlmReadWrite, LockRead/UnlockRead and registration of a
shared reader/writer lock class. The default remains mtlmExclusive.
- Added hashed indexes (mtcoHashed) for eligible equality lookups, and reusable
typed TkbmIndexKey keys for searching an index without moving the cursor.
The ordinary index search still handles keys the hash cannot answer.
- Improved index sorting and position lookup for large tables. Row-order
indexes preserve insertion and move order across rebuilds and transaction
rollback. Persistent saves preserve row order and replace the file atomically.
- Added PreserveEmptyBlobs to distinguish zero-length BLOBs from NULL.
Binary stream format 600 now writes ftLargeint exactly as a 64 bit integer.
Set WriteVersion to 590 to write a stream that older versions can read.
- Added DateTimeAsDouble as an opt-in date/time storage representation. Set it
before opening the table. Native streams require matching representations.
- Added exact NUMERIC/DECIMAL handling through kbmSQLDecimal, including precision
and scale, arithmetic, casts, comparisons and aggregates when standard
decimal semantics are selected. SQLite-style NUM is accepted as NUMERIC.
- Added TkbmMemSQL.ParseSQLText for parsing separately from binding and
execution. SQL state is allocated on demand, and long operations can
report progress through TkbmSQLCustomOperation.OnProgress.
- Added INSERT ... ON CONFLICT DO NOTHING / DO UPDATE, INSERT VALUES with
omitted trailing columns, UPDATE ... FROM, DELETE ... USING and MERGE,
including WITH-prefixed MERGE. RETURNING can expose OLD and NEW values.
- Expanded PostgreSQL-compatible SQL with FETCH FIRST ... WITH TIES, BETWEEN
SYMMETRIC, ILIKE and the ~~ operator family, four-part names, quoted function
names, non-ASCII identifiers and WHERE/GROUP BY/HAVING without FROM.
- Improved standard SQL evaluation with common types for VALUES and UNION,
NULL-aware constant folding, lazy BETWEEN, integer overflow checking and
evaluation of volatile select expressions after ORDER BY and LIMIT where
applicable. Added options for Unicode code-point string functions and
collation, character widths, CHAR padding and binary BLOB values.
- Fixed CASE used as an operand, and fixed parser error recovery for unknown
functions and missing nodes. Double-quoted strings in evaluated expressions
retain their old meaning unless standard quoted names are enabled.
- Added RegenerateParser.ps1 and kept the Coco/R grammar and generated parser
in sync, including joined DML and MERGE.
- Updated CompileTool package generation for edition defines, RAD Studio 13
and generation for every supported IDE. Added package entries for full text
search, the journal and decimal support.
- Fixed Delphi and FPC compatibility when copying SQL result fields and
converting exact decimal values to TBcd. Updated Lazarus/FPC registration
and build support.
- Added GROUPS window frames, EXCLUDE frame clauses, named windows and
aggregate FILTER (WHERE ...). Improved NATURAL JOIN and JOIN ... USING
column resolution and support for parenthesized join groups.
- AttachedAutoRefresh now schedules a refresh on the attached table's own
thread when another thread posts or deletes a record.
- Added strict full text query parsing and AddDocumentPostings for adapters
that supply pre-tokenized terms and their positions.
- Fixed: an unaliased column of a join lost its plain name when another selected
column with the same source name had an alias (SELECT a.id, b.id AS bid FROM a
JOIN b ...), so an outer query could not read it as t.id.
- Fixed: a bare ORDER BY name did not match the select list's single unaliased
qualified column of that name (SELECT a.id FROM a JOIN b ... ORDER BY id).
- The installers now ship kbmMemTableJournal and kbmMemAggregate.inc.
Whats new in 8.10.00 Jul 21 2026
--------------------------------
- Added support for OUTER JOINs in kbmMemSQL: LEFT, RIGHT and FULL [OUTER] JOIN ... ON,
emitting NULL extended rows where a side has no match.
- Added support for multi table join chains. Any number of LEFT/INNER/CROSS joins
chain left to right (A LEFT JOIN B ... LEFT JOIN C ...), and chains that also
contain RIGHT/FULL joins are supported.
- Added support for JOIN ... USING (col,...) as shorthand for the equivalent
JOIN ... ON left.col=right.col AND ...
- Added support for self joins (the same table referenced under two aliases).
- Added support for derived tables. A parenthesized sub SELECT used as a source in
the FROM clause, aliased. Eg. SELECT d.x FROM (SELECT x FROM t) AS d.
- Added support for correlated sub selects.
- Added support for EXISTS and NOT EXISTS (sub query existence test).
- Added support for quantified comparisons ANY, ALL and SOME.
Eg. WHERE salary > ALL (SELECT salary FROM ...).
- Added support for CAST(expr AS type).
- Added support for set operations INTERSECT and EXCEPT, and made UNION (without ALL)
correctly remove duplicates across the whole set operation chain.
- Added support for window functions with an OVER clause:
ROW_NUMBER(), RANK(), DENSE_RANK(), the aggregate windows SUM/COUNT/AVG/MIN/MAX
OVER (PARTITION BY ...), and LAG(x)/LEAD(x).
Syntax: <fn>(...) OVER (PARTITION BY cols [ORDER BY cols [DESC]]).
- Added support for Common Table Expressions (CTE): WITH name AS ( SELECT ... ) [, ...]
preceding the main query, referenced in its FROM like an ordinary table.
- Added support for recursive CTEs (WITH [RECURSIVE] name AS ( anchor UNION[ALL]
recursive )). A self referencing CTE is evaluated by fixpoint iteration until no
new rows are produced. UNION de-duplicates; UNION ALL relies on acyclic data or
an internal safety cap. Typically used for hierarchy/tree and graph traversal.
A CTE whose body is a set operation now materialises all its members.
- Fixed COUNT(field) to skip NULL values instead of counting every row like COUNT(*).
- Fixed COUNT(DISTINCT field) combined with GROUP BY to count distinct values per
group instead of globally.
- Fixed GROUP BY on a column that is aliased in the SELECT list.
- Added support for ORDER BY on a referenced column that is not part of the SELECT list.
- Optimized inner/cross join execution with per join level predicate pruning. Conjuncts
of the combined ON/WHERE condition that reference only tables already entered are
tested as each table level is entered, abandoning non matching partial row
combinations before descending into deeper tables rather than after building the
full Cartesian product. The result set is identical - it is a pure short circuit.
- Optimized equi-join conditions with a hash join plan. An equality between two joined
tables builds a hash index over one side and probes it per row, replacing the linear
scan for that join level. Falls back to the scan when no hash spec applies.
- Added a streaming external dataset adapter. RegisterAPI associates a TDataset class
with a kbmSQL dataset API, so any registered TDataset can participate as a table in
a kbmMemSQL join directly, without first being copied into a kbmMemTable. Mixed
joins between a kbmMemTable and an external dataset are supported.
- Added the TriggerUpdated flag on kbmSQL field nodes to mark a column as maintained
by a database trigger. Database dialect rewriters consult the flag when deciding
per column NOT NULL emission - e.g. SQLite omits its defensive force NOT NULL on
a primary key column when the flag is set, because the AFTER INSERT trigger needs
the column nullable at the SQL layer in order to run. Other databases typically
ignore the flag as their trigger patterns work fine alongside NOT NULL.
![]()







