CacheDB Temporary Tables and Metadata

May 15, 2025 by Igor Lozhkin

Arnica NanoQuery-based CacheDB uses SQLite embedded database behind the scenes. SQLServer developers starting programming with CacheDB might run into unexpected nuances. For example, while CacheDB supports temporary tables, it will manifest its schema differently, depending on how a temporary table is created.

For instance, in SQLServer a temporary table can be created declaratively or implicitly:

CREATE TABLE #temp1 (
	FirstName varchar(50),
	LastName varchar(50)
)

CREATE TABLE persons (
	FirstName varchar(50),
	LastName varchar(50)
)

SELECT 
	FirstName,
	LastName
INTO
	#temp1
FROM
	persons

In this example temporary tables #temp1 and #temp2 will manifest identical schema when their schemas are queried.

This is not the case with CacheDB, because SQLite uses fewer base types, which can then then be assigned type affinities for mostly informational (rather than computational) purposes.

Let's consider a similar example with CacheDB:

CREATE TEMPORARY TABLE temp1 (
	FirstName varchar(50),
	LastName varchar(50)
);

CREATE TABLE persons (
	FirstName varchar(50),
	LastName varchar(50)
);

CREATE TEMPORARY TABLE temp2 AS
SELECT 
	FirstName,
	LastName
FROM
	persons;

In this example, temporary table temp1 will manifest the same varchar(50) datatypes for its columns when schema is queried (the type affinities will stay intact), while temporary table temp2 will manifest base datatype TEST when its schema is queried (type affinities are lost).

Knowing these nuances can help avoid unexpected problems when interpreting data structures in CacheDB query results.

Our Contacts

Arnica Software Corporation
The Exchange Tower
130 King Street West
Suite 1900
Toronto, ON M5X 1E3
Canada