Artigo
09/28/2010

Criando exibições indexadas

Uma exibição deve atender aos seguintes requisitos para que você possa criar um índice clusterizado nela:

As opções ANSI_NULLS e QUOTED_IDENTIFIER devem ser definidas como ON quando a instrução CREATE VIEW for executada. A função OBJECTPROPERTY relata isso para exibições através das propriedades ExecIsAnsiNullsOn ou ExecIsQuotedIdentOn.
A opção ANSI_NULLS deve ser definida como ON para a execução de todas as instruções CREATE TABLE que criam tabelas referenciadas pela exibição.
A exibição não deve se referenciar a nenhuma outra exibição, somente a tabelas base.
Todas as tabelas base referenciadas pela exibição devem estar no mesmo banco de dados que a exibição e devem ter o mesmo proprietário que ela.
A exibição deve ser criada com a opção SCHEMABINDING. A associação de esquema associa a exibição ao esquema de tabelas base subjacentes.
Funções definidas pelo usuário referenciadas na exibição devem ser criadas com a opção SCHEMABINDING.
Tabelas e funções definidas pelo usuário devem ser referenciadas através de nomes de duas partes na exibição. Não são permitidos nomes de uma, três ou quatro partes.

Todas as funções referenciadas por expressões na exibição devem ser determinísticas. A propriedade IsDeterministic da função OBJECTPROPERTY informa se uma função definida pelo usuário é determinística. Para obter mais informações, consulte Funções determinísticas e não-determinísticas.

Observação
Quando você se referir aos literais de cadeia de caracteres datetime e smalldatetime em exibições indexadas no SQL Server 2008, recomendamos que você converta 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). Expressões que envolvem conversão implícita de cadeias de caracteres para datetime ou smalldatetime são consideradas não determinísticas, a menos que o nível de compatibilidade seja definido como 80 ou abaixo disso. Isso porque os resultados dependem das configurações de LANGUAGE e DATEFORMAT da sessão de servidor. Por exemplo, os resultados da expressão CONVERT (datetime, '30 listopad 1996', 113) dependem da configuração LANGUAGE porque a cadeia de caracteres 'listopad' significa meses diferentes em idiomas diferentes. Semelhantemente, na expressão DATEADD(mm,3,'2000-12-01'), SQL Server interpreta a cadeia de caracteres '2000-12-01' com base na configuração DATEFORMAT.

Quando você se referir aos literais de cadeia de caracteres datetime e smalldatetime em exibições indexadas no SQL Server 2008, recomendamos que você converta 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). Expressões que envolvem conversão implícita de cadeias de caracteres para datetime ou smalldatetime são consideradas não determinísticas, a menos que o nível de compatibilidade seja definido como 80 ou abaixo disso. Isso porque os resultados dependem das configurações de LANGUAGE e DATEFORMAT da sessão de servidor. Por exemplo, os resultados da expressão CONVERT (datetime, '30 listopad 1996', 113) dependem da configuração LANGUAGE porque a cadeia de caracteres 'listopad' significa meses diferentes em idiomas diferentes. Semelhantemente, na expressão DATEADD(mm,3,'2000-12-01'), SQL Server interpreta a cadeia de caracteres '2000-12-01' com base na configuração DATEFORMAT.

A conversão implícita de dados de caracteres não Unicode entre agrupamentos também é considerada não determinística, a menos que o nível de compatibilidade seja definido como 80 ou abaixo disso.

Criar índices em exibições que contêm essas expressões não é permitido no modo de compatibilidade 90. Entretanto, as exibições existentes que contêm essas expressões de um banco de dados atualizado são sustentáveis. Se você usar exibições indexadas que contêm conversões implícitas de cadeia de caracteres para datas; certifique-se de que as configurações LANGUAGE e DATEFORMAT estão consistentes em seus bancos de dados e aplicativos para evitar uma possível corrupção de exibição indexada.

Se a definição de exibição usar uma função de agregação, a lista SELECT também deverá incluir COUNT_BIG (*).
A propriedade de acesso de 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 ser parte da definição de uma chave de í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 de dados 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 Microsoft.NET Framework
PRECISE = TRUE	Deve ser declarado explicitamente como um atributo do método .NET Framework.
DATA ACCESS = NO SQL	Determinado pela definição do atributo DataAccess para DataAccessKind.None e do atributo SystemDataAccess para SystemDataAccessKind.None.
EXTERNAL ACCESS = NO	Essa propriedade padroniza como NO rotinas CLR.

Para obter mais informações sobre como definir atributos de métodos de rotina CLR, consulte Atributos personalizados para rotinas de CLR.

Cuidado
Não é recomendado definir as propriedades de métodos de rotina CLR em contradição com a funcionalidade do método. Fazer isso poderia causar a corrupção de dados.

A instrução SELECT na exibição não pode conter os elementos de sintaxe Transact-SQL a seguir:
- O * ou a sintaxe * table_name**.** para especificar colunas. Nomes de coluna devem ser determinados explicitamente.
- Um nome de coluna de tabela usado como uma expressão simples não pode ser especificado em mais de uma coluna de exibição. Uma coluna pode ser referenciada várias vezes com todas as referências, ou todas menos uma, à coluna, sendo parte de uma expressão complexa ou um parâmetro para uma função. Por exemplo, a lista de seleção seguinte não é válida:
```
SELECT ColumnA, ColumnB, ColumnA
```
  Esta lista de seleção é válida:
```
SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB
```
- Uma expressão em uma coluna usada na cláusula GROUP BY ou uma expressão em resultados de uma agregação.
- Uma tabela derivada.
- Uma expressão de tabela comum (CTE).
- Funções do conjunto de linhas.
- Operadores UNION, EXCEPT ou INTERSECT.
- Subconsultas
- Junções externas ou autojunções.
- Cláusula TOP.
- Cláusula ORDER BY.
- Palavra-chave DISTINCT
- COUNT (COUNT_BIG (*) é permitida.)
- As funções de agregação AVG, MAX, MIN, STDEV, STDEVP, VAR ou VARP. Se AVG(expression) é especificada em consultas que referenciam a exibição indexada, o otimizador pode calcular freqüentemente o resultado necessário se a lista de seleção de exibição contiver SUM(expression) e COUNT_BIG(expression). Por exemplo, uma lista de SELECT de exibição indexada não pode conter a expressão AVG(column1). Se a lista de exibição SELECT contiver as expressões SUM(column1) e COUNT_BIG(column1), SQL Server pode calcular a média para uma consulta que referencia a exibição e especifica AVG(column1).
- Uma função SUM que referencia uma expressão nullable.
- A cláusula OVER, que inclui funções de classificação ou de janela de agregação.
- Uma função de agregação CLR definida pelo usuário.
- Os predicados de texto completo CONTAINS ou FREETEXT.
- Cláusula COMPUTE ou COMPUTE BY.
- Os operadores CROSS APPLY ou OUTER APPLY.
- Os operadores PIVOT ou UNPIVOT
- Dicas de tabela (se aplica somente ao nível de compatibilidade de 90 ou superior).
- Dicas de junção.
- Referências diretas para expressões Xquery. Referências indiretas, como expressões Xquery dentro de uma função definida pelo usuário associada a esquema, são aceitáveis.
Se GROUP BY for especificado, a lista de seleção de exibição deverá conter uma expressão COUNT_BIG(*), e a definição de exibição não poderá especificar HAVING, ROLLUP, CUBE ou GROUPING SETS.

Requisitos para a instrução CREATE INDEX

O primeiro índice criado em uma exibição deve ser um índice clusterizado exclusivo. Depois que for criado o índice clusterizado exclusivo, você poderá criar índices não clusterizados adicionais. As convenções de nomenclatura para índices em exibições são semelhantes às convenções para índices em tabelas. A única diferença é que o nome de tabela é substituído por um nome de exibição. Para obter mais informações, consulte CREATE INDEX (Transact-SQL).

A instrução CREATE INDEX deve satisfazer os requisitos seguintes como também os requisitos CREATE INDEX regulares:

O usuário que executa a instrução CREATE INDEX deve ser o proprietário da exibição.
As opções SET seguintes devem ser definidas como ON quando a instrução CREATE INDEX for executada:
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER
A opção NUMERIC_ROUNDABORT deve ser definida como OFF. Essa é a configuração padrão.
Se o banco de dados estiver sendo executado em modo de compatibilidade 80 ou menor, a opção ARITHABORT deverá ser definida como ON.
Quando você cria um índice clusterizado ou não clusterizado, a opção IGNORE_DUP_KEY deve ser definida como OFF (a configuração padrão).
A exibição não pode incluir colunas text, ntext ou colunas image, mesmo que não estejam referenciadas na instrução CREATE INDEX.
Se a instrução SELECT na definição de exibição especifica uma cláusula GROUP BY, a chave de índice clusterizado exclusivo pode fazer referência somente a colunas especificadas na cláusula GROUP BY.
Uma expressão imprecisa que forma o valor de uma coluna de chave do índice deve fazer referência a uma coluna armazenada em uma tabela base subjacente a exibição. Essa coluna pode ser uma coluna armazenada regular ou uma coluna computada persistente. Nenhuma outra expressão imprecisa pode fazer parte da coluna chave de uma exibição indexada.

Considerações

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. Para obter mais informações, consulte Usando tipos de dados de valor grande.

Após o índice clusterizado ser criado, qualquer conexão que tenta modificar os dados base para a exibição também deve ter as mesmas configurações de opção exigidas para criar o índice. O SQL Server gera um erro e reverte qualquer instrução INSERT, UPDATE ou DELETE que afetará o conjunto de resultados da exibição se a conexão que estiver executando a instrução não tiver a configurações de opção corretas. Para obter mais informações, consulte Opções SET que afetam os resultados.

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.

Embora somente as colunas que formam a chave do índice clusterizado sejam especificadas na instrução CREATE UNIQUE CLUSTERED INDEX, o conjunto de resultado completo da exibição é armazenado no banco de dados. Como em um índice clusterizado em uma tabela base, uma estrutura da árvore B do índice clusterizado contém somente as colunas chave, mas as linhas de dados contêm todas as colunas no conjunto de resultados da exibição.

Se desejar adicionar índices às exibições em um sistema existente, você precisa associar ao esquema qualquer exibição na qual queira colocar um índice. Você pode executar as seguintes operações:

Descarte a exibição e a recrie especificando WITH SCHEMABINDING.

É possível criar uma segunda exibição que tenha o mesmo texto da exibição existente, mas com um nome diferente. O otimizador considera os índices na nova exibição, mesmo se ela não estiver diretamente referenciada na cláusula FROM de consultas.

Observação
As exibições ou tabelas que participam de uma exibição criada com a cláusula SCHEMABINDING não podem ser descartadas, a menos que a exibição seja descartada ou alterada, de tal forma que não mais possua associação de esquema. Além disso, as instruções ALTER TABLE nas tabelas que participam em exibições com associação de esquema falharão caso essas instruções afetem a definição da exibição.

As exibições ou tabelas que participam de uma exibição criada com a cláusula SCHEMABINDING não podem ser descartadas, a menos que a exibição seja descartada ou alterada, de tal forma que não mais possua associação de esquema. Além disso, as instruções ALTER TABLE nas tabelas que participam em exibições com associação de esquema falharão caso essas instruções afetem a definição da exibição.

Você deve ter certeza que a exibição nova satisfaz todos os requisitos de uma exibição indexada. Isso pode exigir que você altere a propriedade da exibição e todas as tabelas base a que ela faz referência, para que todas possam pertencer ao mesmo usuário.

Í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. Para obter mais informações, consulte Desabilitando índices.

Exemplo

O exemplo a seguir cria uma exibição e um índice naquela exibição. Duas consultas que usam a exibição indexada são incluídas.

USE AdventureWorks;
GO
--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;
GO
--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
--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 ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can 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 DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO