Geht das… generated always as (…) (generierte Spalten)


Teilweise unterstützt von H2 ab 2.1.214. Teilweise unterstützt von SQLite ab 3.40.0. Teilweise unterstützt von PostgreSQL ab 15. Teilweise unterstützt von Apache Derby ab 10.16.1.1. Teilweise unterstützt von MySQL ab 8.0.31. Teilweise unterstützt von MariaDB ab 10.10. Teilweise unterstützt von SQL Server ab 2022. Vollständig unterstützt von Db2 (LUW) ab 9.7. Vollständig unterstützt von Oracle DB ab 11gR1.Apache DerbyBigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite200520072009201120132015201720192021⚠ 3.31.0 - 3.40.0adg⊘ 3.5.7 - 3.30.0⚠ 2008R2 - 2022bcd⚠ 12 - 15ef⊘ 8.4 - 11✓ 11gR1+a⚠ 5.7 - 8.0.31ade⊘ 5.0 - 5.6⚠ 5.2 - 10.10ade⊘ 5.1⚠ 2.0.202 - 2.1.214c⚠ 1.4.191 - 1.4.200ce✓ 9.7+⊘ 2.0⚠ 10.15.1.3 - 10.16.1.1d
  1. Unterstützt Speicher-Optionen
  2. Nur ohne expliziten Datentyp: col_name integer generated always as …
  3. Nur ohne die Schlüsselwörter generated always
  4. Eingeschränkte Unterstützung von Constraints
  5. Erfordert einen expliziten Datentyp: col_name integer generated always as …
  6. Erfordert die Speicher-Option stored
  7. Ignoriert den Datentyp, falls angegeben

SQL kennt generierte Spalten, auch bekannt als berechnete Spalten oder virtuelle Spalten, deren Wert automatisch aus anderen Werten derselben Zeile ermittelt wird.0

Das folgende Beispiel zeigt eine Tabelle mit zwei Basis-Spalten (d.h. nicht generierte Spalten): net_preis und ust_satz. Weiters wird die generierte Spalte ust_betrag definiert, die den Steuerbetrag berechnet.

CREATE TABLE … (
 netto_preis  NUMERIC(15,2),
 ust_satz     NUMERIC( 5,2),
 ust_betrag   NUMERIC(15,2) GENERATED ALWAYS AS
                            (netto_preis * ust_satz / 100.0)
)

Beachte, dass der SQL-Standard die Klammern um den Generierungsausdruck zwingend vorschreibt und nur deterministische Ausdrücke erlaubt (Ausnahmen).

Eine Tabelle kann beliebig viele generierte Spalten haben, diese können sich – in Standard-SQL – jedoch gegenseitig nicht sehen1. Manche Produkte erlauben es dennoch, dass sich der Generierungsausdruck einer Spalte auf eine andere generierte Spalte bezieht, solange sich dadurch keine zyklische Abhängigkeit ergibt (siehe Die Verkettung generierter Spalten). In solchen Systemen kann man dieser Tabelle auch die folgende Spalte hinzufügen.

ALTER TABLE …
 brutto_preis GENERATED ALWAYS AS (netto_preis + vat_amount)

Beachte, dass dieses Beispiel den Datentypen weglässt, sodass die Spalte den Typ des Ergebnisses des Generierungsausdruck es annimmt. In diesem konkreten Beispiel könnte das ausreichend sein, weil die notwendige Genauigkeit und die Anzahl der Dezimalstellen vom Ausdruck abgeleitet werden können. Für die Spalte ust_betrag von oben ist das allerdings nicht der Fall: die Division könnte zu mehr Nachkommastellen führen, als in der Währung darstellbar ist.2

Die bisher gezeigte Funktionalität ist natürlich auch mit Views abbildbar. Bei Views muss man sich auch keine Gedanken über Einschränkungen bezüglich der Verkettung von Ausdrücken oder dem Determinismus machen. Was die generierten Spalten jedoch auszeichnet ist, dass sie Tabellenspalten sind und damit überall verwendet werden können, wo Tabellenspalten erlaubt sind. Das ist allen voran in Constraints und Indizes (siehe Use The Index, Luke!).

Constraints

Apache DerbydBigQueryDb2 (LUW)H2MariaDBegMySQLaffOracle DBPostgreSQLSQL ServerbSQLiteccgPrimärschlüsselUniqueFremdschlüssel zu and. Tab.Fremdschlüssel von and. Tab.
  1. Nur mit der Speicheroption stored
  2. Nur mit der Speicheroption persisted
  3. Nur ohne expliziten Datentyp: spalten_name integer generated always as …
  4. Benötigt einen expliziten Datentyp: spalten_name integer generated always as …
  5. Nur als Tabellen-Constraint – nicht in der Spaltendefinition
  6. Fremdschlüssel in der Spaltendefinition werden gelesen aber ignoriert • Nur mit der Speicheroption stored
  7. Mit einem Unique-Constraint auf der generierten Spalte

Speicher-Optionen: virtuell oder physisch

In der Praxis kann die oben gezeigte Syntax dazu führen, dass die generierten Werte, wie bei Basispalten, physisch in der Tabelle abgelegt werden – oder aber auch nicht.3 Das DBMS könnte sich den Platz auch sparen und den Wert erst ermitteln, wenn er abgefragt wird. Da der Generierungsausdruck grundsätzlich deterministisch sein muss, ist es egal wann das Ergebnis ermittelt wird.

Da beide Varianten – physische oder virtuelle generierte Spalten – ihre Vor- und Nachteile haben, ist es durchaus üblich, dass ein System beide Varianten unterstützt und eine Syntax-Erweiterung zur Auswahl anbietet. Da diese Syntax nocht vom SQL-Standard abgedeckt ist, unterscheiden sich die Schlüsselwörte jedoch zwischen den Dialekten.

… GENERATED ALWAYS AS (…) [ persisted | persistent | stored
                          | virtual ]

Beachte

Die folgende Übersicht stellt lediglich da, ob die Syntax akzeptiert wird. Die darunterliegenden Tests prüfen nicht, ob die generierten Spalten gespeichert werden oder nicht

Apache DerbyBigQueryDb2 (LUW)aaaaH2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite… as (…)           … as (…) persisted … as (…) persistent… as (…) stored    … as (…) virtual   
  1. Wird in einigen Fällen akzeptiert, vermutlich aber eine Macke des Parsers – mit Vorsicht verwenden!

Die Verkettung generierter Spalten

In Standard-SQL darf ein Generierungsausdruck keine Referenz auf eine generierte Spalte haben. Da es jedoch keinen zwingenden Grund gibt das generell zu verbieten, ist es in einigen Systemen erlaubt, solange sich die Kette nicht zu einem Kreis schließt.

Apache DerbyBigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite… as (… gen_col …)

Gültige Ausdrücke

Eine weitere, nicht immer erzwungene Einschränkung des SQL-Standards ist, dass Generierungsausdrücke deterministisch sein müssen. Das heißt, dass Funktionen wie current_timestamp oder noch deutlicher random() nicht erlaubt sind. Natürlich führt die Verwendung von nicht-deterministischer Funktionen auch zu nicht-deterministischen Ergebnissen. Bei generierten Spalten kann es jedoch zu unvorhersehbaren Ergebnissen kommen, wenn man nicht genau weiß, wie das System damit umgeht.

Apache DerbyBigQueryDb2 (LUW)H2MariaDBaMySQLOracle DBPostgreSQLSQL ServeraSQLitedeterministischnicht-deterministisch
  1. Nur wenn sie weder direkt noch indirekt (über einen Constraint oder Index) persistiert wird

Syntax-Varianten

Zu guter Letzt gibt es noch einige Syntax-Varianten, die das Verhalten der generierten Spalten nicht ändern aber die Portabilität beschränken. Daher in aller Deutlichkeit: in Standard-SQL sind die Klammern um den Generierungsausdruck sowie die Schlüsselwörter generated always zwingend vorgeschrieben.

Apache DerbyBigQueryDb2 (LUW)H2MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLite… as (<expr>)… as  <expr> … generated always as ……                  as …

In generierte Spalten schreiben

Die Grundidee hinter generierten Spalten ist, dass ihr Wert aus anderen Daten abgeleitet wird. Daher ist es nicht erlaubt, den Wert einer generierten Spalte in einer Insert-, Update- oder Merge-Anweisung zu setzen. Dazu lässt man die generierten Spalten bei diesen Anweisungen einfach weg oder benutzt das Schlüsselwort default als Ausdruck für den Spaltenwert.

  • Versteckte und unsichtbare Spalten

  • generated [always|by default] as identity (Identity-Spalten)

  • generated always as row [start|end] (System-versionierte Tabellen)

  • default (Standardwert für Spalten): Ähnlich, kann aber in insert, update and merge übersteuert werden

  • Funktions-basierte Indizierung

Normative Referenzen

Generierte Spalten sind in ISO/IEC 9075:2016-2 als optionale Funktion T175 definiert.

Über den Autor

Foto von Markus Winand

Markus Winand ist der SQL Renaissance Botschafter auf der Mission, Entwickler auf die Evolution von SQL im 21. Jahrhundert aufmerksam zu machen. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Fußnoten

  1. Or constants, of course.

  2. SQL-2:2016, §11.4 SR 10 c

  3. Dieses Beispiel vernachlässigt einige Anforderungen der Buchhaltung.

  4. Der SQL-Standard beschreibt die Funktionalität von generierten Spalten so, dass die Werte zum Zeitpunkt der Datenänderung ermittelt werden. D.h. währen der Insert-, Update- oder Merge-Anweisung. Streng betrachtet bedeutet das, dass der Standard verlangt, dass die Werte gespeichert werden (da sie „SQL-data“ werden). Der Artikel „SQL:2003 Has Been Published“ beschreibt die Funktionalität auch so.

    Bei den verschiedenen Umsetzungen ist es aber keineswegs so, dass generierte Spalten immer gespeichert werden. Ich persönlich glaube auch, dass dieses Verhalten noch immer im Sinne des Begriffs „effectivley“ ist, wie ihn der SQL-Standard nutzt (Teil 1, „Rule evaluation order“) obwohl die Formulierung diesen Fall nicht abdeckt.

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„modern SQL“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz