Criar exibições indexadas

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo descreve como criar índices em uma exibição. O primeiro índice criado em uma exibição deve ser um índice clusterizado exclusivo. Depois que o índice clusterizado exclusivo for criado, você poderá criar mais índices não clusterizados. Criar um índice clusterizado exclusivo em uma exibição melhora o desempenho da consulta porque a exibição é armazenada no banco de dados da mesma forma que uma tabela com um índice clusterizado é armazenada. O otimizador de consulta pode usar exibições indexadas para acelerar a execução da consulta. A exibição não precisa ser referenciada na consulta para que o otimizador a considere para uma substituição.

Etapas

As seguintes etapas são necessárias para criar uma exibição indexada e são essenciais para o êxito da implementação da exibição indexada:

  1. Verifique se as opções de SET estão corretas para todas as tabelas existentes que serão referenciadas na exibição.
  2. Verifique se as opções SET da sessão estão definidas corretamente antes de criar qualquer tabela nova e a exibição.
  3. Verifique se a definição de exibição é determinística.
  4. Verifique se a tabela base tem o mesmo proprietário que a exibição.
  5. Crie a exibição usando a opção WITH SCHEMABINDING.
  6. Crie o índice clusterizado exclusivo na exibição.

Ao executar operações UPDATE, DELETE ou INSERT (DML, ou linguagem de manipulação de dados) em uma tabela referenciada por um número grande de exibições indexadas ou com menos exibições indexadas, mas complexas, essas exibições indexadas referenciadas também precisam ser atualizadas. Como resultado, o desempenho da consulta DML poderá diminuir significativamente ou, em alguns casos, um plano de consulta poderá nem mesmo ser produzido.

Nesses cenários, teste suas consultas DML antes do uso em produção, analise o plano de consulta e ajuste/simplifique a instrução DML.

Opções SET necessárias para exibições indexadas

A avaliação da mesma expressão pode produzir resultados diferentes no Mecanismo de Banco de Dados quando diferentes opções SET estão ativas no momento em que a consulta é executada. Por exemplo, depois que a opção SET CONCAT_NULL_YIELDS_NULL for definida como ON, a expressão 'abc' + NULL retornará o valor NULL. Entretanto, depois que CONCAT_NULL_YIELDS_NULL for definida como OFF, a mesma expressão produzirá abc.

Para verificar se as exibições podem ser mantidas corretamente e retornar resultados consistentes, as exibições indexadas requerem valores fixos para várias opções SET. As opções SET da seguinte tabela devem ser definidas com os valores mostrados na coluna Required value sempre que ocorrerem as seguintes condições:

  • A exibição e os índices subsequentes são criados na exibição.
  • As tabelas base referenciadas na exibição quando a exibição é criada.
  • Quando qualquer operação de inserção, atualização ou exclusão é executada em qualquer tabela que participe da exibição indexada. Esse requisito inclui operações como cópia em massa, replicação e consultas distribuídas.
  • A exibição indexada for usada pelo otimizador de consulta para produzir o plano de consulta.
Opções Set Valor obrigatório Valor do servidor padrão Padrão
Valor OLE DB e ODBC		 Padrão
Valor da 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

1 Configurar ANSI_WARNINGS como ON define implicitamente ARITHABORT como ON.

Quando você usa uma conexão de servidor OLE DB ou ODBC, o único valor que deve ser modificado é a configuração ARITHABORT. Todos os valores da DB-Library devem ser definidos corretamente no nível do servidor usando sp_configure ou no aplicativo usando o comando SET.

Importante

É altamente recomendável definir a opção de usuário ARITHABORT como ON em todo o servidor assim que a primeira exibição indexada ou índice em uma coluna computada for criado em qualquer banco de dados no servidor.

Requisito de exibição determinística

A definição de uma exibição indexada deve ser determinística. Uma exibição é determinística quando todas as expressões na lista selecionada, bem como as cláusulas WHERE e GROUP BY, são determinísticas. Expressões determinísticas sempre retornam o mesmo resultado ao serem avaliadas com um conjunto específico de valores de entrada. Somente as funções determinísticas podem participar de expressões determinísticas. Por exemplo, a função DATEADD é determinística porque sempre retorna o mesmo resultado para qualquer conjunto específico de valores de argumento para seus três parâmetros. GETDATE não é determinística, pois é sempre invocada com o mesmo argumento. Porém, o valor que ela retorna muda a cada execução.

Para determinar se uma coluna de exibição é determinística, use a propriedade IsDeterministic da função COLUMNPROPERTY. Para determinar se uma coluna determinística em uma exibição com associação de esquema é precisa, use a propriedade IsPrecise da função COLUMNPROPERTY. COLUMNPROPERTY retorna 1 se TRUE, 0 se FALSE e NULL para entradas que não são válidas. Isso significa que a coluna não é determinística ou não é precisa.

Mesmo que uma expressão seja determinística, se ela contiver expressões flutuantes, o resultado exato depende da arquitetura do processador ou da versão de microcódigo. Para assegurar a integridade dos dados, tais expressões podem participar somente como colunas não chave de exibições indexadas. Expressões determinísticas que não contêm expressões de flutuação são chamadas de precisas. Somente as expressões determinísticas precisas podem participar de colunas de chave e de cláusulas WHERE ou GROUP BY de exibições indexadas.

Requisitos adicionais

Os seguintes requisitos também devem ser atendidos, além das opções de SET e dos requisitos de funções determinísticas

  • O usuário que executa CREATE INDEX deve ser o proprietário da exibição.

  • Quando você cria o índice, a opção de índice IGNORE_DUP_KEY deve ser definida como OFF (a configuração padrão).

  • As tabelas devem ser referenciadas por meio de nomes de duas partes, <schema>.<tablename> na definição da exibição.

  • Funções definidas pelo usuário referenciadas na exibição devem ser criadas usando a opção WITH SCHEMABINDING.

  • Qualquer função definida pelo usuário referenciada na exibição precisa ser referenciada por nomes de duas partes, <schema>.<function>.

  • A propriedade de acesso a dados de uma função definida pelo usuário deve ser NO SQL e a propriedade de acesso externa deve ser NO.

  • Funções CLR (Common Language Runtime) podem aparecer na lista de seleção da exibição, mas não podem fazer parte da definição da chave do índice clusterizado. Funções CLR não podem aparecer na cláusula WHERE da exibição ou na cláusula ON de uma operação JOIN na exibição.

  • Funções CLR e métodos de tipos CLR definidos pelo usuário usados na definição da exibição devem ter as propriedades definidas como mostradas na tabela a seguir.

    Propriedade Observação
    DETERMINISTIC = TRUE Deve ser declarado explicitamente como um atributo do método do Microsoft .NET Framework.
    PRECISE = TRUE Deve ser declarado explicitamente como um atributo do método do .NET Framework.
    DATA ACCESS = NO SQL Determinado pela configuração do atributo DataAccess como DataAccessKind.None e do atributo SystemDataAccess como SystemDataAccessKind.None.
    EXTERNAL ACCESS = NO Essa propriedade padroniza como NO rotinas CLR.

  • A exibição deve ser criada usando a opção WITH SCHEMABINDING.

  • A exibição deve referenciar apenas as tabelas base que estão no mesmo banco de dados que a exibição. A exibição não pode fazer referência a outras exibições.

  • Se GROUP BY estiver presente, a definição VIEW deverá conter COUNT_BIG(*) e não deverá conter HAVING. Essas restrições GROUP BY são aplicáveis apenas à definição de exibição indexada. Uma consulta pode usar uma exibição indexada em seu plano de execução, mesmo que ela não satisfaça essas restrições de GROUP BY.

  • Se a definição de exibição contiver uma cláusula GROUP BY, a chave do índice clusterizado exclusivo poderá referenciar somente as colunas especificadas na cláusula GROUP BY.

  • A instrução SELECT na definição da exibição não deve conter a seguinte sintaxe Transact-SQL:

    Funções Transact-SQL Alternativas possíveis
    COUNT Use COUNT_BIG.
    Funções ROWSET (OPENDATASOURCE, OPENQUERY, OPENROWSET e OPENXML)
    Média aritmética (AVG) Usar COUNT_BIG e SUM como colunas separadas
    Funções de agregação estatística (STDEV,STDEVP,VAR e VARP)
    Função SUM que referencia uma expressão que permite valor nulo Usar ISNULL dentro de SUM() para tornar a expressão não anulável
    Outras funções agregadas (MIN,MAX,CHECKSUM_AGG e STRING_AGG)
    Funções agregadas definidas pelo usuário (SQL CLR)
    Cláusula SELECT Elemento Transact-SQL Alternativa possível
    WITH cte AS Expressões de tabelas comuns (CTEs) WITH
    SELECT Subconsultas
    SELECT SELECT [ <table>. ] * Nomear colunas explicitamente
    SELECT SELECT DISTINCT Use GROUP BY.
    SELECT SELECT TOP
    SELECT Cláusula OVER, que inclui funções de classificação ou de janela de agregação
    FROM LEFT OUTER JOIN
    FROM RIGHT OUTER JOIN
    FROM FULL OUTER JOIN
    FROM OUTER APPLY
    FROM CROSS APPLY
    FROM Expressões de tabelas derivadas (ou seja, usando SELECT na cláusula FROM)
    FROM Autojunções
    FROM Variáveis de tabela
    FROM Funções com valor de tabela em linha
    FROM Função com valor de tabela de várias declarações
    FROM PIVOT, UNPIVOT
    FROM TABLESAMPLE
    FROM FOR SYSTEM_TIME Consultar a tabela de histórico temporal diretamente
    WHERE Predicados de texto completo (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
    GROUP BY Operadores CUBE, ROLLUP ou GROUPING SETS Definir exibições indexadas separadas para cada combinação de colunas GROUP BY
    GROUP BY HAVING
    Operador de conjunto UNION, UNION ALL, EXCEPT, INTERSECT Usar OR, AND NOT e AND na cláusula WHERE respectivamente
    ORDER BY ORDER BY
    ORDER BY OFFSET
    Tipo de coluna de origem Alternativa possível
    Tipos de colunas de valores grandes preteridos (text, ntext e image) Migrar colunas para varchar(max), nvarchar(max) e varbinary(max), respectivamente.
    Colunas xml ou FILESTREAM
    colunas float 1 na chave de índice
    Conjuntos de colunas esparsas

    1 A exibição indexada pode conter colunas float; no entanto, essas colunas não podem ser incluídas na chave do índice clusterizado.

    Importante

    Não há compatibilidade das exibições indexadas em consultas temporais (consultas que usam a cláusula FOR SYSTEM_TIME).

Recomendações de datetime e smalldatetime

Quando você referencia os literais de cadeia de caracteres datetime e smalldatetime em exibições indexadas, é recomendável converter explicitamente o literal para o tipo de data desejado, usando um estilo de formato de data determinístico. Para obter uma lista de estilos de formato de data determinísticos, veja CAST e CONVERT. Para obter mais informações sobre expressões determinísticas e não determinísticas, consulte a seção Considerações nesta página.

Expressões que envolvem a conversão implícita de cadeias de caracteres para datetime or smalldatetime são consideradas não determinísticas. Para obter mais informações, confira Conversão não determinística de cadeias de caracteres de data literal em valores de DATA.

Considerações de desempenho com exibições indexadas

Quando você executa a DML (como UPDATE, DELETE ou INSERT) em uma tabela referenciada por um número grande de exibições indexadas ou por um número menor de exibições indexadas, mas complexas, essas exibições indexadas também devem ser atualizadas durante a execução da DML. Como resultado, o desempenho da consulta DML poderá diminuir significativamente ou, em alguns casos, um plano de consulta poderá nem mesmo ser produzido. Nesses cenários, teste suas consultas DML antes do uso em produção, analise o plano de consulta e ajuste/simplifique a instrução DML.

Para impedir que o use exibições indexadas, inclua a dica OPTION (EXPAND VIEWS) na consulta. Além disso, se qualquer uma das opções listadas for definida incorretamente, essa opção evitará que o otimizador use os índices nas exibições. Para obter mais informações sobre a dica OPTION (EXPAND VIEWS), confira SELECT.

Considerações adicionais

  • A configuração da opção large_value_types_out_of_row de colunas em uma exibição indexada é herdada da configuração da coluna correspondente na tabela base. Esse valor é definido usando sp_tableoption. A configuração padrão para colunas formadas de expressões é 0. Isso significa que tipos de valor grandes são armazenados na linha.

  • As exibições indexadas podem ser criadas em uma tabela particionada e elas próprias podem ser particionadas.

  • Todos os índices em uma exibição são descartados quando a exibição é descartada. Todos os índices não clusterizados e estatísticas criadas automaticamente na exibição são descartados quando o índice clusterizado é descartado. As estatísticas criadas pelo usuário na exibição são mantidas. Os índices não clusterizados podem ser descartados individualmente. Descartar o índice clusterizado na exibição remove o conjunto de resultados armazenado e o otimizador retorna para processar a exibição como se fosse uma exibição padrão.

  • Índices em tabelas e exibições podem ser desabilitados. Quando um índice clusterizado em uma tabela for desabilitado, os índices em exibições associadas à tabela também serão desabilitados.

Permissões

Para criar a exibição, um usuário precisa ter a permissão CREATE VIEW no banco de dados e a permissão ALTER no esquema no qual a exibição está sendo criada. Se a tabela base reside em um esquema diferente, a permissão REFERENCES na tabela é a mínima necessária. Se o usuário que estiver criando o índice for diferente dos usuários que criaram a exibição, a permissão ALTER será necessária somente para a criação do índice na exibição (coberta por ALTER no esquema).

Índices só podem ser criados em exibições que tenham o mesmo proprietário que as tabelas referenciadas. Esse conceito também é chamado de cadeia de propriedade intacta entre a exibição e as tabelas. Normalmente, quando a tabela e a exibição residem no mesmo esquema, o mesmo proprietário de esquema se aplica a todos os objetos no esquema. Portanto, é possível criar uma exibição e não ser o proprietário dela. Por outro lado, também é possível que objetos individuais dentro de um esquema tenham proprietários explícitos diferentes. A coluna principal_id em sys.tables contém um valor quando o proprietário é diferente do proprietário do esquema.

Criar uma exibição indexada: um exemplo T-SQL

O exemplo a seguir cria uma exibição e um índice nessa exibição, no banco de dados 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

As duas consultas a seguir demonstram como a exibição indexada pode ser usada, mesmo que ela não esteja especificada na cláusula 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;

Por fim, esse exemplo mostra a consulta diretamente da exibição indexada. Antes do SQL Server 2016 (13.x) Service Pack 1, o uso automático de uma exibição indexada pelo otimizador de consulta era compatível apenas em edições específicas do SQL Server. No SQL Server Standard edition, você deve usar a dica de consulta NOEXPAND para consultar a exibição indexada diretamente. Desde o SQL Server 2016 (13.x) Service Pack 1, todas as edições oferecem suporte ao uso automático de uma exibição indexada. O Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure também oferecem suporte ao uso automático de exibições indexadas sem especificar a dica NOEXPAND. Para obter mais informações, confira Dicas de tabela (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;

Para obter mais informações, confira CREATE VIEW.

Conteúdo relacionado