Erstellen von indizierten Ansichten
Gilt für:SQL ServerAzure SQL-DatenbankAzure 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 in einer Ansicht verbessert die Abfrageleistung, da die Ansicht in der Datenbank auf die gleiche Weise gespeichert wird, wie eine Tabelle mit einem gruppierten Index gespeichert wird. Der Abfrageoptimierer kann indizierte Sichten verwenden, um die Abfrageausführung zu beschleunigen. Auf die Ansicht muss in der Abfrage für den Optimierer nicht verwiesen werden, um diese Ansicht für eine Ersetzung zu berücksichtigen.
Schritte
Die folgenden Schritte sind zum Erstellen einer indizierten Sicht erforderlich und wichtig für eine erfolgreiche Implementierung der indizierten Sicht:
- Überprüfen Sie, ob die
SET
Optionen für alle vorhandenen Tabellen korrekt sind, auf die in der Ansicht verwiesen wird. - Stellen Sie sicher, dass die SET-Optionen für die Sitzung richtig festgelegt sind, bevor Sie Tabellen und die Sicht erstellen.
- Stellen Sie sicher, dass die Sichtdefinition deterministisch ist.
- Vergewissern Sie sich, dass die Basistabelle denselben Besitzer wie die Ansicht hat.
- Erstellen Sie die Sicht mithilfe der Option
WITH SCHEMABINDING
. - Erstellen Sie den eindeutigen gruppierten Index für die Sicht.
Wichtig
Beim Ausführen UPDATE
DELETE
oder Ausführen von INSERT
Vorgängen (Data Manipulation Language oder DML) in einer Tabelle, auf die von einer großen Anzahl indizierter Ansichten verwiesen wird, oder weniger, aber sehr komplexe indizierte Ansichten, müssen diese 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 Ansichten
Die Auswertung desselben Ausdrucks kann unterschiedliche Ergebnisse im Datenbankmodul erzeugen, wenn unterschiedliche SET-Optionen aktiv sind, wenn die Abfrage ausgeführt wird. 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 Werte festgelegt werden, die in der Spalte "Erforderlicher Wert " angezeigt werden, wenn die folgenden Bedingungen auftreten:
- Die Sicht und nachfolgende Indizes für die Sicht werden erstellt.
- Die Basistabellen, auf die beim Erstellen der Ansicht in dieser verwiesen wird.
- Für eine Tabelle, die Teil der indizierten Sicht ist, wird ein Einfüge-, Update- oder Löschvorgang durchgeführt. 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 | EIN | EIN | EIN | OFF |
ANSI_PADDING | EIN | EIN | EIN | OFF |
ANSI_WARNINGS 1 | EIN | EIN | EIN | OFF |
ARITHABORT | EIN | EIN | OFF | OFF |
CONCAT_NULL_YIELDS_NULL | EIN | EIN | EIN | OFF |
NUMERIC_ROUNDABORT | OFF | OFF | OFF | OFF |
QUOTED_IDENTIFIER | EIN | EIN | EIN | OFF |
1 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 mithilfe oder über die Anwendung mithilfe des SET
Befehls auf Serverebene sp_configure
richtig festgelegt werden.
Wichtig
Es wird dringend empfohlen, die ARITHABORT
Benutzeroption serverweit ON
festzulegen, sobald die erste indizierte Ansicht oder der Index einer berechneten Spalte in einer beliebigen Datenbank auf dem Server erstellt wird.
Anforderung zur Verwendung deterministischer Sichten
Die Definition einer indizierten Ansicht muss deterministisch sein. Eine Sicht ist deterministisch, wenn alle Ausdrücke in der Auswahlliste sowie die WHERE
-Klausel und die GROUP BY
-Klausel deterministisch sind. Deterministische Ausdrücke geben immer dasselbe Ergebnis zurück, wenn sie mit einem bestimmten Satz 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 sie immer mit demselben Argument aufgerufen wird, aber der Wert, den er zurückgibt, ändert sich bei jeder Ausführung.
Verwenden Sie die IsDeterministic
Eigenschaft der COLUMNPROPERTY-Funktion , um zu bestimmen, ob eine Ansichtsspalte deterministisch ist. Um festzustellen, ob eine deterministische Spalte in einer Ansicht mit Schemabindung präzise ist, verwenden Sie die IsPrecise
Eigenschaft der COLUMNPROPERTY
Funktion. COLUMNPROPERTY
gibt zurück1
, 0
wenn FALSE
TRUE
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, kann das exakte Ergebnis von der Prozessorarchitektur oder der Version des Microcodes abhängen, 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 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
Die folgenden Anforderungen müssen zusätzlich zu den SET
Optionen und deterministischen Funktionsanforderungen ebenfalls erfüllt sein:
Der Benutzer, der die
CREATE INDEX
-Anweisung ausführt, muss der Besitzer der Sicht sein.Wenn Sie den Index erstellen, muss die
IGNORE_DUP_KEY
Indexoption auf (die Standardeinstellung) festgelegtOFF
werden.Auf Tabellen muss in der Sichtdefinition mit dem zweiteiligen Namen Schema.Tabellenname verwiesen werden.
Benutzerdefinierte Funktionen, auf die in der Sicht verwiesen wird, müssen mit der Option
WITH SCHEMABINDING
erstellt werden.Beim Verweis auf benutzerdefinierte Funktionen in der Sicht müssen zweiteilige Namen verwendet werden: <Schema>.<Funktion>.
Die Datenzugriffseigenschaft einer benutzerdefinierten Funktion muss
NO SQL
lauten, und die Eigenschaft für den externen Zugriff mussNO
lauten.ClR-Funktionen (Common Language Runtime) können in der Auswahlliste der Ansicht angezeigt werden, können jedoch nicht Teil der Definition des gruppierten Indexschlüssels sein. CLR-Funktionen können in der WHERE-Klausel der Ansicht oder der ON-Klausel eines JOIN-Vorgangs in der Ansicht nicht angezeigt werden.
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
aufDataAccessKind.None
undSystemDataAccess
Attribut aufSystemDataAccessKind.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 Ansicht kann nicht auf andere Ansichten verweisen.
Wenn
GROUP BY
vorhanden ist, muss die VIEW-DefinitionCOUNT_BIG(*)
enthalten, währendHAVING
nicht enthalten sein darf. DieseGROUP BY
-Einschränkungen gelten nur für die indizierte Sichtdefinition. Eine Abfrage kann eine indizierte Ansicht im Ausführungsplan verwenden, auch wenn sie dieseGROUP 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 derGROUP BY
-Klausel angegeben werden.Die SELECT-Anweisung in der Ansichtsdefinition darf nicht die folgende Transact-SQL-Syntax enthalten:
Transact-SQL-Funktion Mögliche Alternativen COUNT
Verwenden von COUNT_BIG
ROWSET-Funktionen ( OPENDATASOURCE
,OPENQUERY
,OPENROWSET
undOPENXML
)Arithmetisches Mittel AVG
Verwenden COUNT_BIG
undSUM
als separate SpaltenStatistische Aggregatfunktionen ( STDEV
,STDEVP
,VAR
undVARP
)Eine SUM
-Funktion, die auf einen Ausdruck verweist, der NULL-Werte zulässtVerwenden von ISNULL
innenSUM()
, um den Ausdruck nicht nullwertig zu machenAndere Aggregatfunktionen ( MIN
,MAX
,CHECKSUM_AGG
undSTRING_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 von GROUP BY
SELECT
SELECT TOP
SELECT
Die OVER
-Klausel, die Fensterrangfunktionen oder Fensteraggregatfunktionen enthältFROM
LEFT OUTER JOIN
FROM
RIGHT OUTER JOIN
FROM
FULL OUTER JOIN
FROM
OUTER APPLY
FROM
CROSS APPLY
FROM
Abgeleitete Tabellenausdrücke (d. a. dies wird in der FROM
Klausel verwendetSELECT
)FROM
Selbstjoins FROM
Tabellenvariablen FROM
Inlinetabellenwert-Funktion FROM
Mehrwertige Funktion mit mehreren Anweisungen FROM
PIVOT
,UNPIVOT
FROM
TABLESAMPLE
FROM
FOR SYSTEM_TIME
Abfragen der zeitlichen Verlaufstabelle direkt WHERE
Volltext-Prädikate ( CONTAINS
,FREETEXT
,CONTAINSTABLE
,FREETEXTTABLE
)GROUP BY
CUBE
-,ROLLUP
- oderGROUPING SETS
-OperatorenDefinieren separater indizierte Ansichten für jede Spaltenkombination GROUP BY
GROUP BY
HAVING
Mengenoperatoren UNION
,UNION ALL
,EXCEPT
,INTERSECT
Verwenden , OR
,AND NOT
undAND
in derWHERE
KlauselORDER BY
ORDER BY
ORDER BY
OFFSET
Quellspaltentyp Mögliche Alternative Veraltete Spaltentypen für große Werte ( Text, ntext und Bild) Migrieren Sie Spalten zu varchar(max), nvarchar(max), bzw. varbinary(max). XML- oder FILESTREAM-Spalten Float1 Spalten im Indexschlüssel Spaltensätze mit geringer Dichte 1 Die indizierte Ansicht kann Float-Spalten enthalten. Solche Spalten können jedoch nicht in den gruppierten Indexschlüssel einbezogen werden.
Wichtig
Indizierte Sichten, die temporale Abfragen (Abfragen, die die
FOR SYSTEM_TIME
-Klausel verwenden) überlagern, werden nicht unterstützt.
Empfehlungen zu 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 datumsformatvorlagen, die deterministisch sind, finden Sie unter CAST und CONVERT (Transact-SQL).For a list of the date format styles that are deterministic, see CAST and CONVERT (Transact-SQL). 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 (zUPDATE
. B. oder INSERT
) für eine Tabelle ausführen, DELETE
auf die von einer großen Anzahl indizierter Ansichten verwiesen wird, oder weniger, aber komplexe indizierte Ansichten, müssen diese indizierten Ansichten auch während der DML-Ausführung aktualisiert werden. Daher kann die DML-Abfrageleistung erheblich beeinträchtigt werden, oder in einigen Fällen kann ein Abfrageplan nicht einmal 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.
Um zu verhindern, dass das Datenbankmodul indizierte Ansichten verwendet, fügen Sie den OPTION (EXPAND VIEWS)
Hinweis auf die Abfrage ein. Außerdem kann der Optimierer die Indizes für die Sichten nicht verwenden, wenn eine der aufgeführten Optionen falsch festgelegt ist. Weitere Informationen zum Hinweis finden Sie unter SELECT (Transact-SQL).For more information about the OPTION (EXPAND VIEWS)
hint, see SELECT (Transact-SQL).
Verschiedene 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 hat, von den Benutzern unterscheidet, die die Ansicht erstellt haben, ist für die Indexerstellung allein die ALTER-Berechtigung für die Ansicht erforderlich (abgedeckt durch ALTER im Schema).
Indizes können nur für Ansichten erstellt werden, die denselben Besitzer wie die referenzierte Tabelle oder Tabellen haben. Dies wird auch als intakte Eigentumskette zwischen der Ansicht und den Tabellen bezeichnet. Wenn sich Tabelle und Ansicht im selben Schema befinden, gilt derselbe Schemabesitzer normalerweise für alle Objekte innerhalb des Schemas. Daher ist es möglich, eine Ansicht zu erstellen und nicht der Besitzer der Ansicht zu sein. Andererseits ist es auch möglich, dass einzelne Objekte innerhalb eines Schemas über unterschiedliche explizite Besitzer verfügen. Die principal_id
Spalte in sys.tables
enthält einen Wert, wenn sich der Besitzer vom Schemabesitzer unterscheidet.
Erstellen einer indizierten Sicht: ein T-SQL-Beispiel
Im folgenden Beispiel wird eine Ansicht und ein Index für diese Ansicht in der AdventureWorks
Datenbank erstellt.
--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 Ansicht verwendet werden kann, obwohl die Ansicht 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
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
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;
Schließlich zeigt dieses Beispiel die direkte Abfrage aus der indizierten Ansicht 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 NOEXPAND
Abfragehinweis verwenden, um die indizierte Ansicht direkt abzufragen. Seit SQL Server 2016 (13.x) Service Pack 1 unterstützen alle Editionen die automatische Verwendung einer indizierten Ansicht. 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 (Transact-SQL).
Nächste Schritte
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Tickets als Feedbackmechanismus für Inhalte auslaufen lassen und es durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter:Einreichen und Feedback anzeigen für