col_name integer generated always as …
generated always
col_name integer generated always as …
stored
Generierte Spalten, auch bekannt als berechnete Spalten oder virtuelle Spalten, sind Spalten deren Wert automatisch aus anderen Werten derselben Zeile ermittelt wird.0
Das folgende Beispiel zeigt eine Tabelle mit zwei Basis-Spalten – das sind 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.✓✗
Eine Tabelle kann beliebig viele generierte Spalten haben, diese können sich – in Standard-SQL – jedoch gegenseitig nicht sehen1. Manche Systeme erlauben es dennoch, dass sich der Generierungsausdruck einer Spalte auf eine andere generierte Spalte bezieht, solange sich dadurch keine zyklische Abhängigkeit ergibt.✓✗ In solchen Systemen kann man dieser Tabelle auch die folgende Spalte hinzufügen.
ALTER TABLE …
brutto_preis GENERATED ALWAYS AS (netto_preis + ust_betrag )
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!).
stored
persisted
spalten_name integer generated always as …
spalten_name integer generated always as …
stored
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 ]
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
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.
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.
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.
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
Generierte Spalten sind in ISO/IEC 9075-2:2023 als optionale Funktion T175 definiert.
20 Jahre SQL-Evolution kann man nicht an einem Tag nachholen. Abonniere den Newsletter via E-Mail, Twitter oder RSS, um sukzessive aufzuholen und modern-sql.com am Radar zu behalten.
Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how
Oder aus Konstanten.
ISO/IEC 9075-2:2023 §11.4 SR 10c
Dieses Beispiel vernachlässigt einige Anforderungen der Buchhaltung.
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.