- Unterstützt Speicher-Optionen
- Nur ohne expliziten Datentyp:
col_nameintegergenerated always as … - Nur ohne die Schlüsselwörter
generated always - Eingeschränkte Unterstützung von Constraints
- Erfordert einen expliziten Datentyp:
col_name integer generated always as … - Erfordert die Speicher-Option
stored - Ignoriert den Datentyp, falls angegeben
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!).
Constraints
- Nur mit der Speicheroption
stored - Nur mit der Speicheroption
persisted - Nur ohne expliziten Datentyp:
spalten_nameintegergenerated always as … - Benötigt einen expliziten Datentyp:
spalten_name integer generated always as … - Nur als Tabellen-Constraint – nicht in der Spaltendefinition
- Fremdschlüssel in der Spaltendefinition werden gelesen aber ignoriert • Nur mit der Speicheroption
stored - 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
- 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.
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.
- 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.
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.
Ähnliche Funktionen
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 ininsert,updateandmergeübersteuert werden
Normative Referenzen
Generierte Spalten sind in ISO/IEC 9075:2016-2 als optionale Funktion T175 definiert.
