Freigeben über

  • Artikel

Erstellen von Indizes für berechnete Spalten

Sie können Indizes für berechnete Spalten definieren, sofern die folgenden Anforderungen erfüllt sind:

  • Anforderungen hinsichtlich des Besitzes

  • Anforderungen hinsichtlich des Determinismus

  • Anforderungen hinsichtlich der Präzision

  • Anforderungen hinsichtlich des Datentyps

  • Anforderungen hinsichtlich der SET-Option

Anforderungen hinsichtlich des Besitzes

Alle Funktionsverweise in der berechneten Spalte müssen denselben Besitzer wie die Tabelle aufweisen.

Anforderungen hinsichtlich des Determinismus

Ausdrücke gelten als deterministisch, wenn sie für eine bestimmte Gruppen von Eingaben stets dasselbe Ergebnis zurückgeben. Die IsDeterministic-Eigenschaft der COLUMNPROPERTY-Funktion meldet, ob computed_column_expression deterministisch ist.

  • computed_column_expression muss deterministisch sein. computed_column_expression ist deterministisch, wenn eine oder mehrere der folgenden Bedingungen zutreffen:

    • Alle Funktionen, auf die der Ausdruck verweist, sind deterministisch und präzise. Zu diesen Funktionen zählen benutzerdefinierte und integrierte Funktionen. Weitere Informationen finden Sie unter Deterministische und nicht deterministische Funktionen. Funktionen können unpräzise sein, wenn die berechnete Spalte als PERSISTED markiert ist. Weitere Informationen finden Sie unter Erstellen von Indizes für permanent berechnete Spalten nachfolgend in diesem Thema.

    • Alle Spalten, auf die der Ausdruck verweist, stammen aus der Tabelle, die die berechnete Spalte enthält.

    • Kein Spaltenverweis ruft Daten aus mehreren Zeilen ab. Beispielsweise hängen Aggregatfunktionen wie SUM oder AVG von Daten aus mehreren Zeilen ab, und computed_column_expression wäre dadurch nicht deterministisch.

    • Es besteht kein Systemdatenzugriff oder Benutzerdatenzugriff.

  • Jede berechnete Spalte, die einen CLR-Ausdruck (Common Language Runtime) enthält, muss deterministisch und als PERSISTED markiert sein, bevor die Spalte indiziert werden kann. Ausdrücke des CLR-benutzerdefinierten Typs sind in den Definitionen berechneter Spalten zulässig. Berechnete Spalten, deren Typ ein CLR-benutzerdefinierter Typ ist, können indiziert werden, sofern der Typ vergleichbar ist. Weitere Informationen finden Sie unter Benutzerdefinierte CLR-Typen.

HinweisHinweis

Wenn Sie auf Zeichenfolgenliterale des Date-Datentyps in indizierten berechneten Spalten in SQL Server verweisen, ist es ratsam, das Literal explizit in den gewünschten Datentyp zu konvertieren, indem Sie ein deterministisches Datenformat verwenden.Eine Liste der deterministischen Datenformatstile finden Sie unter CAST und CONVERT. Ausdrücke für die implizierte Konvertierung von Zeichenfolgen in date-Datentypen werden als nicht deterministisch bezeichnet, es sei denn, der Kompatibilitätsgrad der Datenbank ist auf 80 oder niedriger festgelegt.Ursache hierfür ist, dass die Ergebnisse von den LANGUAGE- und DATEFORMAT-Einstellungen der Serversitzung abhängig sind. Die Ergebnisse des Ausdrucks CONVERT (datetime, '30 listopad 1996', 113) hängen beispielsweise von der LANGUAGE-Einstellung ab, da die Zeichenfolge '30 listopad 1996' für verschiedene Monate in verschiedenen Sprachen steht. In ähnlicher Weise interpretiert Database Engine (Datenbankmodul) in dem Ausdruck DATEADD(mm,3,'2000-12-01') die Zeichenfolge '2000-12-01' basierend auf der DATEFORMAT-Einstellung.

Die implizierte Konvertierung von Nicht-Unicode-Zeichendaten zwischen Sortierungen wird auch als nicht deterministisch erachtet, wenn der Kompatibilitätsgrad nicht auf 80 oder niedriger festgelegt ist.

Beträgt die Einstellung des Kompatibilitätsgrades der Datenbank 90, können Sie keine Indizes für berechnete Spalten erstellen, die diese Ausdrücke enthalten. Vorhandene berechnete Spalten, die diese Ausdrücke aus einer aktualisierten Datenbank enthalten, sind jedoch verwaltbar. Bei Verwendung indizierter berechneter Spalten, die implizite Konvertierungen von Zeichenfolgen in Datumsangaben enthalten, sollten Sie sicherstellen, dass die LANGUAGE- und DATEFORMAT-Einstellungen in der Datenbank und den Anwendungen konsistent sind, um mögliche Beschädigungen der Indizes zu vermeiden.

Anforderungen hinsichtlich der Präzision

computed_column_expression muss präzise sein. computed_column_expression ist präzise, wenn eine oder mehrere der folgenden Bedingungen zutreffen:

  • Es handelt sich um keinen Ausdruck des float- oder real-Datentyps.

  • In der Definition des Ausdrucks wird kein float- oder real-Datentyp verwendet. Beispielsweise ist in der folgenden Anweisung die y-Spalte vom int-Datentyp und deterministisch, aber nicht präzise.

    CREATE TABLE t2 (a int, b int, c int, x float, 
   y AS CASE x 
         WHEN 0 THEN a 
         WHEN 1 THEN b 
         ELSE c 
      END)
HinweisHinweis

Jeder float- oder real-Ausdruck gilt als nicht präzise und kann nicht als Schlüssel eines Indexes verwendet werden; ein float- oder real-Ausdruck kann in einer indizierten Sicht, jedoch nicht als Schlüssel verwendet werden. Dies gilt auch für berechnete Spalten. Jede Funktion, jeder Ausdruck oder jede benutzerdefinierte Funktion gilt als unpräzise, wenn Sie float- oder real-Ausdrücke enthält. Das gilt auch für logische (Vergleiche).

Die IsPrecise-Eigenschaft der COLUMNPROPERTY-Funktion meldet, ob computed_column_expression präzise ist.

Anforderungen hinsichtlich des Datentyps

  • Der für die berechnete Spalte definierte computed_column_expression-Wert darf keine text-, ntext- oder image-Datentypen ergeben.

  • Berechnete Spalten, die aus den Datentypen image, ntext, text, varchar(max), nvarchar(max), varbinary(max) und xml abgeleitet wurden, können indiziert werden, solange der Datentyp der berechneten Spalte als Indexschlüsselspalte zulässig ist.

  • Berechnete Spalten, die aus image-, ntext- und text-Datentypen abgeleitet sind, können Nichtschlüsselspalten (eingeschlossene Spalten) in einem nicht gruppierten Index sein, so lange der Datentyp der berechneten Spalte für Nichtschlüsseldatenspalten zulässig ist.

Anforderungen hinsichtlich der SET-Option

  • Die ANSI_NULLS-Option auf Verbindungsebene muss auf ON festgelegt sein, wenn die CREATE TABLE- oder ALTER TABLE-Anweisung, die die berechnete Spalte definiert, ausgeführt wird. Die OBJECTPROPERTY-Funktion meldet mithilfe der IsAnsiNullsOn-Eigenschaft, ob die Option auf ON festgelegt ist.

  • Für die Verbindung, für die der Index erstellt wird, und für alle Verbindungen, die versuchen, INSERT-, UPDATE- oder DELETE-Anweisungen auszuführen, die Werte des Indexes ändern, müssen sechs SET-Optionen auf ON und eine SET-Option auf OFF festgelegt sein. Der Optimierer ignoriert einen Index für eine berechnete Spalte für alle SELECT-Anweisungen, die von einer Verbindung ausgeführt werden, die diese Optionseinstellungen nicht aufweist.

    • Die NUMERIC_ROUNDABORT-Option muss auf OFF festgelegt sein, und die folgenden Optionen müssen auf ON festgelegt sein:

    • ANSI_NULLS

    • ANSI_PADDING

    • ANSI_WARNINGS

    • ARITHABORT

    • CONCAT_NULL_YIELDS_NULL

    • QUOTED_IDENTIFIER

    Beim Festlegen von ANSI_WARNINGS auf ON wird implizit auch ARITHABORT auf ON festgelegt, wenn der Kompatibilitätsgrad der Datenbank auf 90 gesetzt ist. Wird der Kompatibilitätsgrad der Datenbank auf 80 oder niedriger festgelegt, muss die ARITHABORT-Option explizit auf ON festgelegt werden.Weitere Informationen finden Sie unter SET-Optionen mit Auswirkungen auf Ergebnisse.

Erstellen von Indizes für dauerhafte berechnete Spalten

Sie können einen Index für eine berechnete Spalte erstellen, die mit einem deterministischen, jedoch unpräzisen Ausdruck definiert wird, wenn die Spalte in der CREATE TABLE- oder ALTER TABLE-Anweisung als PERSISTED markiert wurde. Das bedeutet, dass Database Engine (Datenbankmodul) die berechneten Werte in der Tabelle speichert und sie aktualisiert, wenn andere Spalten, von denen die berechnete Spalte abhängt, aktualisiert werden. Database Engine (Datenbankmodul) verwendet diese dauerhaften Werte, wenn ein Index für die Spalte erstellt wird und wenn in einer Abfrage auf den Index verwiesen wird. Diese Option ermöglicht Ihnen das Erstellen eines Indexes für eine berechnete Spalte, wenn Database Engine (Datenbankmodul) nicht präzise nachweisen kann, ob eine Funktion, die berechnete Spaltenausdrücke zurückgibt, insbesondere eine CLR-Funktion, die in .NET Framework erstellt wurde, sowohl deterministisch als auch präzise ist.