Erstellen von indizierten Anzeigen

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

In diesem Artikel wird beschrieben, wie Sie Indizes für eine Sicht erstellen. Der erste Index, der für eine Sicht erstellt wird, muss ein eindeutiger gruppierter Index sein. Nachdem der eindeutige gruppierte Index erstellt wurde, können Sie weitere nicht gruppierte Indizes erstellen. Das Erstellen eines eindeutigen gruppierten Indexes für eine Sicht verbessert die Abfrageleistung, da die Sicht wie eine Tabelle mit einem gruppierten Index in der Datenbank gespeichert wird. Der Abfrageoptimierer kann indizierte Sichten verwenden, um die Abfrageausführung zu beschleunigen. Es ist nicht erforderlich, dass in der Abfrage auf die jeweilige Sicht verwiesen wird, damit der Optimierer diese Sicht als Ersatz berücksichtigt.

Schritte

Die folgenden Schritte sind zum Erstellen einer indizierten Sicht erforderlich und wichtig für eine erfolgreiche Implementierung der indizierten Sicht:

1. Stellen Sie sicher, dass die SET-Optionen für alle vorhandenen Tabellen korrekt sind, auf die in der Sicht verwiesen wird.
2. Stellen Sie sicher, dass die SET-Optionen für die Sitzung richtig festgelegt sind, bevor Sie Tabellen und die Sicht erstellen.
3. Stellen Sie sicher, dass die Sichtdefinition deterministisch ist.
4. Vergewissern Sie sich, dass die Basistabelle denselben Besitzer wie die Ansicht hat.
5. Erstellen Sie die Sicht mithilfe der Option WITH SCHEMABINDING.
6. Erstellen Sie den eindeutigen gruppierten Index für die Sicht.

Wenn Sie UPDATE, DELETE oder INSERT Operationen (Data Manipulation Language, oder DML) auf einer Tabelle ausführen, die von einer großen Anzahl indizierter Ansichten oder weniger, aber komplexen indizierten Ansichten referenziert wird, müssen diese referenzierten indizierten Ansichten ebenfalls aktualisiert werden. Als Folge daraus kann die DML-Abfrageleistung erheblich beeinträchtigt werden. In einigen Fällen kann kein Abfrageplan erstellt werden.

Testen Sie Ihre DML-Abfragen in solchen Fällen, bevor Sie diese für die Produktion verwenden, analysieren Sie den Abfrageplan, und optimieren bzw. vereinfachen Sie die DML-Anweisung.

Erforderliche SET-Optionen für indizierte Anzeigen

Das Auswerten desselben Ausdrucks kann in Datenbank-Engine zu unterschiedlichen Ergebnissen führen, wenn bei der Ausführung der Abfrage unterschiedliche SET-Optionen aktiviert sind. Wenn die SET-Option CONCAT_NULL_YIELDS_NULL auf ON festgelegt ist, gibt beispielsweise der Ausdruck 'abc' + NULL den Wert NULL zurück. Wenn die Option CONCAT_NULL_YIELDS_NULL allerdings auf OFF festgelegt ist, ergibt derselbe Ausdruck abc.

Um sicherzustellen, dass die Sichten ordnungsgemäß verwaltet werden können und konsistente Ergebnisse zurückgeben, sind für indizierte Sichten feste Werte für mehrere SET-Optionen erforderlich. Die SET-Optionen in der folgenden Tabelle müssen auf die in der Spalte Required value angezeigten Werte festgelegt werden, wenn eine der folgenden Bedingungen zutrifft:

• Die Sicht und nachfolgende Indizes für die Sicht werden erstellt.
• Die Basistabellen, auf die beim Erstellen der Ansicht in dieser verwiesen wird.
• Wenn für eine Tabelle, die Teil der indizierten Sicht ist, ein Einfüge-, Update- oder Löschvorgang durchgeführt wird. Dazu gehören Vorgänge wie Massenkopieren, Replikation und verteilte Abfragen.
• Die indizierte Sicht wird vom Abfrageoptimierer verwendet, um den Abfrageplan zu erstellen.

SET-Optionen	Erforderlicher Wert	Standardserverwert	Standard OLE DB- und ODBC-Wert	Standard DB-Library-Wert
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS 1	ON	ON	ON	OFF
ARITHABORT	ON	ON	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	ON	ON	OFF

¹ Durch das Festlegen von ANSI_WARNINGS auf ON wird ARITHABORT implizit auf ON festgelegt.

Wenn Sie eine OLE DB- oder ODBC-Serververbindung verwenden, müssen Sie nur den Wert der ARITHABORT-Einstellung ändern. Alle DB-Library-Werte müssen entweder auf Serverebene mit dem Befehl sp_configure oder von der Anwendung aus mit dem Befehl SET korrekt eingestellt werden.

Wichtig

Wir empfehlen Ihnen dringend, die Option Benutzer ARITHABORT serverweit auf ON zu setzen, sobald die erste indizierte Ansicht oder der erste Index für eine berechnete Spalte in einer beliebigen Datenbank auf dem Server erstellt wurde.

Anforderung zur Verwendung deterministischer Sichten

Die Definition einer indizierten Anzeige muss deterministisch sein. Eine Sicht ist deterministisch, wenn alle Ausdrücke in der Auswahlliste und die WHERE-Klausel und die GROUP BY-Klausel deterministisch sind. Deterministische Ausdrücke geben stets dasselbe Ergebnis zurück, wenn sie mit einer bestimmten Gruppe von Eingabewerten ausgewertet werden. Nur deterministische Funktionen können Teil von deterministischen Ausdrücken sein. Beispielsweise ist die DATEADD-Funktion deterministisch, weil sie für eine bestimmte Gruppe von Argumentwerten immer das gleiche Ergebnis für die drei Parameter zurückgibt. GETDATE ist nicht deterministisch, da diese Funktion immer mit dem gleichen Argument aufgerufen wird, der zurückgegebene Wert jedoch bei jeder Ausführung unterschiedlich ist.

Um zu bestimmen, ob eine Anzeige-Spalte deterministisch ist, verwenden Sie die IsDeterministic-Eigenschaft der COLUMNPROPERTY-Funktion. Um festzustellen, ob eine deterministische Spalte in einer Anzeige mit Schemabindung präzise ist, verwenden Sie die Eigenschaft IsPrecise der Funktion COLUMNPROPERTY. COLUMNPROPERTY gibt 1 zurück, wenn TRUE, 0 wenn FALSE, und NULL für Eingaben, die ungültig sind. Dies bedeutet, dass die Spalte nicht deterministisch oder nicht präzise ist.

Auch wenn ein Ausdruck deterministisch ist, hängt das exakte Ergebnis von der Prozessorarchitektur oder der Version des Microcodes ab, wenn dieser Ausdruck float-Ausdrücke enthält. Um die Datenintegrität sicherzustellen, können solche Ausdrücke nur als Nichtschlüsselspalten von indizierten Sichten verwendet werden. Deterministische Ausdrücke, die keine float-Ausdrücke enthalten, werden als präzise bezeichnet. Nur präzise deterministische Ausdrücke können in indizierten Sichten Teile von Schlüsselspalten und WHERE- oder GROUP BY-Klauseln sein.

Zusätzliche Anforderungen

Zusätzlich zu den Anforderungen in Bezug auf SET-Optionen und deterministischen Funktionen müssen die folgenden Anforderungen erfüllt werden

• Der Benutzer, der die CREATE INDEX-Anweisung ausführt, muss der Besitzer der Sicht sein.

• Wenn Sie den Index erstellen, muss die Indexoption IGNORE_DUP_KEY auf OFF (Standardeinstellung) festgelegt sein.

• Auf Tabellen muss in der Sichtdefinition mit dem zweiteiligen Namen <schema>.<tablename> verwiesen werden.

• Benutzerdefinierte Funktionen, auf die in der Sicht verwiesen wird, müssen mit der Option WITH SCHEMABINDING erstellt werden.

• Auf benutzerdefinierte Funktionen, auf die in der Sicht verwiesen wird, muss mit zweiteiligen Namen verwiesen werden: <schema>.<function>.

• Die Datenzugriffseigenschaft einer benutzerdefinierten Funktion muss NO SQL lauten, und die Eigenschaft für den externen Zugriff muss NO lauten.

• Common Language Runtime-Funktionen (CLR) können in der Auswahl-Liste der Ansicht angezeigt werden, können aber nicht Teil der Definition des gruppierten Indexschlüssels sein. CLR-Funktionen können nicht in der WHERE-Klausel der Anzeige oder in der ON-Klausel einer JOIN-Operation in der Anzeige auftreten.

• Für CLR-Funktionen und -Methoden der CLR-benutzerdefinierten Typen, die in der Sichtdefinition verwendet werden, müssen die in der folgenden Tabelle dargestellten Eigenschaften festgelegt werden.

  Eigenschaft	Hinweis
  DETERMINISTIC = TRUE	Muss explizit als ein Attribut der Microsoft .NET Framework-Methode deklariert werden.
  PRECISE = TRUE	Muss explizit als ein Attribut der .NET Framework-Methode deklariert werden.
  DATA ACCESS = NO SQL	Bestimmt durch Festlegen des Attributs DataAccess auf DataAccessKind.None und Attribut SystemDataAccess auf SystemDataAccessKind.None.
  EXTERNAL ACCESS = NO	Diese Eigenschaft ist für CLR-Routinen standardmäßig auf NO festgelegt.

• Die Sicht muss mithilfe der Option WITH SCHEMABINDING erstellt werden.

• Die Sicht darf nur auf Basistabellen in derselben Datenbank wie die Sicht verweisen. Die Anzeige kann nicht auf andere Anzeigen verweisen.

• Wenn GROUP BY vorhanden ist, muss die VIEW-Definition COUNT_BIG(*) enthalten, während HAVING nicht enthalten sein darf. Diese GROUP BY-Einschränkungen gelten nur für die indizierte Sichtdefinition. Im Ausführungsplan einer Abfrage kann eine indizierte Anzeige auch dann verwendet werden, wenn sie diese GROUP BY-Einschränkungen nicht erfüllt.

• Wenn die Sichtdefinition eine GROUP BY-Klausel enthält, kann der Schlüssel des eindeutigen gruppierten Indexes nur auf die Spalten verweisen, die in der GROUP BY-Klausel angegeben werden.

• Die SELECT-Anweisung in der Anzeigedefinition darf die folgende Transact-SQL-Syntax nicht enthalten:

  Transact-SQL-Funktion	Mögliche Alternativen
  COUNT	Verwenden Sie COUNT_BIG
  ROWSET-Funktionen (OPENDATASOURCE, OPENQUERY, OPENROWSET und OPENXML)
  Arithmetisches Mittel (AVG)	Verwenden COUNT_BIG und SUM als separate Spalten
  Statistische Aggregatfunktionen (STDEV, STDEVP, VAR und VARP)
  Eine SUM-Funktion, die auf einen Ausdruck verweist, der NULL-Werte zulässt	Verwenden von ISNULL in SUM(), um den Ausdruck nicht nullwertig zu machen
  Andere Aggregatfunktionen (MIN, MAX, CHECKSUM_AGG und STRING_AGG)
  Benutzerdefinierte Aggregatfunktionen (SQL CLR)

  SELECT-Klausel	Transact-SQL-Element	Mögliche Alternative
  WITH cte AS	Allgemeine Tabellenausdrücke (CTE) WITH
  SELECT	Unterabfragen
  SELECT	SELECT [ <table>. ] *	Explizite Namensspalten
  SELECT	SELECT DISTINCT	Verwenden Sie GROUP BY
  SELECT	SELECT TOP
  SELECT	Die OVER-Klausel, die Fensterrangfunktionen oder Fensteraggregatfunktionen enthält
  FROM	LEFT OUTER JOIN
  FROM	RIGHT OUTER JOIN
  FROM	FULL OUTER JOIN
  FROM	OUTER APPLY
  FROM	CROSS APPLY
  FROM	Abgeleitete Tabellenausdrücke (d.h. Verwendung von SELECT in der FROM-Klausel)
  FROM	Selbstjoins
  FROM	Tabellenvariablen
  FROM	Inline-Tabellenwertfunktion
  FROM	Multi-Anweisungen Tabellenwertfunktion
  FROM	PIVOT, UNPIVOT
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	Direkte Abfragen der temporalen Verlaufstabelle
  WHERE	Volltextprädikate (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
  GROUP BY	CUBE-, ROLLUP- oder GROUPING SETS-Operatoren	Definieren separater indizierte Anzeigen für jede Spaltenkombination GROUP BY
  GROUP BY	HAVING
  Mengenoperatoren	UNION, UNION ALL, EXCEPT, INTERSECT	Verwendung von OR, AND NOT und AND in der Klausel WHERE
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  Quell-Spaltentyp	Mögliche Alternative
  Veraltete große Wert-Spaltentypen (text, ntext, und image)	Migrieren Sie Spalten zu varchar(max), nvarchar(max), bzw. varbinary(max).
  xml- oder FILESTREAM-Spalten
  float 1 Spalten im Indexschlüssel
  Spaltensätze mit geringer Dichte

  ¹ Die indizierte Anzeige kann Spalten mit dem Datentyp float enthalten. Allerdings dürfen solche Spalten nicht im Schlüssel des gruppierten Indexes enthalten sein.

  Wichtig

  Indizierte Sichten, die temporale Abfragen (Abfragen, die die FOR SYSTEM_TIME-Klausel verwenden) überlagern, werden nicht unterstützt.

Empfehlungen für datetime und smalldatetime

Wenn Sie in indizierten Sichten auf datetime - und smalldatetime -Zeichenfolgenliterale verweisen, wird empfohlen, das Literal mithilfe eines deterministischen Datenformats explizit in den gewünschten Datentyp zu konvertieren. Eine Liste der deterministischen Datenformatstile finden Sie unter CAST und CONVERT. Weitere Informationen zu deterministischen und nicht deterministischen Ausdrücken finden Sie im Abschnitt Weitere Überlegungen auf dieser Seite.

Ausdrücke, die eine implizite Konvertierung von Zeichenfolgen in datetime oder smalldatetime umfassen, werden als nicht deterministisch angesehen. Weitere Informationen finden Sie unter Nicht deterministische Konvertierung von Datumsliteralzeichenfolgen in DATE-Werte.

Überlegungen zur Leistung bei indizierten Sichten

Wenn Sie DML (z. B. UPDATE, DELETE oder INSERT) für eine Tabelle ausführen, auf die durch eine große Anzahl von indizierten Anzeigen oder durch wenige, jedoch sehr komplexe indizierte Anzeigen verwiesen wird, müssen diese indizierten Azeigen während der DML-Ausführung ebenfalls aktualisiert werden. Als Folge daraus kann die DML-Abfrageleistung erheblich beeinträchtigt werden. In einigen Fällen kann kein Abfrageplan erstellt werden. Testen Sie Ihre DML-Abfragen in solchen Fällen, bevor Sie diese für die Produktion verwenden, analysieren Sie den Abfrageplan, und optimieren bzw. vereinfachen Sie die DML-Anweisung.

Wenn Sie verhindern möchten, dass Datenbank-Engine indizierte Sichten verwendet, schließen Sie den OPTION (EXPAND VIEWS)-Hinweis in die Abfrage ein. Wenn eine der aufgeführten Optionen falsch eingestellt ist, verhindert diese Option außerdem, dass der Optimierer die Indizes für die Ansichten verwendet. Weitere Informationen zum OPTION (EXPAND VIEWS)-Hinweis finden Sie unter SELECT.

Weitere Überlegungen

• Die Einstellung der Option large_value_types_out_of_row der Spalten in einer indizierten Sicht wird von der Einstellung für die entsprechende Spalte in der Basistabelle vererbt. Dieser Wert wird mithilfe von sp_tableoptionfestgelegt. Die Standardeinstellung für Spalten, die auf Grundlage von Ausdrücken erstellt werden, ist 0. Das bedeutet, dass umfangreiche Werte innerhalb der Zeile gespeichert werden.

• Indizierte Sichten können für eine partitionierte Tabelle erstellt werden und selbst partitioniert werden.

• Wenn eine Sicht gelöscht wird, werden alle Indizes für diese Sicht gelöscht. Wird der gruppierte Index einer Sicht gelöscht, werden alle nicht gruppierten Indizes und alle automatisch erstellten Statistiken der Sicht gelöscht. Vom Benutzer erstellte Statistiken zur Sicht werden beibehalten. Nicht gruppierte Indizes können einzeln gelöscht werden. Durch das Löschen des gruppierten Index der Sicht wird das gespeicherte Resultset entfernt, und der Optimierer verarbeitet die Sicht von nun an wieder wie eine Standardsicht.

• Indizes für Tabellen und Sichten können deaktiviert werden. Wenn ein gruppierter Index für eine Tabelle deaktiviert wird, werden Indizes für die den Tabellen zugeordneten Sichten auch deaktiviert.

Berechtigungen

Zum Erstellen der Sicht muss ein Benutzer die CREATE VIEW-Berechtigung in der Datenbank und die ALTER-Berechtigung in dem Schema verwenden, in dem die Sicht erstellt wird. Wenn sich die Basistabelle in einem anderen Schema befindet, ist mindestens die REFERENCES-Berechtigung für die Tabelle erforderlich. Wenn sich der Benutzer, der den Index erstellt, von den Benutzern unterscheidet, die die Anzeige erstellt haben, ist allein für die Indexerstellung die ALTER-Berechtigung für die Anzeige erforderlich (durch ALTER für das Schema abgedeckt).

Indizes können nur für Anzeigen erstellt werden, die denselben Besitzer wie die Tabelle oder Tabellen haben, auf die verwiesen wird. Dieses Konzept wird auch als eine intakte Besitzkette‭ zwischen der Ansicht und der Tabellen bezeichnet. Wenn sich die Tabelle und die Anzeige innerhalb desselben Schemas befinden, ist in der Regel derselbe Schemabesitzer für alle Objekte innerhalb des Schemas zugeordnet. Daher ist es möglich, eine Anzeige zu erstellen und dabei nicht der Besitzer der Anzeige zu sein. Andererseits ist es auch möglich, dass einzelne Objekte innerhalb eines Schemas über unterschiedliche explizite Besitzer verfügen. Die Spalte principal_id in sys.tables enthält einen Wert, wenn sich der Besitzer vom Schemabesitzer unterscheidet.

Erstellen einer indizierten Sicht: ein T-SQL-Beispiel

Das folgende Beispiel erstellt eine Anzeige und einen Index für diese Anzeige in der Datenbank AdventureWorks.

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;

--Create view with SCHEMABINDING.
IF OBJECT_ID('Sales.vOrders', 'view') IS NOT NULL
    DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
    WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate,
    ProductID,
    COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od,
    Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate,
    ProductID;
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (
    OrderDate,
    ProductID
);
GO

Die nächsten beiden Abfragen veranschaulichen, wie die indizierte Anzeige verwendet werden kann, obwohl die Anzeige nicht in der FROM Klausel angegeben ist.

--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
    OrderDate,
    ProductID
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
        AND 800
GROUP BY OrderDate,
    ProductID
ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate,
    SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
        AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;

Zum Abschluss zeigt dieses Beispiel die direkte Abfrage aus der indizierten Anzeige an. Vor SQL Server 2016 (13.x) Service Pack 1 wurde die automatische Verwendung einer indizierten Sicht durch den Abfrageoptimierer nur in bestimmten Editionen von SQL Server unterstützt. In der SQL Server Standard Edition müssen Sie den Abfragehinweis NOEXPAND verwenden, um die indizierte Anzeige direkt abzufragen. Seit SQL Server 2016 (13.x) Service Pack 1 unterstützen alle Editionen die automatische Verwendung einer indizierten Anzeige. Azure SQL-Datenbank und Azure SQL Managed Instance unterstützen auch die automatische Verwendung indizierter Ansichten ohne Angabe des NOEXPAND-Hinweises. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

Weitere Informationen finden Sie unter CREATE VIEW.

Zugehöriger Inhalt

• CREATE INDEX (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• SET ANSI_PADDING (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)