Einführung
Berechnete Spalten sind einfach virtuelle Spalten, deren Inhalt das Ergebnis eines Ausdrucks ist. Normalerweise werden sie verwendet, um Daten zu speichern, die auf den übrigen Spalten der Tabelle basieren. Der Ausdruck kann andere nicht berechnete Spalten der Tabelle, Konstanten, Operatoren und Funktionen enthalten, aber Sie können keine Abfrage als Ausdruck für eine berechnete Spalte angeben.
Da es sich um „virtuelle“ Spalten handelt, werden sie nicht wie der Rest der Tabelle auf der Festplatte gespeichert. Tatsächlich werden sie nicht gespeichert, sondern jedes Mal berechnet, wenn in einer Abfrage auf die Spalte zugegriffen wird. Wie Sie sehen werden, können Sie SQL Server zwingen, die Spalte mit einigen Einschränkungen in der Tabelle zu speichern („persistieren“).
Der beste Weg, um zu verstehen, wie berechnete Spalten funktionieren, ist die Verwendung von Beispielen. Am Ende des Artikels finden Sie eine Datei mit allen Skripten, die in diesem Artikel verwendet werden, und wir werden Ihnen einige davon im Text zeigen, um die Erklärungen zu veranschaulichen. Zu Beginn erstellen wir zwei Tabellen: die erste enthält die Rechnungsinformationen, die zweite die Detailzeilen dieser Rechnungen. In der Skriptdatei finden Sie auch einige Einfügungen, um Beispieldaten zu erstellen.
CREATE TABLE invoices( id_invoice INT PRIMARY KEY IDENTITY , customer_name VARCHAR(25));CREATE TABLE detail_lines( id_detail INT PRIMARY KEY IDENTITY , id_invoice_detail INT , product VARCHAR(30) , unit_price MONEY , quantity INT , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));
Eine berechnete Spalte wird auf dieselbe Weise erstellt wie andere Spalten in einer Tabelle, nämlich mit einer CREATE TABLE- oder ALTER TABLE-Anweisung. Bei einer berechneten Spalte wird der Datentyp der Spalte durch den Ausdruck ersetzt, der verwendet wird, um den Inhalt der Spalte zu ermitteln. Die Syntax ist der Spaltenname, gefolgt von dem Schlüsselwort „as“ und dann dem Ausdruck. Erstellen wir eine berechnete Spalte in der Tabelle detail_line, um den Gesamtbetrag der Zeile zu speichern, den wir durch Multiplikation von Einheitspreis und Menge berechnen.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Prüfen, ob es sich um eine berechnete Spalte handelt
Es gibt mehrere Möglichkeiten, um festzustellen, ob eine Spalte wirklich eine berechnete Spalte ist. Eine davon ist die Verwendung der Funktion columnproperty() unter Angabe der Eigenschaft „IsComputed“.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Eine andere Möglichkeit, Informationen über berechnete Spalten zu erhalten, ist die Systemansicht sys.computed_columns. Diese Ansicht ist eine Erweiterung der Ansicht sys.columns. Das bedeutet, dass sys.computed_columns alle Spalten aus der Ansicht sys.columns erbt und darüber hinaus weitere Spalten hinzufügt, die spezifisch für diese Art von Spalten sind. Im Laufe des Artikels werden wir einige der Spalten in dieser Ansicht sehen, da wir verschiedene Eigenschaften der berechneten Spalten sehen. Für den Moment genügt es zu wissen, dass diese Ansicht nur berechnete Spalten anzeigt und eine Spalte namens is_computed hat, die angibt, ob die Spalte berechnet ist oder nicht. Natürlich haben alle Datensätze dieser Ansicht eine Eins in dieser Spalte.
SELECT name, is_computed FROM sys.computed_columns;
Da der Inhalt der Spalte jedes Mal berechnet wird, wenn in einer Abfrage auf diese Spalte verwiesen wird, wird der Inhalt immer aktualisiert. Jede Änderung in den Spalten, die in dem Ausdruck enthalten sind, spiegelt sich automatisch in dem Wert der Spalte wider. Wir können dies sehen, indem wir die Menge in einem Register in der Beispieltabelle detail_lines ändern und das Ergebnis überprüfen.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Ein Schritt nach vorn
Was wir bisher mit berechneten Spalten gesehen haben, ist sehr einfach, da es nur darum geht, Berechnungen mit anderen Spalten durchzuführen. Der Ausdruck der berechneten Spalten kann jedoch Funktionen enthalten, sowohl T-SQL-Standardfunktionen als auch benutzerdefinierte Funktionen (UDF). Auf diese Weise ist es möglich, die Funktionalität dieser Spalten viel weiter zu erweitern.
Sehen wir uns ein Beispiel dafür an. Wir werden der Tabelle Rechnungen eine berechnete Spalte hinzufügen, die den Gesamtbetrag der Rechnung berechnet. Dazu müssen wir die Rechnungsnummer ermitteln und die Tabelle detail_lines abfragen, um den Gesamtbetrag aus jedem Datensatz mit dieser Rechnungs-ID zu summieren. Das geht am besten mit einer Funktion, die die Rechnungsnummer als Parameter erhält und die Summe zurückgibt. Danach müssen wir die Spalte erstellen, die diese Funktion verwendet.
CREATE FUNCTION fn_total_invoice (@invoice_number INT)RETURNS MONEYASBEGIN DECLARE @total MONEY SELECT @total=SUM(total_amount) FROM detail_lines WHERE id_invoice_detail = @invoice_number RETURN @totalENDALTER TABLE invoices ADD total_invoice AS dbo.fn_total_invoice(id_invoice)
Wir können überprüfen, ob diese Spalte korrekt funktioniert, indem wir einen neuen Datensatz in der Tabelle detail_lines hinzufügen, so dass sich die Gesamtrechnung ändern sollte.
INSERT INTO detail_lines (id_invoice_detail,product,unit_price, quantity) VALUES (2,'Cup',9.90,1)SELECT id_invoice, customer_name, total_invoice FROM invoices WHERE id_invoice=2
Ändern der Spalte
Es kann Situationen geben, in denen Sie eine berechnete Spalte ändern müssen. Leider ist dies nicht möglich. Um diese Änderung vorzunehmen, muss die Spalte gelöscht und mit dem neuen Ausdruck neu erstellt werden.
Wenn die berechnete Spalte eine externe Funktion verwendet, ist es nicht möglich, diese Funktion zu ändern. Wenn wir es versuchen, erhalten wir eine Fehlermeldung, die besagt, dass diese Funktion mit der Tabelle verknüpft ist. Um die Funktion zu ändern, muss die Spalte gelöscht, die Funktion geändert und schließlich die Spalte mit der neuen Version der Funktion neu erstellt werden.
Die Definition der Spalte finden Sie in der Spalte „definition“ der Ansicht sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Speichern einer berechneten Spalte
Wie bereits erwähnt, sind diese Spalten „virtuell“, d.h. sie werden nicht physisch in der Tabelle gespeichert. Es besteht jedoch die Möglichkeit, die physische Speicherung der Berechnung in der Tabelle zu erzwingen, was als „Persistieren“ der Spalte bezeichnet wird. Dies kann die Leistung von SELECT-Anweisungen verbessern, da die Berechnung der Spalte nicht jedes Mal durchgeführt werden muss, wenn auf sie verwiesen wird.
Um die Spalte zu persistieren, muss der Ausdruck, mit dem die Spalte erstellt wird, zudem „deterministisch“ sein. Wie wir auf der Microsoft-Website sehen können, „liefern deterministische Funktionen immer das gleiche Ergebnis, wenn sie mit einem bestimmten Satz von Eingabewerten und bei gleichem Zustand der Datenbank aufgerufen werden.“ (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Wenn wir wissen wollen, ob SQL Server den Ausdruck einer berechneten Spalte als deterministisch betrachtet oder nicht, können wir die Funktion columnproperty() mit der Eigenschaft „IsDeterministic“ verwenden.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Wenn die Definition der Spalte eine benutzerdefinierte Funktion ist, können Sie auch überprüfen, ob diese Funktion selbst deterministisch ist oder nicht. Dazu müssen Sie die Funktion objectproperty() mit der Eigenschaft IsDeterministic verwenden.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Wie Sie aus den Abfragen ersehen können, wird die Spalte des ersten Beispiels, in der wir den Gesamtpreis des Details berechnen, als deterministisch betrachtet. Die Funktion, die den Gesamtpreis der Rechnung berechnet, gilt jedoch als nicht-deterministisch. Auf diese Weise kann nur die Spalte total_price der Tabelle detail_table in der Tabelle gespeichert werden.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Wiederum in der Ansicht sys.computed_columns können Sie das Feld is_persisted sehen, das angibt, ob die Spalte in der Tabelle persistiert oder nicht.
Indizes mit berechneten Spalten
Es ist möglich, berechnete Spalten in Indizes zu verwenden, obwohl sie mehrere Anforderungen erfüllen müssen:
- Eigentümerschaft: Alle Funktionen, die in der Definition der berechneten Spalte verwendet werden, müssen demselben Benutzer gehören wie die Tabelle.
- Determinismus: Die berechnete Spalte muss deterministisch sein. Wenn die Spalte CLR-Ausdrücke enthält, muss sie nicht nur deterministisch, sondern auch persistent sein.
- Accuracy: Der Ausdruck der berechneten Spalte muss genau sein. Das bedeutet, dass er nicht vom Datentyp „float“ oder „real“ sein kann. Sie können diesen Datentyp auch nicht in Ihrer Definition verwenden. Diese Eigenschaft kann mit der Funktion columnproperty() überprüft werden, indem die Eigenschaft IsPrecise angegeben wird.
- Datentyp: Die berechnete Spalte kann nicht von den Typen text, ntext oder image sein. Auch wenn der Ausdruck die Datentypen image, ntext, text, varchar (max), nvarchar (max), varbinary (max) oder xml enthält, kann er nur verwendet werden, wenn der aus dem Ausdruck resultierende Datentyp in einem Index erlaubt ist.
Zusätzlich zu diesen Überlegungen müssen die Verbindungen, die zur Erstellung der Spalte und die zur Erstellung des Index verwendet werden, bestimmte Konfigurationen aufweisen, um diese Aktionen auszuführen.
Die Verbindung zur Erstellung der berechneten Spalte muss die Option ANSI_NULLS aktiviert haben.
Die Verbindung zum Erstellen des Index sowie die Verbindungen zum Einfügen, Aktualisieren und Löschen von Datensätzen, die den Index beeinflussen, müssen die Optionen ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER aktiviert haben. Außerdem muss die Option NUMERIC_ROUNDABORT deaktiviert sein.
Letzte Überlegungen
Abschließend werden wir einige zusätzliche Aspekte betrachten, die für die korrekte Verwendung der berechneten Spalten notwendig sind.
Natürlich können berechnete Spalten weder aktualisiert noch in die Werteliste einer INSERT-Aktion aufgenommen werden. Obwohl die berechneten Spalten Teil der Ergebnisliste einer Select-Anweisung sein können, können sie auch in den Klauseln WHERE, ORDER BY oder in all jenen verwendet werden, in die ein Ausdruck eingefügt werden kann.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Trotz der obigen Ausführungen kann eine berechnete Spalte nicht in der Definition einer DEFAULT- oder FOREIGN KEY-Beschränkung verwendet werden. Sie kann auch nicht in einer NOT NULL-Beschränkungsdefinition verwendet werden.
Andererseits können berechnete Spalten als Teil von PRIMARY KEY- oder UNIQUE-Beschränkungen verwendet werden. Zu diesem Zweck sollte die Definition der berechneten Spalte ein deterministischer Ausdruck sein.
Fazit
Die Verwendung von berechneten Spalten kann in einigen Situationen sehr nützlich sein. Sie müssen sorgfältig prüfen, wo sie verwendet werden sollen, da sie Einschränkungen unterliegen, insbesondere bei der Erstellung von Indizes und der Persistenz von Spalten. Dies ist nur der Anfang. Probieren Sie ruhig neue Dinge aus und experimentieren Sie mit berechneten Spalten, um neue Möglichkeiten zu finden.