Geïndexeerde weergaven maken

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In dit artikel wordt beschreven hoe u indexen maakt in een weergave. De eerste index die in een weergave is gemaakt, moet een unieke geclusterde index zijn. Nadat de unieke geclusterde index is gemaakt, kunt u meer niet-geclusterde indexen maken. Het maken van een unieke geclusterde index in een weergave verbetert de queryprestaties, omdat de weergave op dezelfde manier wordt opgeslagen in de database als een tabel met een geclusterde index. De queryoptimalisatie kan geïndexeerde weergaven gebruiken om de uitvoering van de query te versnellen. De weergave hoeft niet in de query opgenomen te zijn voor de optimizer om die weergave voor een vervanging te overwegen.

Stappen

De volgende stappen zijn vereist voor het maken van een geïndexeerde weergave en zijn essentieel voor de geslaagde implementatie van de geïndexeerde weergave:

1. Controleer of de SET-opties juist zijn voor alle bestaande tabellen waarnaar in de weergave wordt verwezen.
2. Controleer of de SET-opties voor de sessie correct zijn ingesteld voordat u tabellen en de weergave maakt.
3. Controleer of de weergavedefinitie deterministisch is.
4. Controleer of de basistabel dezelfde eigenaar heeft als de weergave.
5. Maak de weergave met behulp van de optie WITH SCHEMABINDING.
6. Maak de unieke geclusterde index in de weergave.

Wanneer u de bewerkingen UPDATE, DELETE of INSERT (Data Manipulation Language of DML) uitvoert op een tabel die wordt gerefereerd door een groot aantal geïndexeerde weergaven, of door minder maar complexe geïndexeerde weergaven, moeten deze geïndexeerde weergaven ook worden bijgewerkt. Hierdoor kunnen de prestaties van DML-query's aanzienlijk afnemen, of in sommige gevallen kan een queryplan niet eens worden geproduceerd.

In dergelijke scenario's test u uw DML-query's voordat u ze in productie neemt, analyseert u het queryplan en optimaliseert of vereenvoudigt u de DML-instructie.

Vereiste SET-opties voor geïndexeerde weergaven

Het evalueren van dezelfde expressie kan verschillende resultaten opleveren in de database-engine wanneer verschillende SET opties actief zijn wanneer de query wordt uitgevoerd. Wanneer de SET optie CONCAT_NULL_YIELDS_NULL bijvoorbeeld is ingesteld op ON, retourneert de expressie 'abc' + NULL de waarde NULL. Nadat CONCAT_NULL_YIELDS_NULL echter is ingesteld op OFF, produceert dezelfde expressie abc.

Om ervoor te zorgen dat de weergaven correct kunnen worden onderhouden en consistente resultaten kunnen retourneren, vereisen geïndexeerde weergaven vaste waarden voor verschillende SET opties. De SET-opties in de volgende tabel moeten worden ingesteld op de waarden die worden weergegeven in de kolom Required value wanneer de volgende voorwaarden optreden:

• De weergave en de volgende indexen in de weergave worden gemaakt.
• De basistabellen waarnaar in de weergave wordt verwezen op het moment dat de weergave wordt gemaakt.
• Wanneer een invoeg-, bijwerk- of verwijderbewerking wordt uitgevoerd op een tabel die deelneemt aan de geïndexeerde weergave. Deze vereiste omvat bewerkingen zoals bulkkopie, replicatie en gedistribueerde query's.
• De geïndexeerde weergave wordt door de query optimizer (deel van de database) gebruikt om het queryplan te genereren.

OPTIES INSTELLEN	Vereiste waarde	Standaardserverwaarde	Verstek OLE DB- en ODBC-waarde	Verstek DB-Library waarde
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

¹ANSI_WARNINGS instellen op ON stelt impliciet ARITHABORT in op ON.

Als u een OLE DB- of ODBC-serververbinding gebruikt, is de enige waarde die moet worden gewijzigd de ARITHABORT-instelling. Alle DB-Library waarden moeten correct worden ingesteld op serverniveau met behulp van sp_configure of vanuit de toepassing met behulp van de opdracht SET.

Belangrijk

We raden u ten zeerste aan de optie ARITHABORT gebruiker in te stellen op ON serverbrede zodra de eerste geïndexeerde weergave of index op een berekende kolom wordt gemaakt in een database op de server.

Deterministische weergavevereiste

De definitie van een geïndexeerde weergave moet deterministischezijn. Een weergave is deterministisch als alle expressies in de selectielijst en de WHERE- en GROUP BY-componenten deterministisch zijn. Deterministische expressies retourneren altijd hetzelfde resultaat wanneer ze worden geëvalueerd met een specifieke set invoerwaarden. Alleen deterministische functies kunnen deelnemen aan deterministische expressies. De functie DATEADD bijvoorbeeld deterministisch is, omdat deze altijd hetzelfde resultaat retourneert voor een bepaalde set argumentwaarden voor de drie parameters. GETDATE is niet deterministisch omdat deze altijd wordt aangeroepen met hetzelfde argument, maar de waarde die wordt geretourneerd telkens wanneer deze wordt uitgevoerd, wordt gewijzigd.

Als u wilt bepalen of een weergavekolom deterministisch is, gebruikt u de eigenschap IsDeterministic van de functie COLUMNPROPERTY. Als u wilt bepalen of een deterministische kolom in een weergave met schemabinding nauwkeurig is, gebruikt u de eigenschap IsPrecise van de functie COLUMNPROPERTY. COLUMNPROPERTY retourneert 1 als TRUE, 0 als FALSEen NULL voor invoer die niet geldig is. Dit betekent dat de kolom niet deterministisch of niet nauwkeurig is.

Zelfs als een expressie deterministisch is, is het exacte resultaat afhankelijk van de processorarchitectuur of -versie van microcode als deze float-expressies bevat. Om de gegevensintegriteit te waarborgen, kunnen dergelijke expressies alleen deelnemen als niet-belangrijke kolommen van geïndexeerde weergaven. Deterministische expressies die geen float-expressies bevatten, worden nauwkeurigegenoemd. Alleen nauwkeurige deterministische expressies kunnen deelnemen aan sleutelkolommen en in WHERE of GROUP BY clausules van geïndexeerde weergaven.

Aanvullende vereisten

Aan de volgende vereisten moet ook worden voldaan, naast de SET opties en deterministische functievereisten

• De gebruiker die CREATE INDEX uitvoert, moet de eigenaar van de weergave zijn.

• Wanneer u de index maakt, moet de IGNORE_DUP_KEY-indexoptie worden ingesteld op OFF (de standaardinstelling).

• Er moet in de weergavedefinitie naar tabellen worden verwezen met tweedelige namen, <schema>.<tablename>.

• Door de gebruiker gedefinieerde functies waarnaar in de weergave wordt verwezen, moeten worden gemaakt met behulp van de optie WITH SCHEMABINDING.

• Alle door de gebruiker gedefinieerde functies waarnaar in de weergave wordt verwezen, moeten worden verwezen door tweedelige namen, <schema>.<function>.

• De eigenschap voor gegevenstoegang van een door de gebruiker gedefinieerde functie moet worden NO SQLen de eigenschap externe toegang moet NOzijn.

• ClR-functies (Common Language Runtime) kunnen worden weergegeven in de selectielijst van de weergave, maar kunnen geen deel uitmaken van de definitie van de geclusterde indexsleutel. CLR-functies kunnen niet voorkomen in de WHERE clausule van de weergave of de ON clausule van een JOIN bewerking in de weergave.

• CLR-functies en -methoden van door de gebruiker gedefinieerde CLR-typen die in de weergavedefinitie worden gebruikt, moeten de eigenschappen hebben ingesteld, zoals wordt weergegeven in de volgende tabel.

  Eigenschap	Notitie
  DETERMINISTIC = TRUE	Moet expliciet worden gedeclareerd als een kenmerk van de Microsoft .NET Framework-methode.
  NAUWKEURIG = WAAR	Moet expliciet worden gedeclareerd als een kenmerk van de .NET Framework-methode.
  DATA ACCESS = GEEN SQL	Bepaald door het kenmerk DataAccess in te stellen op DataAccessKind.None en SystemDataAccess kenmerk op SystemDataAccessKind.None.
  EXTERNE TOEGANG = NEE	Deze eigenschap is standaard ingesteld op NEE voor CLR-routines.

• De weergave moet worden gemaakt met behulp van de optie WITH SCHEMABINDING.

• De weergave moet alleen verwijzen naar basistabellen die zich in dezelfde database bevinden als de weergave. De weergave kan niet verwijzen naar andere weergaven.

• Als GROUP BY aanwezig is, moet de VIEW-definitie COUNT_BIG(*) bevatten en mag deze niet HAVINGbevatten. Deze GROUP BY beperkingen zijn alleen van toepassing op de definitie van de geïndexeerde weergave. Een query kan een geïndexeerde weergave gebruiken in het uitvoeringsplan, zelfs als deze niet voldoet aan deze GROUP BY beperkingen.

• Als de weergavedefinitie een GROUP BY component bevat, kan de sleutel van de unieke geclusterde index alleen verwijzen naar de kolommen die zijn opgegeven in de GROUP BY component.

• De instructie SELECT in de weergavedefinitie mag niet de volgende Transact-SQL syntaxis bevatten:

  Transact-SQL functie	Mogelijke alternatieven
  COUNT	Gebruik COUNT_BIG
  ROWSET functies (OPENDATASOURCE, OPENQUERY, OPENROWSETen OPENXML)
  Rekenkundig gemiddelde (AVG)	Gebruik COUNT_BIG en SUM als afzonderlijke kolommen
  Statistische aggregatiefuncties (STDEV,STDEVP,VARenVARP)
  SUM functie die verwijst naar een null-expressie	Gebruik ISNULL in SUM() om de expressie niet nullbaar te maken
  Andere statistische functies (MIN,MAX,CHECKSUM_AGGenSTRING_AGG)
  Door de gebruiker gedefinieerde statistische functies (SQL CLR)

  SELECT-clausule	Transact-SQL element	Mogelijk alternatief
  WITH cte AS	CTE-WITH (Common Table Expressions)
  SELECT	Subqueries
  SELECT	SELECT [ <table>. ] *	Kolommen expliciet benoemen
  SELECT	SELECT DISTINCT	Gebruik GROUP BY
  SELECT	SELECT TOP
  SELECT	OVER clausule, die classificatie- of aggregaatvensterfuncties bevat
  FROM	LEFT OUTER JOIN
  FROM	RIGHT OUTER JOIN
  FROM	FULL OUTER JOIN
  FROM	OUTER APPLY
  FROM	CROSS APPLY
  FROM	Afgeleide tabelexpressies (dat wil zeggen, met behulp van SELECT in de FROM-clausule)
  FROM	Zelfkoppelingen
  FROM	Tabelvariabelen
  FROM	Inline tabel-waardefunctie
  FROM	Tabelwaardefunctie met meerdere instructies
  FROM	PIVOT, UNPIVOT
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	Rechtstreeks een query uitvoeren op de tijdelijke geschiedenistabel
  WHERE	Predicaten voor volledige tekst (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
  GROUP BY	operators voor CUBE, ROLLUPof GROUPING SETS	Afzonderlijke geïndexeerde weergaven definiëren voor elke combinatie van GROUP BY kolommen
  GROUP BY	HAVING
  Operators instellen	UNION, UNION ALL, EXCEPT, INTERSECT	Gebruik respectievelijk OR, AND NOTen AND in de WHERE-clausule
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  Bronkolomtype	Mogelijk alternatief
  Afgekeurde kolomtypen grote waarde (text, ntexten image)	Kolommen migreren naar varchar(max), nvarchar(max)en varbinary(max) respectievelijk.
  xml- of FILESTREAM-kolommen
  float1 kolommen in de indexsleutel
  Sparse-kolomsets

  ¹ De geïndexeerde weergave kan float- kolommen bevatten; Dergelijke kolommen kunnen echter niet worden opgenomen in de geclusterde indexsleutel.

  Belangrijk

  Geïndexeerde weergaven worden niet ondersteund boven op tijdelijke query's (query's die gebruikmaken van FOR SYSTEM_TIME component).

Aanbevelingen voor datetime en smalldatetime

Wanneer u verwijst naar datetime- en smalldatetime- literal-strings in geïndexeerde weergaven, raden we aan de literal expliciet te converteren naar het gewenste datumtype met behulp van een deterministische datumopmaakstijl. Zie CAST- en CONVERT-voor een lijst met datumnotatiestijlen die deterministisch zijn. Zie de sectie Overwegingen op deze pagina voor meer informatie over deterministische en niet-deterministische expressies.

Expressies waarbij impliciete conversie van tekenreeksen naar datetime- of smalldatetime- worden beschouwd als niet-deterministisch. Zie Niet-deterministische conversie van letterlijke datumtekenreeksen in DATUM-waardenvoor meer informatie.

Prestatieoverwegingen met geïndexeerde weergaven

Wanneer u DML (zoals UPDATE, DELETE of INSERT) uitvoert op een tabel waarnaar wordt verwezen door een groot aantal geïndexeerde weergaven, of minder maar complexe geïndexeerde weergaven, moeten deze geïndexeerde weergaven ook worden bijgewerkt tijdens het uitvoeren van DML. Hierdoor kunnen de prestaties van DML-query's aanzienlijk afnemen, of in sommige gevallen kan een queryplan niet eens worden geproduceerd. In dergelijke scenario's test u uw DML-query's voordat u deze in productie neemt, analyseert u het queryplan en optimaliseert u de DML-instructie.

Om te voorkomen dat de database-engine geïndexeerde weergaven gebruikt, neemt u de OPTION (WEERGAVEN UITVOUWEN) hint op in de query. Als een van de vermelde opties onjuist is ingesteld, voorkomt u met deze optie dat de optimizer de indexen in de weergaven gebruikt. Zie SELECTvoor meer informatie over de OPTION (EXPAND VIEWS) hint.

Aanvullende overwegingen

• De instelling van de large_value_types_out_of_row optie van kolommen in een geïndexeerde weergave wordt overgenomen van de instelling van de bijbehorende kolom in de basistabel. Deze waarde wordt ingesteld met behulp van sp_tableoption. De standaardinstelling voor kolommen die zijn gevormd op basis van expressies, is 0. Dit betekent dat grote waardetypen in de rij worden opgeslagen.

• Geïndexeerde weergaven kunnen worden gemaakt in een gepartitioneerde tabel en kunnen zelf worden gepartitioneerd.

• Alle indexen in een weergave worden verwijderd wanneer de weergave wordt verwijderd. Alle niet-geclusterde indexen en automatisch gemaakte statistieken in de weergave worden verwijderd wanneer de geclusterde index wordt verwijderd. Door de gebruiker gemaakte statistieken in de weergave worden bijgehouden. Niet-geclusterde indexen kunnen afzonderlijk worden verwijderd. Als u de geclusterde index in de weergave verwijdert, wordt de opgeslagen resultatenset verwijderd en gaat de optimizer de weergave verwerken als een standaardweergave.

• Indexen voor tabellen en weergaven kunnen worden uitgeschakeld. Wanneer een geclusterde index voor een tabel is uitgeschakeld, worden indexen van weergaven die aan de tabel zijn gekoppeld, ook uitgeschakeld.

Machtigingen

Als u de weergave wilt maken, moet een gebruiker de machtiging CREATE VIEW in de database opslaan en ALTER machtiging voor het schema waarin de weergave wordt gemaakt. Als de basistabel zich in een ander schema bevindt, is de REFERENCES machtiging voor de tabel minimaal vereist. Als de gebruiker die de index maakt verschilt van de gebruikers die de weergave hebben gemaakt, is voor het maken van de index alleen de ALTER-machtiging voor de weergave vereist (gedekt door ALTER in het schema).

Indexen kunnen alleen worden gemaakt voor weergaven met dezelfde eigenaar als de tabel of tabellen waarnaar wordt verwezen. Dit concept wordt ook wel een intacte eigendomsketen genoemd tussen de weergave en de tabellen. Wanneer de tabel en weergave zich in hetzelfde schema bevinden, is dezelfde schema-eigenaar doorgaans van toepassing op alle objecten in het schema. Daarom is het mogelijk om een weergave te maken en niet de eigenaar van de weergave te zijn. Aan de andere kant is het ook mogelijk dat afzonderlijke objecten in een schema verschillende expliciete eigenaren hebben. De kolom principal_id in sys.tables bevat een waarde als de eigenaar verschilt van de eigenaar van het schema.

Een geïndexeerde weergave maken: een T-SQL-voorbeeld

In het volgende voorbeeld wordt een weergave en een index voor die weergave gemaakt, in de AdventureWorks-database.

--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

De volgende twee query's laten zien hoe de geïndexeerde weergave kan worden gebruikt, ook al is de weergave niet opgegeven in de FROM component.

--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;

Ten slotte toont dit voorbeeld query's rechtstreeks vanuit de geïndexeerde weergave. Automatisch gebruik van een geïndexeerde weergave door de queryoptimalisatie wordt alleen ondersteund in specifieke edities van SQL Server. In de SQL Server Standard-editie moet u de NOEXPAND queryhint gebruiken om rechtstreeks een query uit te voeren op de geïndexeerde weergave. Azure SQL Database en Azure SQL Managed Instance ondersteunen het automatisch gebruik van geïndexeerde weergaven zonder de NOEXPAND hint op te geven. Zie tabelhints (Transact-SQL)voor meer informatie.

--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;

Zie CREATE VIEWvoor meer informatie.

Verwante inhoud

• MAAK INDEX AAN (Transact-SQL)
• ANSI_NULLS (Transact-SQL) instellen
• STEL ANSI_PADDING IN (Transact-SQL)
• STEL ANSI_WARNINGS IN (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• set NUMERIC_ROUNDABORT (Transact-SQL)
• QUOTED_IDENTIFIER (Transact-SQL) INSTELLEN