Vytváření indexovaných zobrazení

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Tento článek popisuje, jak vytvořit indexy v zobrazení. První index vytvořený v zobrazení musí být jedinečný clusterovaný index. Po vytvoření jedinečného clusterovaného indexu můžete vytvořit více neclusterovaných indexů. Vytvoření jedinečného clusterovaného indexu v zobrazení zlepšuje výkon dotazů, protože zobrazení je uložené v databázi stejným způsobem jako tabulka s clusterovaným indexem. Optimalizátor dotazů může k urychlení provádění dotazu použít indexovaná zobrazení. Zobrazení nemusí být v dotazu odkazováno, aby optimalizátor mohl toto zobrazení považovat za náhradní.

Schody

K vytvoření indexovaného zobrazení jsou potřeba následující kroky a jsou důležité pro úspěšnou implementaci indexovaného zobrazení:

1. Ověřte správnost možností SET pro všechny existující tabulky, na které budou odkazovány v zobrazení.
2. Před vytvořením tabulek a zobrazení ověřte, že jsou správně nastavené možnosti SET pro relaci.
3. Ověřte, že definice zobrazení je deterministická.
4. Ověřte, že základní tabulka má stejného vlastníka jako zobrazení.
5. Vytvořte zobrazení pomocí možnosti WITH SCHEMABINDING.
6. Vytvořte jedinečný clusterovaný index v zobrazení.

Při provádění operací UPDATE, DELETE nebo INSERT (Jazyk pro manipulaci s daty, nebo DML) na tabulce odkazované velkým počtem indexovaných zobrazení, nebo méně početnými, ale složitými indexovanými zobrazeními, musí být tato odkazovaná indexovaná zobrazení také aktualizována. Výsledkem je, že výkon dotazů DML se může výrazně snížit, nebo se v některých případech dokonce ani nepodaří vytvořit plán dotazu.

V takových scénářích otestujte dotazy DML před použitím v produkčním prostředí, analyzujte plán dotazu a vylaďte nebo zjednodušte příkaz DML.

Požadované možnosti SET pro indexovaná zobrazení

Vyhodnocení stejného výrazu může způsobit různé výsledky v databázovém stroji, pokud jsou při spuštění dotazu aktivní různé možnosti SET. Například po nastavení možnosti SETCONCAT_NULL_YIELDS_NULL na ON, výraz 'abc' + NULL vrátí hodnotu NULL. Po nastavení CONCAT_NULL_YIELDS_NULL na OFFvšak stejný výraz vytvoří abc.

Aby bylo zajištěno správné udržování zobrazení a vrácení konzistentních výsledků, vyžadují indexovaná zobrazení pevné hodnoty pro několik možností SET. Možnosti SET v následující tabulce musí být nastaveny na hodnoty zobrazené ve sloupci Required value vždy, když dojde k následujícím podmínkám:

• Vytvoří se zobrazení a následné indexy v zobrazení.
• Základní tabulky odkazované v zobrazení v okamžiku vytvoření zobrazení.
• Když se provede jakákoli operace vložení, aktualizace nebo odstranění v libovolné tabulce, která se účastní indexovaného zobrazení. Tento požadavek zahrnuje operace, jako jsou hromadné kopírování, replikace a distribuované dotazy.
• Indexované zobrazení používá optimalizátor dotazů k vytvoření plánu dotazu.

Možnosti SET	Požadovaná hodnota	Výchozí hodnota serveru	Výchozí Hodnota OLE DB a ODBC	Výchozí hodnota DB-Library
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

¹ Nastavení ANSI_WARNINGSON implicitně nastaví ARITHABORT na ON.

Pokud používáte připojení k serveru OLE DB nebo ODBC, jedinou hodnotou, kterou je nutné upravit, je nastavení ARITHABORT. Všechny DB-Library hodnoty musí být správně nastaveny na úrovni serveru pomocí sp_configure nebo z aplikace pomocí příkazu SET.

Důležitý

Důrazně doporučujeme nastavit možnost ARITHABORT uživatele na úrovni celého serveru (ON), jakmile je na serveru v libovolné databázi vytvořeno první indexované zobrazení nebo index na vypočítaném sloupci.

Požadavek na deterministické zobrazení

Definice indexovaného zobrazení musí být deterministické. Zobrazení je deterministické, pokud jsou deterministické všechny výrazy v seznamu select a klauzule WHERE a GROUP BY. Deterministické výrazy vždy vrací stejný výsledek pokaždé, když se vyhodnotí konkrétní sadou vstupních hodnot. Deterministické funkce se mohou účastnit deterministických výrazů. Například DATEADD funkce je deterministická, protože vždy vrací stejný výsledek pro libovolnou sadu hodnot argumentů pro své tři parametry. GETDATE není deterministický, protože je vždy vyvolán se stejným argumentem, ale hodnota, kterou vrací, se mění při každém spuštění.

Chcete-li zjistit, zda je sloupec zobrazení deterministický, použijte vlastnost IsDeterministic funkce COLUMNPROPERTY. Chcete-li zjistit, zda je deterministický sloupec v zobrazení s vazbou schématu přesný, použijte vlastnost IsPrecise funkce COLUMNPROPERTY. COLUMNPROPERTY vrátí 1, pokud TRUE, 0 pokud FALSEa NULL pro vstup, který není platný. To znamená, že sloupec není deterministický nebo není přesný.

I když je výraz deterministický, pokud obsahuje výrazy float, přesný výsledek závisí na architektuře procesoru nebo verzi mikrokódu. Aby se zajistila integrita dat, mohou se tyto výrazy účastnit pouze jako neklíčové sloupce indexovaných zobrazení. Deterministické výrazy, které neobsahují výrazy typu float, se nazývají přesné. Přesné deterministické výrazy mohou být použity pouze v klíčových sloupcích a v klauzulích WHERE nebo GROUP BY indexovaných zobrazení.

Další požadavky

Kromě možností SET a deterministických požadavků na funkci musí být splněny také následující požadavky.

• Uživatel, který provádí CREATE INDEX, musí být vlastníkem zobrazení.

• Při vytváření indexu musí být možnost indexu IGNORE_DUP_KEY nastavená na OFF (výchozí nastavení).

• Tabulky musí být odkazovány dvoučástkovými názvy, <schema>.<tablename>, v definici zobrazení.

• Uživatelem definované funkce odkazované v zobrazení musí být vytvořeny pomocí možnosti WITH SCHEMABINDING.

• Na všechny uživatelem definované funkce, na které se odkazuje v zobrazení, je nutné odkazovat pomocí dvoudílných názvů, <schema>.<function>.

• Vlastnost přístupu k datům uživatelem definované funkce musí být NO SQLa vlastnost externího přístupu musí být NO.

• Funkce modulu CLR (Common Language Runtime) se můžou zobrazit v seznamu výběru zobrazení, ale nemůžou být součástí definice clusterovaného indexového klíče. Funkce CLR se v WHERE klauzuli zobrazení ani v ON operaci JOIN v zobrazení nezobrazují.

• Funkce a metody CLR uživatelem definovaných typů CLR používané v definici zobrazení musí mít vlastnosti nastavené, jak je znázorněno v následující tabulce.

  Vlastnost	Poznámka
  DETERMINISTIC = PRAVDA	Musí být deklarována explicitně jako atribut metody Microsoft .NET Framework.
  PRECISE = PRAVDA	Musí být deklarována explicitně jako atribut metody rozhraní .NET Framework.
  PŘÍSTUP K DATŮM = BEZ SQL	Určeno nastavením atributu DataAccess na DataAccessKind.None a SystemDataAccess atribut na SystemDataAccessKind.None.
  EXTERNÍ PŘÍSTUP = NE	Tato vlastnost má ve výchozím nastavení hodnotu NE pro rutiny CLR.

• Zobrazení musí být vytvořeno pomocí možnosti WITH SCHEMABINDING.

• Zobrazení musí odkazovat pouze na základní tabulky, které jsou ve stejné databázi jako zobrazení. Zobrazení nemůže odkazovat na jiná zobrazení.

• Pokud existuje GROUP BY, musí definice VIEW obsahovat COUNT_BIG(*) a nesmí obsahovat HAVING. Tato omezení GROUP BY platí pouze pro definici indexovaného zobrazení. Dotaz může ve svém plánu provádění použít indexované zobrazení, i když těmto omezením GROUP BY nevyhovují.

• Pokud definice zobrazení obsahuje klauzuli GROUP BY, klíč jedinečného clusterovaného indexu může odkazovat pouze na sloupce zadané v klauzuli GROUP BY.

• Příkaz SELECT v definici zobrazení nesmí obsahovat následující syntaxi Transact-SQL:

  Transact-SQL funkce	Možné alternativy
  COUNT	Použij COUNT_BIG
  ROWSET funkce (OPENDATASOURCE, OPENQUERY, OPENROWSETa OPENXML)
  Aritmetický průměr (AVG)	Použití COUNT_BIG a SUM jako samostatných sloupců
  Statistické agregační funkce (STDEV,STDEVP,VARaVARP)
  SUM funkce, která odkazuje na výraz s možnou hodnotou null	Použití ISNULL uvnitř SUM() k tomu, aby byl výraz nenulový
  Další agregační funkce (MIN,MAX,CHECKSUM_AGGaSTRING_AGG)
  Uživatelem definované agregační funkce (SQL CLR)

  Klauzule SELECT	element Transact-SQL	Možná alternativa
  WITH cte AS	Běžné výrazy tabulek (CTE) WITH
  SELECT	Poddotazy
  SELECT	SELECT [ <table>. ] *	Explicitní názvy sloupců
  SELECT	SELECT DISTINCT	Použijte GROUP BY
  SELECT	SELECT TOP
  SELECT	OVER klauzule, která zahrnuje funkce řazení nebo agregace oken
  FROM	LEFT OUTER JOIN
  FROM	RIGHT OUTER JOIN
  FROM	FULL OUTER JOIN
  FROM	OUTER APPLY
  FROM	CROSS APPLY
  FROM	Odvozené tabulkové výrazy (to jest použití SELECT v klauzuli FROM)
  FROM	Samo-spojení
  FROM	Proměnné tabulky
  FROM	Vložená funkce s hodnotou tabulky
  FROM	Funkce s hodnotami tabulky s více příkazy
  FROM	PIVOT, UNPIVOT
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	Přímé dotazování na tabulku časové historie
  WHERE	Predikáty fulltextu (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
  GROUP BY	operátory CUBE, ROLLUPnebo GROUPING SETS	Definovat samostatná indexovaná zobrazení pro každou kombinaci GROUP BY sloupců
  GROUP BY	HAVING
  Nastavit operátory	UNION, UNION ALL, EXCEPT, INTERSECT	V klauzuli WHERE použijte OR, AND NOTa AND.
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  Typ zdrojového sloupce	Možná alternativa
  Zastaralé typy sloupců s velkými hodnotami (text, ntexta image)	Migrujte sloupce na varchar(max), nvarchar(max)a varbinary(max).
  sloupců xml nebo FILESTREAM
  plovoucí1 sloupců v klíči indexu
  Řídké sady sloupců

  ¹ indexované zobrazení může obsahovat plovoucí sloupce; Tyto sloupce však nelze zahrnout do clusterovaného indexového klíče.

  Důležitý

  Indexovaná zobrazení nejsou podporována nad dočasnými dotazy (dotazy, které používají klauzuli FOR SYSTEM_TIME).

Doporučení pro datetime a smalldatetime

Pokud odkazujete na datetime a řetězcové literály řetězcové literály v indexovaných zobrazeních, doporučujeme explicitně převést literál na požadovaný typ data pomocí stylu deterministického formátu data. Seznam stylů formátu kalendářních dat, které jsou deterministické, naleznete v tématu CAST a CONVERT. Další informace o deterministických a nedeterministických výrazech najdete v části Důležité informace na této stránce.

Výrazy, které zahrnují implicitní převod řetězců znaků na datetime nebo smalldatetime jsou považovány za nedeterministické. Další informace naleznete v tématu Nedeterministický převod doslovných řetězců data na hodnoty DATE.

Výkonové aspekty indexovaných zobrazení

Při spuštění DML (například UPDATE, DELETE nebo INSERT) na tabulce, kterou odkazuje velký počet indexovaných zobrazení nebo méně, ale složitějších indexovaných zobrazení, je potřeba aktualizovat i tato indexovaná zobrazení během provádění DML. Výsledkem je, že výkon dotazů DML může výrazně klesnout, nebo v některých případech nelze ani vytvořit plán dotazu. V takových scénářích otestujte dotazy DML před použitím v produkčním prostředí, analyzujte plán dotazu a vylaďte nebo zjednodušte příkaz DML.

Pokud chcete zabránit databázovému stroji v použití indexovaných zobrazení, použijte pokyn OPTION (EXPAND VIEWS) v dotazu. Pokud je některá z uvedených možností nesprávně nastavená, zabrání optimalizátoru v používání indexů v zobrazeních. Další informace o pokynu OPTION (EXPAND VIEWS) naleznete v části SELECT.

Další důležité informace

• Nastavení možnosti large_value_types_out_of_row sloupců v indexované zobrazení se dědí z nastavení odpovídajícího sloupce v základní tabulce. Tato hodnota je nastavena pomocí sp_tableoption. Výchozí nastavení sloupců vytvořených z výrazů je 0. To znamená, že velké typy hodnot jsou uloženy v řádku.

• Indexovaná zobrazení lze vytvořit na particionované tabulce a mohou být samy o sobě particionované.

• Při zrušení zobrazení se odstraní všechny indexy v zobrazení. Všechny neclusterované indexy a automaticky vytvořené statistiky v zobrazení se při vyřazení clusterovaného indexu zahodí. Statistiky vytvořené uživatelem v zobrazení jsou zachovány. Neclusterované indexy je možné jednotlivě vynechat. Odstranění clusterovaného indexu z pohledu odebere uloženou sadu výsledků, a optimalizátor se vrátí ke zpracování pohledu jako standardního pohledu.

• Indexy tabulek a zobrazení je možné zakázat. Pokud je clusterovaný index v tabulce zakázaný, indexy v zobrazeních přidružených k tabulce jsou také zakázány.

Dovolení

K vytvoření zobrazení musí uživatel uchovávat oprávnění CREATE VIEW v databázi a ALTER oprávnění ke schématu, ve kterém se zobrazení vytváří. Pokud se základní tabulka nachází v jiném schématu, vyžaduje se minimálně oprávnění REFERENCES k tabulce. Pokud se uživatel, který vytváří index, liší od uživatelů, kteří zobrazení vytvořili, k samotnému vytvoření indexu je vyžadováno oprávnění ALTER k zobrazení (zahrnuje oprávnění ALTER na schématech).

Indexy lze vytvořit pouze v zobrazeních, která mají stejného vlastníka jako odkazovaná tabulka nebo tabulky. Tento koncept se také nazývá nedotčený řetěz vlastnictví mezi zobrazením a tabulkami. Obvykle platí, že pokud se tabulka a zobrazení nacházejí ve stejném schématu, platí stejný vlastník schématu pro všechny objekty v rámci schématu. Proto je možné vytvořit zobrazení a ne být vlastníkem zobrazení. Na druhou stranu je také možné, že jednotlivé objekty v rámci schématu mají různé explicitní vlastníky. Sloupec principal_id v sys.tables obsahuje hodnotu, pokud se vlastník liší od vlastníka schématu.

Vytvoření indexovaného zobrazení: příklad T-SQL

Následující příklad vytvoří zobrazení a index v tomto zobrazení v databázi 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

Další dva dotazy ukazují, jak se dá indexované zobrazení použít, i když zobrazení není zadané v klauzuli FROM.

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

Nakonec tento příklad ukazuje dotazování přímo z indexovaného zobrazení. Automatické použití indexovaného zobrazení optimalizátorem dotazů je podporováno pouze v konkrétních edicích SQL Serveru. V edici SQL Server Standard musíte k přímému dotazování indexovaného zobrazení použít nápovědu k dotazu NOEXPAND. Azure SQL Database a Azure SQL Managed Instance podporují automatické využívání indexovaných zobrazení bez nutnosti zadat NOEXPAND hint. Další informace naleznete v tématu Nápovědy k tabulce (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;

Další informace naleznete v tématu CREATE VIEW.

Související obsah

• VYTVOŘTE INDEX (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• ANSI_PADDING SET (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• NASTAVIT CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)