Indizes in berechneten Spalten

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

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 teilt mit, ob der Ausdruck einer berechneten Spalte (computed_column_expression) deterministisch ist.

Der computed_column_expression muss deterministisch sein. Ein computed_column_expression ist deterministisch, wenn alle 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 Deterministic and Nondeterministic Functions. Funktionen können unpräzise sein, wenn die berechnete Spalte als PERSISTED markiert ist. Weitere Informationen finden Sie unter Erstellen von Indizes für persistente berechnete Spalten nachfolgend in diesem Artikel.

• 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 ein computed_column_expression wäre dadurch nicht deterministisch.

• Der computed_column_expression kann weder auf Systemdaten noch auf Benutzerdaten zugreifen.

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.

CAST und CONVERT

Wenn Sie in SQL Server auf Zeichenfolgenliterale des Date-Datentyps in indizierten berechneten Spalten 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.

Weitere Informationen finden Sie unter Nicht deterministische Konvertierung von Datumsliteralzeichenfolgen in DATE-Werte.

Kompatibilitätsgrad

Die implizierte Konvertierung von Nicht-Unicode-Zeichendaten zwischen Sortierungen wird 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.

Der Kompatibilitätsgrad 90 entspricht SQL Server 2005 (9.x).

Anforderungen hinsichtlich der Präzision

Der computed_column_expression muss präzise sein. Ein computed_column_expression ist präzise, wenn mindestens eine der folgenden Bedingungen zutrifft:

• 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 eine int 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);
  

Die IsPrecise-Eigenschaft der COLUMNPROPERTY-Funktion teilt mit, ob der Ausdruck einer berechneten Spalte (computed_column_expression) präzise ist.

Anforderungen hinsichtlich des Datentyps

• Der computed_column_expression , der für die berechnete Spalte definiert ist, kann nicht zu einem der Datentypen text, ntextoder image ausgewertet werden.
• 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 den Datentypen image, ntextund text abgeleitet wurden, können Nichtschlüsselspalten (eingeschlossene Spalten) in einem nicht gruppierten Index sein, so lange der Datentyp der berechneten Spalte für Nichtschlüssel-Indexspalten 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 IsAnsiNullsOn 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

Erstellen von Indizes für persistente berechnete Spalten

Manchmal können Sie eine berechnete Spalte erstellen, die mit einem deterministischen, aber unpräzisen Ausdruck definiert ist. Sie können dies tun, wenn die Spalte in der PERSISTED- oder CREATE TABLE-Anweisung als ALTER TABLE markiert ist.

Das bedeutet, dass Datenbank-Engine die berechneten Werte in der Tabelle speichert und sie aktualisiert, wenn andere Spalten, von denen die berechnete Spalte abhängt, aktualisiert werden. Datenbank-Engine verwendet diese persistenten 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 die Datenbank-Engine 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.

Nächste Schritte

• COLUMNPROPERTY (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)