Tworzenie widoków indeksowanych

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric

W tym artykule opisano sposób tworzenia indeksów w widoku. Pierwszy indeks utworzony w widoku musi być unikatowym indeksem klastrowanym. Po utworzeniu unikatowego indeksu klastrowanego można utworzyć więcej indeksów nieklastrowanych. Tworzenie unikatowego indeksu klastrowanego w widoku zwiększa wydajność zapytań, ponieważ widok jest przechowywany w bazie danych w taki sam sposób, jak tabela z indeksem klastrowanym. Optymalizator zapytań może używać widoków indeksowanych, aby przyspieszyć wykonywanie zapytania. Widok nie musi być przywoływany w zapytaniu, aby optymalizator rozważył jego zastosowanie jako substytucji.

Kroki

Następujące kroki są wymagane do utworzenia widoku indeksowanego i mają kluczowe znaczenie dla pomyślnej implementacji widoku indeksowanego:

1. Sprawdź opcje SET, czy są poprawne dla wszystkich istniejących tabel, do których będą odnosić się w widoku.
2. Przed utworzeniem tabel i widoku sprawdź, czy SET opcje sesji są ustawione poprawnie.
3. Sprawdź, czy definicja widoku jest deterministyczna.
4. Sprawdź, czy tabela podstawowa ma tego samego właściciela co widok.
5. Utwórz widok przy użyciu WITH SCHEMABINDING opcji .
6. Utwórz unikatowy indeks klastrowany w widoku.

Wykonując operacje UPDATE, DELETE lub INSERT (języka manipulowania danymi, czyli DML) na tabeli, do której odwołuje się wiele indeksowanych widoków, lub kilka złożonych widoków indeksowanych, również te widoki muszą zostać zaktualizowane. W związku z tym wydajność zapytań DML może się znacznie pogorszyć, a czasami nawet nie da się wygenerować planu zapytania.

W takich scenariuszach przetestuj zapytania DML przed użyciem produkcyjnym, przeanalizuj plan zapytania i dostosuj/uprość instrukcję DML.

Wymagane opcje SET dla widoków zindeksowanych

Ocenianie tego samego wyrażenia może generować różne wyniki w aparacie bazy danych, gdy różne opcje SET są aktywne podczas wykonywania zapytania. Na przykład, po ustawieniu opcji SET na CONCAT_NULL_YIELDS_NULL, wyrażenie ON zwraca wartość 'abc' + NULL. Jednak po ustawieniu CONCAT_NULL_YIELDS_NULL na OFF, to samo wyrażenie generuje abc.

Aby upewnić się, że widoki mogą być prawidłowo obsługiwane i zwracać spójne wyniki, indeksowane widoki wymagają stałych wartości dla kilku SET opcji. Opcje SET w poniższej tabeli muszą być ustawione na wartości wyświetlane w Required value kolumnie zawsze, gdy wystąpią następujące warunki:

• Utworzony zostanie widok oraz kolejne indeksy dla widoku.
• Tabele podstawowe, do których odwołuje się widok w momencie utworzenia widoku.
• Gdy każda operacja wstawiania, aktualizowania lub usuwania jest wykonywana na dowolnej tabeli, która uczestniczy w widoku indeksowanym. To wymaganie obejmuje operacje, takie jak kopiowanie zbiorcze, replikacja i zapytania rozproszone.
• Widok indeksowany jest używany przez optymalizator zapytań do tworzenia planu zapytania.

USTAW opcje	Wymagana wartość	Wartość domyślna serwera	Wartość domyślna Wartość OLE DB i ODBC	Wartość domyślna DB-Library wartość
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

¹ Ustawienie ANSI_WARNINGS na ON niejawnie ustawia ARITHABORT na ON.

Jeśli używasz połączenia serwera OLE DB lub ODBC, jedyną wartością, którą należy zmodyfikować, jest ustawienie ARITHABORT. Wszystkie wartości DB-Library muszą być poprawnie ustawione na poziomie serwera przy użyciu polecenia sp_configure lub przez aplikację przy użyciu komendy SET.

Ważne

Zdecydowanie zalecamy ustawienie ARITHABORT opcji użytkownika na ON cały serwer zaraz po utworzeniu pierwszego indeksowanego widoku lub indeksu w obliczonej kolumnie w dowolnej bazie danych na serwerze.

Wymaganie widoku deterministycznego

Definicja indeksowanego widoku musi być deterministyczna. Widok jest deterministyczny, jeśli wszystkie wyrażenia na liście wyboru oraz klauzule WHERE i GROUP BY są deterministyczne. Wyrażenia deterministyczne zawsze zwracają ten sam wynik za każdym razem, gdy są obliczane przy użyciu określonego zestawu wartości wejściowych. Tylko funkcje deterministyczne mogą uczestniczyć w wyrażeniach deterministycznych. Na przykład funkcja jest deterministyczna, DATEADD ponieważ zawsze zwraca ten sam wynik dla dowolnego zestawu wartości argumentów dla jego trzech parametrów. GETDATE nie jest deterministyczny, ponieważ zawsze jest wywoływany z tym samym argumentem, ale wartość, którą zwraca, zmienia się za każdym razem, gdy jest wykonywany.

Aby określić, czy kolumna widoku jest deterministyczna, użyj IsDeterministic właściwości funkcji COLUMNPROPERTY . Aby określić, czy kolumna deterministyczna w widoku z powiązaniem schematu jest dokładna, użyj IsPrecise właściwości COLUMNPROPERTY funkcji. COLUMNPROPERTY Zwraca 1 jeśli TRUE, 0 jeśli FALSE, i NULL dla nieprawidłowych danych wejściowych. Oznacza to, że kolumna nie jest deterministyczna lub nie jest dokładna.

Nawet jeśli wyrażenie jest deterministyczne, jeśli zawiera wyrażenia zmiennoprzecinkowe, dokładny wynik zależy od architektury procesora lub wersji mikrokodu. Aby zapewnić integralność danych, takie wyrażenia mogą być używane wyłącznie jako kolumny niekluczowe w widokach indeksowanych. Wyrażenia deterministyczne, które nie zawierają wyrażeń zmiennoprzecinkowych, są nazywane precyzyjnymi wyrażeniami. Tylko precyzyjne wyrażenia deterministyczne mogą uczestniczyć w kluczowych kolumnach oraz klauzulach WHERE lub GROUP BY widoków indeksowanych.

Wymagania dodatkowe

Oprócz opcji i wymagań funkcji deterministycznych należy również spełnić SET następujące wymagania

• Użytkownik wykonujący CREATE INDEX musi być właścicielem widoku.

• Podczas tworzenia indeksu należy ustawić opcję indeksu IGNORE_DUP_KEY na OFF (ustawienie domyślne).

• Tabele muszą być przywołyane przez nazwy dwuczęściowe , <schema>.<tablename>w definicji widoku.

• Funkcje zdefiniowane przez użytkownika, do których odwołuje się widok, muszą być tworzone przy użyciu WITH SCHEMABINDING opcji .

• Wszystkie funkcje zdefiniowane przez użytkownika, do których odwołuje się widok, muszą odwoływać się do nazw dwuczęściowych: <schema>.<function>.

• Właściwość dostępu do danych funkcji zdefiniowanej przez użytkownika musi być NO SQL, a właściwość dostępu zewnętrznego musi być NO.

• Funkcje środowiska uruchomieniowego języka wspólnego (CLR) mogą być wyświetlane na liście wyboru widoku, ale nie mogą być częścią definicji klucza indeksu klastrowanego. Funkcje CLR nie mogą pojawić się w klauzuli WHERE widoku ani w klauzuli ON operacji JOIN w widoku.

• Funkcje CLR i metody typów zdefiniowanych przez użytkownika środowiska CLR używane w definicji widoku muszą mieć ustawione właściwości, jak pokazano w poniższej tabeli.

  Majątek	Uwaga / Notatka
  DETERMINISTYCZNY = PRAWDA	Należy jawnie zadeklarować jako atrybut metody programu Microsoft .NET Framework.
  PRECYZYJNE = PRAWDA	Należy jawnie zadeklarować jako atrybut metody .NET Framework.
  DOSTĘP DO DANYCH = BEZ SQL	Określana przez ustawienie atrybutu DataAccess na DataAccessKind.None i SystemDataAccess atrybutu na SystemDataAccessKind.None.
  DOSTĘP ZEWNĘTRZNY = NIE	Ta właściwość domyślnie ma wartość NIE dla procedur CLR.

• Widok musi zostać utworzony przy użyciu WITH SCHEMABINDING opcji .

• Widok musi odwoływać się tylko do tabel bazowych, które znajdują się w tej samej bazie danych co widok. Widok nie może odwoływać się do innych widoków.

• Jeśli GROUP BY jest obecny, definicja VIEW musi zawierać COUNT_BIG(*) i nie może zawierać HAVING. Te GROUP BY ograniczenia mają zastosowanie tylko do definicji widoku indeksowanego. Zapytanie może używać widoku indeksowanego w planie wykonywania, nawet jeśli nie spełnia tych GROUP BY ograniczeń.

• Jeśli definicja widoku zawiera klauzulę GROUP BY , klucz unikatowego indeksu klastrowanego może odwoływać się tylko do kolumn określonych w klauzuli GROUP BY .

• Instrukcja SELECT w definicji widoku nie może zawierać następującej składni Transact-SQL:

  Transact-SQL, funkcja	Możliwe alternatywy
  COUNT	Użyj COUNT_BIG
  ROWSET funkcje (OPENDATASOURCE, OPENQUERY, OPENROWSETi OPENXML)
  Średnia arytmetyczna (AVG)	Używanie COUNT_BIG i SUM jako oddzielne kolumny
  Funkcje agregujące statystyczne (STDEV,STDEVPVAR, i VARP)
  SUM funkcja, która odwołuje się do wyrażenia mogącego przyjąć wartość null	Użyj ISNULL wewnątrz SUM(), aby wyrażenie było nienullowalne
  Inne funkcje agregujące (MIN,MAX,CHECKSUM_AGG i STRING_AGG)
  Funkcje agregujące zdefiniowane przez użytkownika (SQL CLR)

  Klauzula SELECT	element Transact-SQL	Możliwa alternatywa
  WITH cte AS	Typowe wyrażenia tabeli (CTE) WITH
  SELECT	Podzapytania
  SELECT	SELECT [ <table>. ] *	Jawne nadawanie nazw kolumnom
  SELECT	SELECT DISTINCT	Użyj GROUP BY
  SELECT	SELECT TOP
  SELECT	OVER klauzula zawierająca funkcje okna klasyfikacji lub agregacji
  FROM	LEFT OUTER JOIN
  FROM	RIGHT OUTER JOIN
  FROM	FULL OUTER JOIN
  FROM	OUTER APPLY
  FROM	CROSS APPLY
  FROM	Wyrażenia tabeli pochodnej (przy użyciu SELECT w klauzuli FROM)
  FROM	Samosprzężenia
  FROM	Zmienne tabeli
  FROM	Funkcja zwracająca tabelę w linii
  FROM	Funkcja tabelaryczna z wieloma instrukcjami
  FROM	PIVOT, UNPIVOT
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	Wykonywanie zapytań bezpośrednio w tabeli historii czasowej
  WHERE	Predykaty pełnotekstowe (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
  GROUP BY	CUBE, ROLLUP, lub GROUPING SETS operatory	Definiowanie oddzielnych widoków indeksowanych dla każdej kombinacji GROUP BY kolumn
  GROUP BY	HAVING
  Ustawianie operatorów	UNION, , UNION ALL, , EXCEPTINTERSECT	Użyj OR, AND NOT i AND w klauzuli WHERE odpowiednio
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  Typ kolumny źródłowej	Możliwa alternatywa
  Przestarzałe typy kolumn dużych wartości (tekst, ntekst i obraz)	Migracja kolumn do varchar(max), nvarchar(max), oraz varbinary(max).
  kolumny XML lub FILESTREAM
  liczba kolumn zmiennoprzecinkowych1 w kluczu indeksu
  Zestawy rzadkich kolumn

  ¹ Indeksowany widok może zawierać kolumny zmiennoprzecinkowe ; nie można jednak uwzględnić takich kolumn w kluczu indeksu klastrowanego.

  Ważne

  Widoki indeksowane nie są obsługiwane w odniesieniu do zapytań czasowych (zapytania korzystające z klauzuli FOR SYSTEM_TIME).

Rekomendacje dotyczące daty/godziny i typu smalldatetime

Jeśli odwołujesz się do literałów ciągu znaków datetime i smalldatetime w widokach indeksowanych, zalecamy, abyś jawnie przekonwertował literał na preferowany typ daty, korzystając z deterministycznego stylu formatu daty. Aby uzyskać listę stylów formatu daty, które są deterministyczne, zobacz CAST i CONVERT. Aby uzyskać więcej informacji na temat wyrażeń deterministycznych i niedeterministycznych, zobacz sekcję Zagadnienia na tej stronie.

Wyrażenia, które obejmują niejawną konwersję ciągów znaków na wartość datetime lub smalldatetime, są uznawane za niedeterministyczne. Aby uzyskać więcej informacji, zobacz Niedeterministyczna konwersja ciągów dat literału na wartości DATE.

Zagadnienia dotyczące wydajności w widokach indeksowanych

W przypadku wykonywania kodu DML (takiego jak UPDATE, DELETE lub INSERT) w tabeli, do której odwołuje się duża liczba indeksowanych widoków, lub mniej, ale złożonych widoków indeksowanych, te indeksowane widoki muszą być również aktualizowane podczas wykonywania DML. W związku z tym wydajność zapytań DML może się znacznie pogorszyć, a czasami nawet nie da się wygenerować planu zapytania. W takich scenariuszach przetestuj zapytania DML przed użyciem produkcyjnym, przeanalizuj plan zapytania i dostosuj/uprość instrukcję DML.

Aby zapobiec używaniu przez silnik bazy danych indeksowanych widoków, dołącz wskazówkę OPCJA (ROZWIŃ WIDOKI) w zapytaniu. Ponadto jeśli którakolwiek z wymienionych opcji jest niepoprawnie ustawiona, ta opcja uniemożliwia optymalizatorowi korzystanie z indeksów w widokach. Aby uzyskać więcej informacji na temat OPTION (EXPAND VIEWS) wskazówki, zobacz SELECT.

Uwagi dodatkowe

• Ustawienie large_value_types_out_of_row opcji kolumn w indeksowanym widoku jest dziedziczone z ustawienia odpowiedniej kolumny w tabeli bazowej. Ta wartość jest ustawiana przy użyciu sp_tableoption. Domyślnym ustawieniem kolumn utworzonych na podstawie wyrażeń jest 0. Oznacza to, że duże typy wartości są przechowywane w wierszu.

• Widoki indeksowane można tworzyć na tabeli podzielonej na partycje i same mogą być podzielone na partycje.

• Wszystkie indeksy w widoku są usuwane, gdy widok zostanie usunięty. Wszystkie indeksy nieklastrowane i automatycznie utworzone statystyki w widoku są porzucane po usunięciu indeksu klastrowanego. Statystyki utworzone przez użytkownika w widoku są zachowywane. Indeksy nieklastrowane mogą być indywidualnie porzucane. Usunięcie indeksu klastrowanego w widoku powoduje usunięcie przechowywanego zestawu wyników, a optymalizator wraca do przetwarzania widoku jak zwykły widok.

• Indeksy w tabelach i widokach można wyłączyć. Gdy indeks klastrowany w tabeli jest wyłączony, indeksy widoków skojarzonych z tabelą również są wyłączone.

Uprawnienia

Aby utworzyć widok, użytkownik musi przechowywać CREATE VIEW uprawnienie w bazie danych i ALTER uprawnienia do schematu, w którym jest tworzony widok. Jeśli tabela podstawowa znajduje się w innym schemacie, wymagane jest minimalne uprawnienie do tabeli REFERENCES. Jeśli użytkownik tworzący indeks różni się od użytkowników, którzy utworzyli widok, tylko w przypadku tworzenia ALTER indeksu wymagane jest uprawnienie do widoku (objęte ALTER schematem).

Indeksy można tworzyć wyłącznie w widokach, które mają tego samego właściciela co tabele, do których odnoszą się. Ta koncepcja jest również nazywana nienaruszonym łańcuchem własności między widokiem a tabelami. Zazwyczaj gdy tabela i widok znajdują się w tym samym schemacie, ten sam właściciel schematu ma zastosowanie do wszystkich obiektów w schemacie. W związku z tym można utworzyć widok, a nie być właścicielem widoku. Z drugiej strony możliwe jest również, że poszczególne obiekty w schemacie mają różnych jawnych właścicieli. Kolumna principal_id w pliku sys.tables zawiera wartość, jeśli właściciel różni się od właściciela schematu.

Tworzenie indeksowanego widoku: przykład języka T-SQL

Poniższy przykład tworzy widok i indeks w tym widoku w AdventureWorks bazie danych.

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

W następnych dwóch zapytaniach pokazano, jak można użyć indeksowanego widoku, mimo że widok nie jest określony w 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;

Na koniec w tym przykładzie pokazano wykonywanie zapytań bezpośrednio z widoku indeksowanego. Automatyczne używanie widoku indeksowanego przez optymalizator zapytań jest obsługiwane tylko w określonych wersjach programu SQL Server. W wersji SQL Server Standard należy użyć NOEXPAND wskazówki dotyczącej zapytania, aby bezpośrednio wykonać zapytanie względem indeksowanego widoku. Usługi Azure SQL Database i Azure SQL Managed Instance obsługują automatyczne korzystanie z indeksowanych widoków bez określania NOEXPAND wskazówki. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące tabel (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;

Aby uzyskać więcej informacji, zobacz CREATE VIEW (TWORZENIE WIDOKU).

Treści powiązane

• STWÓRZ INDEKS (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• SET ANSI_PADDING (Transact-SQL)
• SET ANSI_WARNINGS (Transact-SQL)
• SET ARITHABORT (Transact-SQL)
• SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
• SET NUMERIC_ROUNDABORT (Transact-SQL)
• SET QUOTED_IDENTIFIER (Transact-SQL)