Criar exibições indexadas

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstâ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.

Importante

Ao executar operações UPDATE, DELETE ou INSERT (DML, ou linguagem de manipulação de dados) em uma tabela referenciada por um grande número de exibições indexadas ou com menos exibições indexadas, mas muito complexas, essas exibições indexadas referenciadas também terão que 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 Valor Obrigatório 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.
  • Houver 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 ATIVADO ATIVADO ATIVADO OFF
ANSI_PADDING ATIVADO ATIVADO ATIVADO OFF
ANSI_WARNINGS 1 ATIVADO ATIVADO ATIVADO OFF
ARITHABORT ATIVADO ATIVADO OFF OFF
CONCAT_NULL_YIELDS_NULL ATIVADO ATIVADO ATIVADO OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ATIVADO ATIVADO ATIVADO OFF

1 Configurar ANSI_WARNINGS como ON define implicitamente ARITHABORT como ON.

Se você estiver usando uma conexão de servidor OLE DB ou ODBC, o único valor que deverá ser modificado será 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 será determinística se todas as expressões na lista selecionada, bem como as cláusulas WHERE e GROUP BY, forem 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 poderá depender 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 deve 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.

    Property 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 Usar o 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 Usar o 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 obsoletos text, ntext e image Migrar colunas para varchar(max), nvarchar(max) e varbinary(max), respectivamente.
    Colunas xml ou FILESTREAM
    colunas float1 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

    As exibições indexadas não são compatíveis sobre 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, consulte CAST e CONVERT (Transact-SQL). 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 em datetime ou 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 deverão 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 evitar que o Mecanismo de Banco de Dados 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, isso evitará que o otimizador use os índices nas exibições. Para obter mais informações sobre a dica OPTION (EXPAND VIEWS), consulte SELECT (Transact-SQL).

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. Isso 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
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
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, veja CREATE VIEW (Transact-SQL).

Próximas etapas