Skapa indexerade vyer

Artikel
03/05/2025

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance

I den här artikeln beskrivs hur du skapar index i en vy. Det första indexet som skapas i en vy måste vara ett unikt grupperat index. När det unika klustrade indexet har skapats kan du skapa fler icke-grupperade index. Om du skapar ett unikt grupperat index i en vy förbättras frågeprestanda, eftersom vyn lagras i databasen på samma sätt som en tabell med ett grupperat index lagras. Frågeoptimeraren kan använda indexerade vyer för att påskynda frågekörningen. Vyn behöver inte refereras i frågan för att optimeraren ska kunna överväga den vyn för en ersättning.

Steg

Följande steg krävs för att skapa en indexerad vy och är viktiga för en lyckad implementering av den indexerade vyn:

Kontrollera att de SET alternativen är korrekta för alla befintliga tabeller som ska refereras till i vyn.
Kontrollera att SET alternativen för sessionen har angetts korrekt innan du skapar tabeller och vyn.
Kontrollera att vydefinitionen är deterministisk.
Kontrollera att bas-tabellen har samma ägare som vyn.
Skapa vyn med hjälp av alternativet WITH SCHEMABINDING.
Skapa det unika klustrade indexet i vyn.

När du kör UPDATE, DELETE eller INSERT åtgärder (datamanipuleringsspråk eller DML) i en tabell som refereras till av ett stort antal indexerade vyer, eller färre men komplexa indexerade vyer, måste även de refererade indexerade vyerna uppdateras. Därför kan DML-frågeprestanda försämras avsevärt, eller i vissa fall kan en frågeplan inte ens skapas.

I sådana scenarier testar du dina DML-frågor före produktionsanvändning, analyserar frågeplanen och finjusterar/förenklar DML-instruktionen.

Obligatoriska SET-alternativ för indexerade vyer

Utvärdering av samma uttryck kan ge olika resultat i databasmotorn när olika SET alternativ är aktiva när frågan körs. När till exempel SET alternativet CONCAT_NULL_YIELDS_NULL har angetts till ONreturnerar uttrycket 'abc' + NULL värdet NULL. Men när CONCAT_NULL_YIELDS_NULL har angetts till OFFgenererar samma uttryck abc.

För att säkerställa att vyerna kan underhållas korrekt och returnera konsekventa resultat kräver indexerade vyer fasta värden för flera SET alternativ. De SET alternativen i följande tabell måste anges till de värden som visas i kolumnen Required value när följande villkor inträffar:

Vyn och efterföljande index i vyn skapas.
De basstabeller som refereras till i vyn när vyn skapas.
När någon infognings-, uppdaterings- eller borttagningsåtgärd utförs i en tabell som deltar i den indexerade vyn. Det här kravet omfattar åtgärder som masskopiering, replikering och distribuerade frågor.
Den indexerade vyn används av frågeoptimeraren för att skapa frågeplanen.

SET-inställningar	Obligatoriskt värde	Standardservervärde	Förvald OLE DB- och ODBC-värde	Förval DB-Library värde
`ANSI_NULLS`	`ON`	`ON`	`ON`	`OFF`
`ANSI_PADDING`	`ON`	`ON`	`ON`	`OFF`
`ANSI_WARNINGS` ¹	`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`

¹ Att ställa in ANSI_WARNINGS till ON anger implicit ARITHABORT till ON.

Om du använder en OLE DB- eller ODBC-serveranslutning är det enda värdet som måste ändras inställningen ARITHABORT. Alla DB-Library värden måste anges korrekt antingen på servernivå med hjälp av sp_configure eller från programmet med hjälp av kommandot SET.

Viktig

Vi rekommenderar starkt att du anger alternativet ARITHABORT användare för att ON hela servern så snart den första indexerade vyn eller indexet på en beräknad kolumn skapas i en databas på servern.

Krav för deterministisk vy

Definitionen av en indexerad vy måste vara deterministisk. En vy är deterministisk om alla uttryck i urvalslistan och satserna WHERE och GROUP BY är deterministiska. Deterministiska uttryck returnerar alltid samma resultat när de utvärderas med en specifik uppsättning indatavärden. Endast deterministiska funktioner kan delta i deterministiska uttryck. Till exempel är funktionen DATEADD deterministisk eftersom den alltid returnerar samma resultat för en viss uppsättning argumentvärden för dess tre parametrar. GETDATE är inte deterministiskt eftersom det alltid anropas med samma argument, men värdet som returneras ändras varje gång det körs.

Om du vill avgöra om en vykolumn är deterministisk använder du egenskapen IsDeterministic för funktionen COLUMNPROPERTY. Om du vill avgöra om en deterministisk kolumn i en vy med schemabindning är exakt använder du egenskapen IsPrecise för funktionen COLUMNPROPERTY. COLUMNPROPERTY returnerar 1 om TRUE, 0 om FALSEoch NULL för indata som inte är giltiga. Det innebär att kolumnen inte är deterministisk eller inte exakt.

Även om ett uttryck är deterministiskt beror det exakta resultatet på processorarkitekturen eller versionen av mikrokod om det innehåller flyttaluttryck. För att säkerställa dataintegritet kan sådana uttryck endast delta som icke-nyckelkolumner i indexerade vyer. Deterministiska uttryck som inte innehåller flyttaluttryck kallas exakta. Endast exakta deterministiska uttryck kan delta i nyckelkolumner och i WHERE eller GROUP BY-satser i indexerade vyer.

Ytterligare krav

Följande krav måste också uppfyllas, utöver SET alternativ och deterministiska funktionskrav

Användaren som kör CREATE INDEX måste vara ägare till vyn.
När du skapar indexet måste alternativet IGNORE_DUP_KEY index anges till OFF (standardinställningen).
Tabeller måste refereras till med tvådelade namn, <schema>.<tablename>, i vydefinitionen.
Användardefinierade funktioner som refereras i vyn måste skapas med hjälp av alternativet WITH SCHEMABINDING.
Alla användardefinierade funktioner som refereras till i vyn måste refereras till av tvådelade namn, <schema>.<function>.
Dataåtkomstegenskapen för en användardefinierad funktion måste vara NO SQLoch egenskapen för extern åtkomst måste vara NO.
CLR-funktioner (Common Language Runtime) kan visas i vyns urvalslista, men kan inte ingå i definitionen av den klustrade indexnyckeln. CLR-funktioner kan inte visas i WHERE-satsen eller i ON-satsen för en JOIN-åtgärd i vyn.

CLR-funktioner och metoder för CLR-användardefinierade typer som används i vydefinitionen måste ha egenskaperna angivna enligt följande tabell.

Egenskap	Anteckning
DETERMINISTISK = SANT	Måste uttryckligen deklareras som ett attribut för Microsoft .NET Framework-metoden.
PRECIS = SANT	Måste deklareras explicit som ett attribut för .NET Framework-metoden.
DATAÅTKOMST = INGEN SQL	Bestäms genom att ange attributet `DataAccess` till `DataAccessKind.None` och ange attributet `SystemDataAccess` till `SystemDataAccessKind.None`.
EXTERN ÅTKOMST = NEJ	Den här egenskapen är som standard NEJ för CLR-rutiner.

Vyn måste skapas med hjälp av alternativet WITH SCHEMABINDING.
Vyn får endast referera till bastabeller som finns i samma databas som vyn. Vyn kan inte referera till andra vyer.
Om GROUP BY finns måste VIEW-definitionen innehålla COUNT_BIG(*) och får inte innehålla HAVING. Dessa GROUP BY begränsningar gäller endast för den indexerade vydefinitionen. En fråga kan använda en indexerad vy i sin exekveringsplan även om den inte uppfyller dessa restriktioner (GROUP BY).
Om vydefinitionen innehåller en GROUP BY-sats kan nyckeln för det unika klustrade indexet endast referera till de kolumner som anges i GROUP BY-satsen.

SELECT-instruktionen i vydefinitionen får inte innehålla följande Transact-SQL syntax:

funktionen Transact-SQL	Möjliga alternativ
`COUNT`	Använd `COUNT_BIG`
`ROWSET` funktioner (`OPENDATASOURCE`, `OPENQUERY`, `OPENROWSET`och `OPENXML`)
Aritmetiskt medelvärde (`AVG`)	Använd `COUNT_BIG` och `SUM` som separata kolumner
Statistiska aggregeringsfunktioner (`STDEV`,`STDEVP`,`VAR`och`VARP`)
`SUM` funktion som refererar till ett null-uttryck	Använd `ISNULL` inuti `SUM()` för att göra uttrycket icke-nullbart
Andra mängdfunktioner (`MIN`,`MAX`,`CHECKSUM_AGG`och`STRING_AGG`)
Användardefinierade aggregeringsfunktioner (SQL CLR)

SELECT-sats	Transact-SQL-element	Möjliga alternativ
`WITH cte AS`	Vanliga tabelluttryck (CTE) `WITH`
`SELECT`	Underfrågor
`SELECT`	`SELECT [ <table>. ] *`	Namnge kolumnerna uttryckligen
`SELECT`	`SELECT DISTINCT`	Använd `GROUP BY`
`SELECT`	`SELECT TOP`
`SELECT`	`OVER`-sats, som innehåller funktioner för rangordning eller aggregeringsfönster
`FROM`	`LEFT OUTER JOIN`
`FROM`	`RIGHT OUTER JOIN`
`FROM`	`FULL OUTER JOIN`
`FROM`	`OUTER APPLY`
`FROM`	`CROSS APPLY`
`FROM`	Härledda tabelluttryck (det vill: använda `SELECT` i `FROM`-satsen)
`FROM`	Självkopplingar
`FROM`	Tabellvariabler
`FROM`	Infogad tabellvärdesfunktion
`FROM`	Tabellvärdefunktion med flera satser
`FROM`	`PIVOT`, `UNPIVOT`
`FROM`	`TABLESAMPLE`
`FROM`	`FOR SYSTEM_TIME`	Fråga tabellen med tidshistorik direkt
`WHERE`	Fulltextpredikat (`CONTAINS`, `FREETEXT`, `CONTAINSTABLE`, `FREETEXTTABLE`)
`GROUP BY`	operatorerna `CUBE`, `ROLLUP`eller `GROUPING SETS`	Definiera separata indexerade vyer för varje kombination av `GROUP BY` kolumner
`GROUP BY`	`HAVING`
Ange operatorer	`UNION`, `UNION ALL`, `EXCEPT`, `INTERSECT`	Använd `OR`, `AND NOT`och `AND` i satsen `WHERE` respektive
`ORDER BY`	`ORDER BY`
`ORDER BY`	`OFFSET`

Källkolumntyp	Möjliga alternativ
Inaktuella kolumntyper för stora värden (text, ntextoch bild)	Migrera kolumner till varchar(max), nvarchar(max)och varbinary(max) respektive.
xml-- eller FILESTREAM-kolumner
flyttalstyp¹ kolumner i indexnyckeln
Glesa kolumnuppsättningar

¹ Den indexerade vyn kan innehålla kolumner med flyttal. Sådana kolumner kan dock inte ingå i den klustrade indexnyckeln.

Viktig

Indexerade vyer stöds inte ovanpå temporala frågor (frågor som använder FOR SYSTEM_TIME-satsen).

Rekommendationer för datetime och smalldatetime

När du refererar till datetime- och smalldatetime- strängliteraler i indexerade vyer rekommenderar vi att du uttryckligen konverterar literalvärdet till den datumtyp du vill ha genom att använda ett deterministiskt datumformat. En lista över formatmallar för datumformat som är deterministiska finns i CAST och CONVERT. Mer information om deterministiska och icke-deterministiska uttryck finns i avsnittet Överväganden på den här sidan.

Uttryck som omfattar implicit konvertering av teckensträngar till datetime- eller smalldatetime- anses vara icke-terministiska. Mer information finns i icke-deterministisk konvertering av bokstavliga datumsträngar till DATE-värden.

Prestandaöverväganden med indexerade vyer

När du kör DML (till exempel UPDATE, DELETE eller INSERT) i en tabell som refereras till av ett stort antal indexerade vyer, eller färre men komplexa indexerade vyer, måste även dessa indexerade vyer uppdateras under DML-körningen. Därför kan DML-frågeprestanda försämras avsevärt, eller i vissa fall kan en frågeplan inte ens skapas. I sådana scenarier testar du dina DML-frågor före produktionsanvändning, analyserar frågeplanen och finjusterar/förenklar DML-instruktionen.

För att förhindra att databasmotorn använder indexerade vyer, inkludera OPTION (EXPANDERA VYER) hint i frågan. Om något av alternativen i listan är felaktigt angivna förhindrar det här alternativet också att optimeraren använder indexen i vyerna. För mer information om ledtråden OPTION (EXPAND VIEWS), se SELECT.

Ytterligare överväganden

Inställningen för alternativet large_value_types_out_of_row för kolumner i en indexerad vy ärvs från inställningen för kolumnen i bastabellen. Det här värdet anges med hjälp av sp_tableoption. Standardinställningen för kolumner som skapas från uttryck är 0. Det innebär att stora värdetyper lagras på rad.
Indexerade vyer kan skapas i en partitionerad tabell och kan själva partitioneras.
Alla index i en vy tas bort när vyn tas bort. Alla icke-grupperade index och statistik som skapats automatiskt i vyn tas bort när det klustrade indexet tas bort. Användarskapad statistik i vyn behålls. Icke-grupperade index kan tas bort individuellt. Om du släpper det klustrade indexet på vyn tar du bort den lagrade resultatuppsättningen och optimeringsmotorn återgår till bearbetning av vyn som en standardvy.
Index för tabeller och vyer kan inaktiveras. När ett klustrat index i en tabell inaktiveras inaktiveras även index för vyer som är associerade med tabellen.

Behörigheter

För att skapa vyn måste en användare ha behörigheten CREATE VIEW i databasen och ALTER behörighet för schemat där vyn skapas. Om bastabellen finns i ett annat schema krävs minst REFERENCES behörighet på tabellen. Om användaren som skapar indexet skiljer sig från de användare som skapade vyn, krävs ALTER-behörighet för vyn enbart för att skapa indexet (omfattas av ALTER i schemat).

Index kan bara skapas för vyer som har samma ägare som den refererade tabellen eller tabellerna. Det här begreppet kallas även för en intakt ägarskapskedja mellan vyn och tabellerna. När tabellen och vyn finns i samma schema gäller vanligtvis samma schemaägare för alla objekt i schemat. Därför är det möjligt att skapa en vy och inte vara ägare till vyn. Å andra sidan är det också möjligt att enskilda objekt i ett schema har olika explicita ägare. Kolumnen principal_id i sys.tables innehåller ett värde om ägaren skiljer sig från schemaägaren.

Skapa en indexerad vy: ett T-SQL-exempel

I följande exempel skapas en vy och ett index för den vyn i AdventureWorks-databasen.

--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 följande två frågorna visar hur den indexerade vyn kan användas, även om vyn inte anges i FROM-satsen.

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

Slutligen visar det här exemplet frågor direkt från den indexerade vyn. Automatisk användning av en indexerad vy av frågeoptimeraren stöds endast i specifika utgåvor av SQL Server. I SQL Server Standard-utgåvan måste du använda NOEXPAND frågetipset för att fråga den indexerade vyn direkt. Azure SQL Database och Azure SQL Managed Instance tillåter automatiskt användning av indexerade vyer utan att specificera NOEXPAND hint. Mer information finns i Tabelltips (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;

Mer information finns i CREATE VIEW.

Dela via

Skapa indexerade vyer

Steg

Obligatoriska SET-alternativ för indexerade vyer

Krav för deterministisk vy

Ytterligare krav

Rekommendationer för datetime och smalldatetime

Prestandaöverväganden med indexerade vyer

Ytterligare överväganden

Behörigheter

Skapa en indexerad vy: ett T-SQL-exempel

Feedback

Ytterligare resurser

Dela via

Skapa indexerade vyer

Steg

Obligatoriska SET-alternativ för indexerade vyer

Krav för deterministisk vy

Ytterligare krav

Rekommendationer för datetime och smalldatetime

Prestandaöverväganden med indexerade vyer

Ytterligare överväganden

Behörigheter

Skapa en indexerad vy: ett T-SQL-exempel

Relaterat innehåll

Feedback

Ytterligare resurser