Criar vistas indexadas

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

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

Passos

As etapas a seguir são necessárias para criar um modo de exibição indexado e são críticas para a implementação bem-sucedida do modo de exibição indexado:

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 da sessão SET estão definidas corretamente antes de criar quaisquer tabelas e a vista.
3. Verifique se a definição de exibição é determinística.
4. Verifique se a tabela base tem o mesmo proprietário que a vista.
5. Crie a vista usando a opção WITH SCHEMABINDING.
6. Crie o índice clusterizado exclusivo na exibição.

Quando executa as operações UPDATE, DELETE ou INSERT (Linguagem de Manipulação de Dados, ou DML) numa tabela referenciada por um grande número de vistas indexadas, ou por um menor número mas de vistas indexadas mais complexas, essas vistas referidas também têm de ser atualizadas. Como resultado, o desempenho da consulta DML pode diminuir significativamente ou, em alguns casos, um plano de consulta nem sequer pode ser produzido.

Nesses cenários, teste as suas consultas DML antes do uso em produção, analise o plano de consulta e otimize/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 de SET estiverem ativas quando a consulta for executada. Por exemplo, depois que a opção SETCONCAT_NULL_YIELDS_NULL estiver definida como ON, a expressão 'abc' + NULL retornará o valor NULL. No entanto, depois que CONCAT_NULL_YIELDS_NULL é definido como OFF, a mesma expressão produz abc.

Para garantir que as exibições possam ser mantidas corretamente e retornar resultados consistentes, as exibições indexadas exigem valores fixos para várias opções SET. As opções de SET na tabela a seguir devem ser definidas para os valores mostrados na coluna Required value sempre que ocorrerem as seguintes condições:

• O modo de exibição e os índices subsequentes no modo de exibição são criados.
• As tabelas base referenciadas na vista no momento em que a vista é criada.
• Quando qualquer operação de inserção, atualização ou exclusão é executada em qualquer tabela que participa do modo de exibição indexado. Esse requisito inclui operações como cópia em massa, replicação e consultas distribuídas.
• O modo de exibição indexado é usado pelo otimizador de consulta para produzir o plano de consulta.

¹ Definir ANSI_WARNINGS para ON define implicitamente ARITHABORT para ON.

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

Requisito de visão determinística

A definição de uma vista indexada deve ser determinística. Uma visão é determinística se todas as expressões na lista de seleção e as cláusulas WHERE e GROUP BY forem determinísticas. As expressões determinísticas sempre retornam o mesmo resultado sempre que são avaliadas com um conjunto específico de valores de entrada. Somente 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 de valores de argumento para seus três parâmetros. GETDATE não é determinista porque é sempre invocado com o mesmo argumento, mas o valor que retorna muda cada vez que é executado.

Para determinar se uma coluna de visualização é determinística, use a propriedade IsDeterministic da função COLUMNPROPERTY. Para determinar se uma coluna determinística numa vista com vinculação de esquema é precisa, use a propriedade IsPrecise da função COLUMNPROPERTY. COLUMNPROPERTY retorna 1 se TRUE, 0 se FALSEe NULL para entrada que não é válida. 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 do microcódigo. Para garantir a integridade dos dados, essas expressões podem participar apenas como colunas não-chave de vistas indexadas. As expressões determinísticas que não contêm expressões flutuantes são chamadas precisas. Apenas expressões determinísticas precisas podem participar em colunas-chave e nas cláusulas WHERE ou GROUP BY das vistas indexadas.

Requisitos adicionais

Devem também ser cumpridos os seguintes requisitos, para além das opções SET e dos requisitos de função determinística

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

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

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

• As funções definidas pelo usuário referenciadas no modo de exibição devem ser criadas usando a opção WITH SCHEMABINDING.

• Todas as funções definidas pelo utilizador referenciadas na vista devem ser referenciadas por nomes de duas partes, <schema>.<function>.

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

• As 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 de índice clusterizada. As funções CLR não podem aparecer na cláusula WHERE do modo de exibição ou na cláusula ON de uma operação JOIN no modo de exibição.

• As funções e métodos CLR dos tipos definidos pelo usuário CLR usados na definição de exibição devem ter as propriedades definidas conforme mostrado na tabela a seguir.

  Propriedade	Observação
  DETERMINÍSTICA = VERDADEIRA	Deve ser declarado explicitamente como um atributo do método Microsoft .NET Framework.
  PRECISO = VERDADEIRO	Deve ser declarado explicitamente como um atributo do método .NET Framework.
  ACESSO A DADOS = SEM SQL	Determinado ao definir o atributo DataAccess como DataAccessKind.None e o atributo SystemDataAccess como SystemDataAccessKind.None.
  ACESSO EXTERNO = NÃO	O valor padrão desta propriedade é NO para rotinas CLR.

• A vista deve ser criada utilizando a opção WITH SCHEMABINDING.

• A vista deve referenciar apenas tabelas base que estão no mesmo banco de dados. A vista não pode fazer referência a outras vistas.

• Se GROUP BY estiver presente, a definição VIEW deve conter COUNT_BIG(*) e não deve conter HAVING. Essas restrições de GROUP BY são aplicáveis somente à definição de exibição indexada. Uma consulta pode usar um modo de exibição indexado em seu plano de execução, mesmo que 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á fazer referência apenas às colunas especificadas na cláusula GROUP BY.

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

  Transact-SQL função	Alternativas possíveis
  COUNT	Use COUNT_BIG
  ROWSET funções (OPENDATASOURCE, OPENQUERY, OPENROWSETe OPENXML)
  Média aritmética (AVG)	Use COUNT_BIG e SUM como colunas separadas
  Funções agregadas estatísticas (STDEV,STDEVP,VAReVARP)
  SUM função que faz referência a uma expressão anulável	Use ISNULL dentro SUM() para tornar a expressão não anulável
  Outras funções agregadas (MIN,MAX,CHECKSUM_AGGeSTRING_AGG)
  Funções de agregação definidas pelo usuário (SQL CLR)

  Cláusula SELECT	elemento Transact-SQL	Alternativa possível
  WITH cte AS	Expressões de tabela comuns (CTE) WITH
  SELECT	Subconsultas
  SELECT	SELECT [ <table>. ] *	Nomear colunas explicitamente
  SELECT	SELECT DISTINCT	Utilize o GROUP BY
  SELECT	SELECT TOP
  SELECT	OVER cláusula, que inclui funções de janela de classificação ou agregação
  FROM	LEFT OUTER JOIN
  FROM	RIGHT OUTER JOIN
  FROM	FULL OUTER JOIN
  FROM	OUTER APPLY
  FROM	CROSS APPLY
  FROM	Expressões de tabela derivadas (ou seja, usando SELECT na cláusula FROM)
  FROM	Auto-junções
  FROM	Variáveis da tabela
  FROM	Função embutida com valor de tabela
  FROM	Função multideclaração com valor de tabela
  FROM	PIVOT, UNPIVOT
  FROM	TABLESAMPLE
  FROM	FOR SYSTEM_TIME	Consultar diretamente a tabela do histórico temporal
  WHERE	Predicados de texto completo (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
  GROUP BY	operadores CUBE, ROLLUPou GROUPING SETS	Definir exibições indexadas separadas para cada combinação de GROUP BY colunas
  GROUP BY	HAVING
  Definir operadores	UNION, UNION ALL, EXCEPT, INTERSECT	Use OR, AND NOTe AND na cláusula WHERE, respectivamente
  ORDER BY	ORDER BY
  ORDER BY	OFFSET

  Tipo de coluna de origem	Alternativa possível
  Tipos de coluna de valor grande preteridos (texto, ntext , e imagem )	Migre colunas para varchar(max), nvarchar(max)e varbinary(max) respectivamente.
  colunas xml ou FILESTREAM
  flutuarcolunas de 1 na chave de índice
  Conjuntos de colunas esparsas

  ¹ A vista indexada pode conter flutuar colunas; no entanto, essas colunas não podem ser incluídas na chave de índice clusterizada.

  Importante

  Não há suporte para exibições indexadas em cima de consultas temporais (consultas que usam FOR SYSTEM_TIME cláusula).

Recomendações para datetime e smalldatetime

Quando te referes a literais datetime e literais string smalldatetime em vistas indexadas, recomendamos que convertas explicitamente o literal para o tipo de data desejado usando um estilo de formato de data determinístico. Para obter uma lista dos estilos de formato de data que são determinísticos, consulte 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.

As expressões que envolvem a conversão implícita de cadeias de caracteres para datetime ou smalldatetime são consideradas não determinísticas. Para obter mais informações, consulte Conversão não determinística de cadeias literais de data em valores DATE.

Considerações de desempenho com modos de exibição indexados

Quando se executa DML (como UPDATE, DELETE ou INSERT) numa tabela referenciada por um grande número de vistas indexadas, ou menos vistas indexadas, mas complexas, essas vistas indexadas também precisam ser atualizadas durante a execução de DML. Como resultado, o desempenho da consulta DML pode diminuir significativamente ou, em alguns casos, um plano de consulta nem sequer pode ser produzido. Nesses cenários, teste as suas consultas DML antes de serem utilizadas em produção, analise o plano de consulta e otimize ou simplifique a instrução DML.

Para impedir que o Mecanismo de Banco de Dados use exibições indexadas, inclua a OPTION (EXPAND VIEWS) dica na consulta. Além disso, se alguma das opções listadas estiver definida incorretamente, essa opção impedirá que o otimizador use os índices nas exibições. Para obter mais informações sobre a dica OPTION (EXPAND VIEWS), consulte SELECT.

Considerações adicionais

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

• As vistas indexadas podem ser criadas numa tabela particionada e podem ser particionadas.

• Todos os índices sobre uma vista são eliminados quando a vista é eliminada. 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 visualização são mantidas. Os índices não agrupados podem ser descartados individualmente. Soltar o índice clusterizado na exibição remove o conjunto de resultados armazenado e o otimizador retorna ao processamento da exibição como uma exibição padrão.

• Os índices em tabelas e vistas podem ser desativados. Quando um índice clusterizado em uma tabela é desabilitado, os índices em modos de exibição associados à tabela também são desabilitados.

Permissões

Para criar o modo de exibição, um usuário precisa manter a permissão CREATE VIEW no banco de dados e ALTER permissão no esquema no qual o modo de exibição está sendo criado. Se a tabela base reside dentro de um esquema diferente, a permissão REFERENCES na tabela é necessária como um mínimo. Se o usuário que cria o índice for diferente dos usuários que criaram o modo de exibição, apenas para a criação do índice será necessária a permissão ALTER no modo de exibição (coberto por ALTER no esquema).

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

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

O exemplo a seguir cria uma vista e um índice nessa vista, na base 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 o modo de exibição indexado pode ser usado, mesmo que o modo de exibição não esteja especificado 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;

Finalmente, este exemplo mostra como consultar diretamente a partir da vista indexada. O uso automático de um modo de exibição indexado pelo otimizador de consulta é suportado apenas em edições específicas do SQL Server. Na edição SQL Server Standard, você deve usar a dica de consulta NOEXPAND para consultar a vista indexada diretamente. O Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure oferecem suporte ao uso automático de modos de exibição indexados sem especificar a dica NOEXPAND. Para obter mais informações, consulte 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, consulte CREATE VIEW.

Conteúdo relacionado

• CRIAR ÍNDICE (Transact-SQL)
• SET ANSI_NULLS (Transact-SQL)
• DEFINIR ANSI_PADDING (Transact-SQL)
• CONJUNTO 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)