SQLite Generated Columns: VIRTUAL vs STORED erklärt

Eine generierte Spalte ist eine berechnete Spalte

Eine generierte Spalte (auf Englisch generated column) ist eine Spalte, deren Wert nicht beim INSERT geliefert wird, sondern aus einem Ausdruck berechnet wird. Du legst die Formel einmal im CREATE TABLE fest, den Rest übernimmt SQLite. Schreiben kannst du in so eine Spalte nicht — der Versuch endet mit einem Fehler.

Hier das kürzestmögliche Beispiel:

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total wurde nie eingefügt, taucht aber trotzdem in jeder Zeile auf. SQLite berechnet den Wert bei jedem Lesezugriff neu aus price + tax. Sobald du eine der beiden Spalten änderst, zieht total automatisch nach.

Das Schlüsselwort GENERATED ALWAYS AS ist Pflicht. Das ALWAYS ist eine Formalie aus dem SQL-Standard – in SQLite gibt es ohnehin keine andere Variante.

VIRTUAL vs. STORED in SQLite

Jede generierte Spalte hat eine von zwei Ausprägungen. Standard ist VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- bei jedem SELECT neu berechnet
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- einmal berechnet und auf der Festplatte gespeichert
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

Das mentale Modell:

• VIRTUAL — belegt null Bytes auf der Festplatte, kostet bei jedem Lesezugriff CPU. Günstig hinzuzufügen, günstig später zu ändern.
• STORED — belegt Speicherplatz, verursacht beim Lesen keinen Mehraufwand. Lohnt sich, wenn der Ausdruck teuer ist oder die Spalte deutlich öfter gelesen als geschrieben wird.

Lässt du das Schlüsselwort weg, bekommst du VIRTUAL. Und das ist in den allermeisten Fällen genau die richtige Wahl.

Wozu der Aufwand? Indizierbare abgeleitete Werte

Das Killer-Feature: Du kannst einen Index auf eine generierte Spalte legen. Damit bekommst du schnelle Lookups auf abgeleitete Werte, ohne all deine Queries umschreiben zu müssen.

Angenommen, du willst E-Mails ohne Beachtung der Groß-/Kleinschreibung suchen:

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

Der Index deckt die kleingeschriebene Form ab. Eine Abfrage, die auf email_lower filtert, nutzt den Index direkt. SQLite kennt zwar auch Expression Indexes (CREATE INDEX ... ON users(lower(email))), aber mit einer generierten Spalte wird der abgeleitete Wert zu einer echten Spalte: Du kannst sie per SELECT abfragen, in Views referenzieren und aus dem Anwendungscode heraus wiederverwenden.

Werte aus JSON extrahieren mit dem SQLite ->> Operator

Generated Columns spielen ihre Stärken vor allem bei JSON aus. Die JSON-Unterstützung von SQLite stellt den ->>-Operator bereit, um einen Skalarwert herauszuziehen – packst du das in eine generierte Spalte, hast du ein typisiertes, indizierbares Feld über einem flexiblen Blob.

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

user_id und kind sehen für deine Queries aus wie ganz normale Spalten, aber die eigentlichen Daten liegen in payload. Sobald sich das JSON ändert, ziehen die Spalten automatisch nach. Der Index auf user_id sorgt dafür, dass das Lookup richtig schnell ist.

Regeln und Einschränkungen

Ein paar Dinge erzwingt SQLite knallhart – die solltest du kennen, bevor du darüber stolperst:

• Der Ausdruck muss deterministisch sein. random(), datetime('now') und andere nicht-deterministische Funktionen sind tabu. Der Wert muss sich aus der Zeile reproduzierbar berechnen lassen.
• Der Ausdruck darf nur auf Spalten derselben Zeile zugreifen. Keine Subqueries, keine Aggregatfunktionen, keine anderen Tabellen.
• Du kannst eine generierte Spalte nicht direkt per INSERT oder UPDATE befüllen. INSERT INTO products (total) VALUES (5) wirft einen Fehler.
• STORED-Spalten lassen sich nicht per ALTER TABLE ... ADD COLUMN hinzufügen. Nachträglich geht nur VIRTUAL.
• Generierte Spalten können NOT NULL-, CHECK-, UNIQUE- und sogar FOREIGN KEY-Constraints haben. In dieser Hinsicht verhalten sie sich wie jede andere Spalte auch.

Eine kurze Demo zur Schreib-Regel:

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

Die Lösung: Lass die generierte Spalte einfach aus der INSERT-Liste weg – SQLite kümmert sich selbst um die Berechnung.

VIRTUAL vs STORED in SQLite – was passt wann?

Welche Variante die richtige ist, hängt vor allem vom Verhältnis zwischen Lese- und Schreibzugriffen sowie von den Kosten des Ausdrucks ab:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Günstige Arithmetik, kann bei jedem Lesen neu berechnet werden.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Aufwendigerer Ausdruck, häufig abgefragt, selten geschrieben → speichern.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

Faustregeln:

• Im Zweifelsfall VIRTUAL. Beim Schreiben kostet das nichts und reicht in fast allen Fällen.
• Auf STORED umsteigen, wenn du die Spalte in einer schreibintensiven Tabelle indexieren willst (der Index muss den Wert sowieso persistieren) oder wenn der Ausdruck wirklich teuer ist.
• Nicht zu lange grübeln. Die Variante gehört zwar zum Schema, aber du kannst die Spalte später droppen und neu anlegen, wenn du es dir anders überlegst — zumindest bei VIRTUAL.

Generierte Spalten vs. Views

Die Überschneidung mit Views ist offensichtlich: beide stellen berechnete Werte bereit, ohne sie zu speichern (na ja, manchmal). Die Trennlinie verläuft meistens so:

• Eine generierte Spalte gehört zu einer Zeile in einer Tabelle. Nimm sie für Ableitungen pro Zeile — eine E-Mail formatieren, ein JSON-Feld extrahieren, eine Summe berechnen.
• Ein View ist eine gespeicherte Abfrage. Nimm ihn, wenn Joins, Aggregationen oder zeilenübergreifende Filter im Spiel sind.

Du kannst beides kombinieren. Ein View kann aus einer Tabelle mit generierten Spalten SELECTen und zusätzlichen Kontext per Join dazuholen. Generierte Spalten leben auf der Speicherebene, Views auf der Abfrageebene.

Als Nächstes: ATTACH DATABASE

Mit generierten Spalten berechnet eine Tabelle ihre eigenen Werte. Die nächste Seite geht in die andere Richtung: mehrere SQLite-Datenbanken gleichzeitig per ATTACH DATABASE einbinden, sodass eine einzelne Abfrage über mehrere Dateien hinweg laufen kann.