Grundlagen

Der erste Teil dieses Artikels lehnt sich stark an die Online-Doku zu berechneten Spalten an..

Eine berechnete Spalte wird aus einem Ausdruck berechnet, der andere Spalten in derselben Tabelle verwenden kann. Der Ausdruck kann

• der Name einer nicht berechneten Spalte,
• eine Konstante,
• eine Funktion
• oder eine beliebige durch einen oder mehrere Operatoren verbundene Kombination der genannten Möglichkeiten sein.

Der Ausdruck kann keine Unterabfrage sein.

In der AdventureWorks2008R2-Beispieldatenbank hat beispielsweise die TotalDue-Spalte der Sales.SalesOrderHeader-Tabelle folgende Definition: TotalDue AS Subtotal + TaxAmt + Freight.

Wenn nichts anderes angegeben wird, handelt es sich bei berechneten Spalten um virtuelle Spalten, die nicht physisch in der Tabelle gespeichert sind. Die darin enthaltenen Werte werden jedes Mal neu berechnet, wenn in einer Abfrage darauf verwiesen wird. Und hier liegt unter Umständen auch das Problem bei Einsatz solcher Spalten. Verwendet man diese nämlich in einer Where-Bedingung, wird die zugrundeliegende Berechung (z. B. eine user defined function) für jede Zeile der Tabelle aufgerufen. Falls es andere Einschränkungen gibt, kann sich diese Anzahl noch reduzieren, aber in der Regel ist dies nicht das gewünschte Verhalten. Deshalb gibt es die Möglichkeit das Ergebnis der Berechnung dauerhaft in der Tabelle zu speichern. Die Database Engine (Datenbankmodul) verwendet das Schlüsselwort PERSISTED in den Anweisungen CREATE TABLE und ALTER TABLE, um berechnete Spalten physisch in der Tabelle zu speichern. Die enthaltenen Werte werden aktualisiert, wenn sich Spalten ändern, die Teil der Berechnung sind.
Verwendet man jetzt diese berechnete Spalte in einer Where-Bedingung, wird auf den vorliegenden Wert zurückgegriffen und keine erneute Berechnung durchgeführt.

Berechnete Spalten können in

• Auswahllisten,
• WHERE-Klauseln,
• ORDER BY-Klauseln
• oder an anderen Stellen verwendet werden, an denen reguläre Ausdrücke verwendet werden können

Eine berechnete Spalte kann natürlich nicht das Ziel einer INSERT- oder UPDATE-Anweisung sein.

Man kann einen Index für eine berechnete Spalte erstellen (weitere Einschränkungen zu Datentypen etc. siehe die Online-Doku). Hier sehe ich auch den Hauptvorteil für dieses Konstrukt, um ansonsten umständliche und ineffiziente Abfragen auf missgebildete Daten zu beschleunigen.

Einfaches Beispiel

In diesem ersten Beispiel soll es um die Möglichkeit gehen schnell einen numerischen Teil in einem alphanumerischen Feld zu finden. Hier wurden die Handelsregisternummern aus verschiedenen Ländern dafür verwendet. Je nach Land und Art des Handelsregisters haben diese ein anderes alphanumerisches Präfix. HRA steht z. B. für die Abteilung A des deutschen Handelsregisters wo Einzelunternehmen, Personengesellschaften und rechtsfähige wirtschaftliche Vereine geführt werden.

In den Daten gibt es also verschiedene Präfixe und da es sich teilweise um manuelle Erfassung handelt, kann dies auch fehlerhaft sein, oder komplett wegfallen. Gesucht werden soll aber in diesem Beispiel nur der numerische Teil, wobei hier auch nur (unscharf) nach den ersten drei oder vier Ziffern gesucht werden soll.

Die Tabelle ist einfach strukturiert und hier gibt es noch ein Feld Zusatz, was lediglich den Zweck hat die Länge der Datensätze zu erhöhen, um die Nützlichkeit des Index einfacher zu demonstrieren.
CREATE TABLE dbo.test_compute (id integer IDENTITY, Zusatz VARCHAR(200), Handelsregister VARCHAR(22)); ALTER TABLE test_compute ADD PRIMARY KEY(id); CREATE INDEX xie1test_compute ON test_compute(Handelsregister);

Als erstes wird die Tabelle gefüllt, wobei das Zusatz-Feld wie oben beschrieben als Ballast mitgeführt wird. Hier werden fünf verschiedene Präfixe verwendet und hierzu jeweils 10.000 Sätze geschrieben. Die vollständigen SQLs sind im Anhang aufgeführt.
Der erste Test bringt dann auch 600 Zeilen, obwohl nur 500 erwartet werden
SELECT id, Zusatz, Handelsregister             FROM test_compute  WHERE Handelsregister LIKE '%123%';

Ändert man die Where-Bedingung etwas, erhält man auch die erwarteten Ergebnisse:
SELECT id, Zusatz, Handelsregister             FROM test_compute  WHERE Handelsregister LIKE '%123%' AND Handelsregister NOT LIKE '%[0-9]123%'

Die Abfrage läuft natürlich über den clustered Index, da der Nutzen des XIE1 begrenzt ist.

Als nächstes wird eine user defined Function (dbo.udf_Handelsregister_Teil) erstellt, die durch einen einfachen Vergleich den Startwert der ersten Ziffer in dem String findet und den Rest dann zurückliefert. Darauf aufbauend wird die berechnete Spalte angelegt:
ALTER TABLE dbo.test_compute ADD Handelsregister_Teil 
AS dbo.udf_Handelsregister_Teil (Handelsregister) PERSISTED;

Jetzt legen wir noch einen Index über diese berechnete Spalte an, was möglich ist, da sie ja mit dem Schlüsselwort PERSISTED angelegt wurde:
CREATE INDEX xie2test_compute ON test_compute(Handelsregister_Teil);

Verwenden wir jetzt diese neue Spalte in der Where-Bedingung, sieht man die Verwendung des neuen Index im Zugriffsplan:
SELECT id, Zusatz, Handelsregister FROM test_compute  WHERE Handelsregister_Teil LIKE '123%';

Der vom Optimizer noch vorgeschlagene Index würde alle Spalten der Select-Liste beinhalten und wäre vielleicht hier zielführend, aber grundsätzlich nicht sinnvoll. Stattdessen nehmen wir in Kauf, dass für jede gefundene Zeile im Index noch die fehlenden Werte aus dem Clustered-Index ermittelt werden.

Um so kleiner die Treffermenge ist, die schnell durch die berechnete Spalte ermittelt werden kann, um so größer wird der Vorteil gegenüber der Lösung ohne berechnete Spalte. Während sich bei der Suche nach drei Ziffern noch die Abfragezeiten im Verhältniss von 46% zu 54% ergeben, so erhält man bei vier Ziffern bereits das Verhältnis von 24% zu 76%, bei fünf Ziffern von 2% zu 98%!

Beispiel mit DATE

Berechnete Spalten können nur persistent werden, wenn sie deterministisch sind. Ausdrücke gelten als deterministisch, wenn sie für eine bestimmte Gruppe von Eingaben stets dasselbe Ergebnis zurückgeben. UDFs sind nicht unbedingt deterministisch!
Die IsDeterministic-Eigenschaft der COLUMNPROPERTY-Funktion meldet, ob die berechnete Spalte deterministisch ist. Genauso kann man auch die Eigenschaft der Funktion abfragen, ob sie deterministisch ist.

SELECT CASE WHEN COLUMNPROPERTY(OBJECT_ID('dbo.test_compute'), 'Handelsregister_Teil', 'IsDeterministic' ) = 1 THEN 'TRUE' WHEN COLUMNPROPERTY(OBJECT_ID('dbo.test_compute'), 'Handelsregister_Teil', 'IsDeterministic' ) = 0 THEN 'FALSE' ELSE 'FEHLER' END AS Spalte_Deterministisch; SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.udf_Handelsregister_Teil'),'isDeterministic') AS Obj_Property, r.IS_DETERMINISTIC, r.ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES r WHERE r.ROUTINE_NAME = 'udf_Handelsregister_Teil';

Bewegt man sich nun im Bereich von DATE- oder DATETIME-Feldern, stellt man schnell fest, dass hier einige Konvertierungen nicht deterministisch sind. Zu CAST und CONVERT verzeichnet die Online-Doku einige Einschränkungen. Wenn man auf Zeichenfolgenliterale des Date-Datentyps in indizierten berechneten Spalten verweist, ist es ratsam, das Literal explizit in den gewünschten Datentyp zu konvertieren, indem man ein deterministisches Datenformat verwendet. Zu den nicht deterministischen Formaten gehören

• alle (yy)-Formate (ohne Jahrhundert)
• und eine Teilmenge der (yyyy)-Formate (mit Jahrhundert)

Die Ursache liegt z. B. in der sprachspezfischen Behandlung von Literalen. So wird '2010-06-22' nicht in ein DateTime-Feld konvertiert werden können, wenn die Sprache 'german' eingestellt ist.

Im folgenden Beispiel wird davon ausgegangen, dass es eine Tabelle gibt, die in einer varchar-Spalte ein Datum enthält. Dieses Datum soll nun zur Selektion oder Sortierung verwendet werden. Damit die notwendige Konvertierung nicht in jedem Statement codiert werden muss, wird eine user defined function erstellt und für die berechnete Spalte verwendet.

Der erste Versuch ist aber nicht zielführend, da in der Folge die UDF nicht determinstisch ist:
SET @dDatum = CAST(SUBSTRING(@Wert,7,4)+ SUBSTRING(@Wert,4,2)+ 
SUBSTRING(@Wert,1,2) AS date); 

Deshalb wird hier anstelle von CAST die Möglichkeit von CONVERT genutzt:
SET @dDatum = CONVERT(date,SUBSTRING(@Wert,7,4)+ SUBSTRING(@Wert,4,2)+ 
SUBSTRING(@Wert,1,2),104);

Nun ist auch die Funktion deterministisch und die Spalte kann dauerhaft angelegt werden und hierüber ein Index definiert werden.

Schemabinding

An dieser Stelle sei noch auf die besondere Bedeutung der Klausel WITH SCHEMABINDING in der UDF hingewiesen. Wird diese nämlich ohne SCHEMABINDING erstellt, ist sie nicht deterministisch und man erhält einen Fehler bei der Erstellung einer dauerhaften berechneten Spalte:

Meldung 4936, Ebene 16, Status 1, Zeile 2
Computed column 'ddatum' in table 'Datenlieferung' cannot be persisted because the column is non-deterministic.

Die Auswirkung dieses Schlüsselwortes merkt man beim Versuch die Funktion wieder zu droppen, wenn die berechnete Spalte bereits angelegt wurde. Es verhindert also die Löschung von Objekten, auf die von anderen Objekten noch verwiesen wird. Auch dies ist eine wichtige Vorraussetzung um sicherzustellen, dass eine berechnete Spalte bei gleichen Eingangswerten stets das gleiche Ergebnis liefert.

  Berechnete_Spalten.sql