Criar exibições indexadas
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:
- Verifique se as opções de
SET
estão corretas para todas as tabelas existentes que serão referenciadas na exibição. - Verifique se as opções SET da sessão estão definidas corretamente antes de criar qualquer tabela nova e a exibição.
- Verifique se a definição de exibição é determinística.
- Verifique se a tabela base tem o mesmo proprietário que a exibição.
- Crie a exibição usando a opção
WITH SCHEMABINDING
. - 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 comoOFF
(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 serNO
.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
comoDataAccessKind.None
e do atributoSystemDataAccess
comoSystemDataAccessKind.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á conterCOUNT_BIG(*)
e não deverá conterHAVING
. Essas restriçõesGROUP 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 deGROUP 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áusulaGROUP 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
eOPENXML
)Média aritmética AVG
Usar COUNT_BIG
eSUM
como colunas separadasFunções de agregação estatística ( STDEV
,STDEVP
,VAR
eVARP
)Função SUM
que referencia uma expressão que permite valor nuloUsar ISNULL
dentro deSUM()
para tornar a expressão não anulávelOutras funções agregadas ( MIN
,MAX
,CHECKSUM_AGG
eSTRING_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çãoFROM
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áusulaFROM
)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
ouGROUPING 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
eAND
na cláusulaWHERE
respectivamenteORDER 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
Comentários
https://aka.ms/ContentUserFeedback.
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulteEnviar e exibir comentários de