Freigeben über

  • Artikel

Erstellen von indizierten Sichten

Eine Sicht muss die folgenden Anforderungen erfüllen, damit Sie einen gruppierten Index dafür erstellen können:

  • Die Optionen ANSI_NULLS und QUOTED_IDENTIFIER müssen beim Ausführen der CREATE VIEW-Anweisung auf ON festgelegt sein. Die OBJECTPROPERTY-Funktion meldet dies mithilfe der ExecIsAnsiNullsOn- oder ExecIsQuotedIdentOn-Eigenschaften für Sichten.

  • Die Option ANSI_NULLS muss für die Ausführung aller CREATE TABLE-Anweisungen, die Tabellen erstellen, auf die die Sicht verweist, auf ON festgelegt sein.

  • Die Sicht darf nicht auf andere Sichten verweisen, sondern nur auf Basistabellen.

  • Alle Basistabellen, auf die die Sicht verweist, müssen in derselben Datenbank wie die Sicht vorhanden sein und denselben Besitzer wie die Sicht haben.

  • Die Sicht muss mit der Option SCHEMABINDING erstellt werden. Die Schemabindung bindet die Sicht an das Schema der zugrunde liegenden Basistabellen.

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

  • Auf Tabellen und benutzerdefinierte Funktionen muss mit zweiteiligen Namen in der Sicht verwiesen werden. Ein-, drei- und vierteilige Namen sind nicht zulässig.

  • Alle Funktionen, auf die Ausdrücke in der Sicht verweisen, müssen deterministisch sein. Die IsDeterministic-Eigenschaft der OBJECTPROPERTY-Funktion meldet, ob eine benutzerdefinierte Funktion deterministisch ist. Weitere Informationen finden Sie unter Deterministische und nicht deterministische Funktionen.

    HinweisHinweis

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

    Die implizite Konvertierung von Nicht-Unicode-Zeichendaten zwischen Sortierungen wird ebenso als nicht deterministisch bezeichnet, es sei denn, der Kompatibilitätsgrad ist auf 80 oder niedriger festgelegt.

    Das Erstellen von Indizes oder Sichten, die diese Ausdrücke enthalten, ist im Kompatibilitätsmodus von 90 nicht zulässig. Vorhandene Sichten, die diese Ausdrücke aus einer aktualisierten Datenbank enthalten, sind jedoch verwaltbar. Wenn Sie indizierte Sichten verwenden, 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 eventuelle Beschädigungen der indizierten Sichten zu vermeiden.

  • Falls die Sichtdefinition eine Aggregatfunktion verwendet, muss die SELECT-Liste zudem COUNT_BIG (*) einschließen.

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

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

  • Für CLR-Funktionen und -Methoden der CLR-benutzerdefinieren 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

    Wird durch die Festlegung des DataAccess -Attributs auf DataAccessKind.None und des SystemDataAccess-Attributs auf SystemDataAccessKind.None bestimmt.

    EXTERNAL ACCESS = NO

    Diese Eigenschaft ist für CLR-Routinen standardmäßig auf NO festgelegt.

    Weitere Informationen zum Festlegen der Attribute von CLR-Routinenmethoden finden Sie unter Benutzerdefinierte Attribute für CLR-Routinen.

    VorsichtshinweisVorsicht

    Die Eigenschaften von CLR-Rountinenmethoden sollten nicht im Widerspruch zur Funktionalität der Methode festgelegt werden. Anderenfalls können Daten beschädigt werden.

  • Die SELECT-Anweisung in der Sicht darf die folgenden Transact-SQL-Syntaxelemente nicht enthalten:

    • Die *- oder table_name**.***-Syntax zur Angabe von Spalten. Spaltennamen müssen explizit angegeben werden.

    • Der Name einer Tabellenspalte, der als einfacher Ausdruck verwendet wird, kann nicht in mehreren Spalten einer Sicht angegeben werden. Auf eine Spalte kann mehrmals verwiesen werden, falls alle Referenzen oder alle außer einer Referenz auf die Spalte Bestandteil eines komplexen Ausdrucks oder ein Parameter für eine Funktion sind. Folgende Auswahl ist beispielsweise ungültig:

      SELECT ColumnA, ColumnB, ColumnA

      Die folgenden Auswahl ist gültig:

      SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB

    • Ein Ausdruck für eine Spalte, die in der GROUP BY-Klausel verwendet wird, oder ein Ausdruck für die Ergebnisse eines Aggregats.

    • Eine abgeleitete Tabelle.

    • Ein allgemeiner Tabellenausdruck (CTE, Common Table Expression).

    • Rowsetfunktionen.

    • UNION-, EXCEPT- oder INTERSECT-Operatoren.

    • Unterabfragen.

    • Äußere Verknüpfungen oder Selbstverknüpfungen.

    • TOP-Klausel.

    • ORDER BY-Klausel.

    • DISTINCT-Schlüsselwort.

    • COUNT (COUNT_BIG(*) ist zulässig.)

    • Die Aggregatfunktionen AVG, MAX, MIN, STDEV, STDEVP, VAR oder VARP. Wenn AVG(expression) in Abfragen angegeben wird, die auf die indizierte Sicht verweisen, kann der Optimierer das gewünschte Ergebnis häufig berechnen, sofern die Auswahlliste der Sicht SUM(expression) und COUNT_BIG(expression) enthält. Beispielsweise darf die SELECT-Liste einer indizierten Sicht nicht den Ausdruck AVG(column1) enthalten. Falls die SELECT-Liste der Sicht die Ausdrücke SUM(column1) und COUNT_BIG(column1) enthält, kann SQL Server den Mittelwert für eine Abfrage berechnen, die auf die Sicht verweist und AVG(column1) angibt.

    • Eine SUM-Funktion, die auf einen Ausdruck verweist, der NULL zulässt.

    • Die OVER-Klausel, die Fensterrang- oder Fensteraggregatfunktionen enthält.

    • Eine CLR-benutzerdefinierte Aggregatfunktion.

    • Die CONTAINS- oder FREETEXT-Volltextprädikate.

    • COMPUTE- oder COMPUTE BY-Klausel.

    • Die CROSS APPLY- oder OUTER APPLY-Operatoren.

    • Der PIVOT- oder der UNPIVOT-Operator

    • Tabellenhinweise (gilt nur für einen Kompatibilitätsgrad von 90 oder höher).

    • Verknüpfungshinweise.

    • Direkte Verweise auf XQuery-Ausdrücke. Indirekte Verweise (z. B. XQuery-Ausdrücke in einer schemagebundenen, benutzerdefinierten Funktion) sind zulässig.

  • Wenn GROUP BY angegeben wird, muss die Auswahlliste der Sicht einen COUNT_BIG(*)-Ausdruck enthalten, und für die Sichtdefinition kann nicht HAVING, ROLLUP, CUBE oder GROUPING SETS angegeben werden.

Anforderungen für die CREATE INDEX-Anweisung

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. Für Sichtindizes gelten dieselben Benennungskonventionen wie für Tabellenindizes. Der einzige Unterschied besteht darin, dass der Tabellenname durch einen Sichtnamen ersetzt wird. Weitere Informationen finden Sie unter CREATE INDEX (Transact-SQL).

Die CREATE INDEX-Anweisung muss die folgenden Anforderungen sowie die regulären Anforderungen für CREATE INDEX erfüllen:

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

  • Die folgenden SET-Optionen müssen auf ON festgelegt sein, wenn die CREATE INDEX-Anweisung ausgeführt wird:

    • ANSI_NULLS

    • ANSI_PADDING

    • ANSI_WARNINGS

    • CONCAT_NULL_YIELDS_NULL

    • QUOTED_IDENTIFIER

  • Die Option NUMERIC_ROUNDABORT muss auf OFF festgelegt sein. Dies ist die Standardeinstellung.

  • Wird die Datenbank mit einem Kompatibilitätsgrad von 80 oder einem früheren Kompatibilitätsgrad ausgeführt, muss die ARITHABORT-Option auf ON festgelegt werden.

  • Wenn Sie einen gruppierten oder einen nicht gruppierten Index erstellen, muss die Option IGNORE_DUP_KEY auf OFF (Standardeinstellung) festgelegt sein.

  • Die Sicht darf keine text-, ntext- oder image-Spalten aufweisen, selbst wenn in der CREATE INDEX-Anweisung nicht auf diese verwiesen wird.

  • Wenn die SELECT-Anweisung in der Sichtdefinition eine GROUP BY-Klausel angibt, kann der Schlüssel des eindeutigen gruppierten Index nur auf Spalten verweisen, die in der GROUP BY-Klausel angegeben sind.

  • Ein unpräziser Ausdruck, der den Wert einer Indexschlüsselspalte bildet, muss auf eine gespeicherte Spalte in einer Basistabelle verweisen, die der Sicht zugrunde liegt. Bei dieser Spalte kann es sich um eine reguläre gespeicherte Spalte oder eine dauerhafte berechnete Spalte handeln. Es können keine anderen unpräzisen Ausdrücke Teil der Schlüsselspalte einer indizierten Sicht sein.

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_tableoption festgelegt. Die Standardeinstellung für aus Ausdrücken gebildete Spalten ist 0. Das bedeutet, dass umfangreiche Werte in Zeilen gespeichert werden. Weitere Informationen finden Sie unter Verwenden von Datentypen mit umfangreichen Werten.

Nach dem Erstellen des gruppierten Index muss jede Verbindung, die versucht, die Basisdaten für die Sicht zu ändern, auch dieselben Optionseinstellungen aufweisen, die für die Erstellung des Index erforderlich waren. SQL Server generiert einen Fehler und führt einen Rollback für sämtliche NSERT-, UPDATE- oder DELETE-Anweisungen aus, die sich auf das Resultset der Sicht auswirken, sofern die Verbindung, von der die Anweisung ausgeführt wird, nicht die richtigen Optionseinstellungen aufweist. Weitere Informationen finden Sie unter SET-Optionen mit Auswirkungen auf Ergebnisse.

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.

Obwohl in der CREATE UNIQUE CLUSTERED INDEX-Anweisung nur die Spalten angegeben werden, aus denen sich der Schlüssel des gruppierten Index zusammensetzt, wird das vollständige Resultset der Sicht in der Datenbank gespeichert. Wie bei einem gruppierten Index für eine Basistabelle enthält die B-Baumstruktur des gruppierten Index nur die Schlüsselspalten, die Datenzeilen enthalten jedoch alle Spalten des Sichtresultsets.

Wenn Sie Sichten in einem vorhandenen System Indizes hinzufügen möchten, müssen Sie alle Sichten an ein Schema binden, für die Sie einen Index definieren möchten. Sie können folgende Vorgänge ausführen:

  • Löschen der Sicht und erneutes Erstellen der Sicht mithilfe von WITH SCHEMABINDING.

  • Sie können eine zweite Sicht erstellen, die den gleichen Text wie die vorhandene Sicht, aber einen anderen Namen aufweist. Der Abfrageoptimierer berücksichtigt die Indizes in der neuen Sicht, selbst wenn in der FROM-Klausel von Abfragen nicht direkt auf diese verwiesen wird.

    HinweisHinweis

    Sichten oder Tabellen, die Bestandteil einer mit der SCHEMABINDING-Klausel erstellten Sicht sind, können erst dann gelöscht werden, wenn die entsprechende Sicht gelöscht oder geändert wird, sodass die Schemabindung nicht mehr vorhanden ist. Darüber hinaus werden Fehler für ALTER TABLE-Anweisungen für Tabellen erzeugt, die Bestandteil von Sichten mit Schemabindung sind, falls diese Anweisungen die Sichtdefinition betreffen.

Stellen Sie sicher, dass die neue Sicht alle Anforderungen einer indizierten Sicht erfüllt. Dazu müssen Sie möglicherweise den Besitzer der Sicht und aller Basistabellen ändern, auf die diese verweist, damit sie alle dem gleichen Benutzer gehören.

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. Weitere Informationen finden Sie unter Deaktivieren von Indizes.

Beispiel

Im folgenden Beispiel werden eine Sicht und ein Index für diese Sicht erstellt. Dies beinhaltet zwei Abfragen, in denen die indizierte Sicht verwendet wird.

USE AdventureWorks;
GO
--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;
GO
--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
--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
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO