Índices em colunas calculadas

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Pode definir índices em colunas calculadas desde que sejam cumpridos os seguintes requisitos:

• Requisitos de propriedade
• Requisitos de determinismo
• Requisitos de precisão
• Requisitos de tipo de dados
• Requisitos da opção SET

Observação

SET QUOTED_IDENTIFIER Deve ser ON quando crias ou mudas índices em colunas computadas ou visualizações indexadas. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

Requisitos de propriedade

Todas as referências de função na coluna calculada devem ter o mesmo proprietário que a tabela.

Requisitos de determinismo

As expressões são determinísticas se devolverem sempre o mesmo resultado para um conjunto específico de entradas. A IsDeterministic propriedade da função COLUMNPROPERTY indica se um computed_column_expression é determinístico.

A computed_column_expression tem de ser determinística. Um computed_column_expression é determinístico quando todas as seguintes afirmações são verdadeiras:

• Todas as funções referenciadas pela expressão são determinísticas e precisas. Estas funções incluem tanto funções definidas pelo utilizador como integradas. Para mais informações, consulte Funções Determinísticas e Não Determinísticas. As funções podem ser imprecisas se a coluna calculada for PERSISTED. Para mais informações, consulte Criar índices em colunas computadas persistentes mais adiante neste artigo.

• Todas as colunas referenciadas na expressão provêm da tabela que contém a coluna computada.

• Nenhuma referência de coluna extrai dados de várias linhas. Por exemplo, funções agregadas como SUM ou AVG dependem de dados de várias linhas e formariam uma computed_column_expression não determinística.

• O computed_column_expression não tem acesso a dados de sistema nem de utilizadores.

Qualquer coluna calculada que contenha uma expressão em tempo de execução de linguagem comum (CLR) deve ser determinística e marcada PERSISTED antes de a coluna poder ser indexada. Expressões de tipos CLR definidas pelo utilizador são permitidas em definições de colunas calculadas. Colunas calculadas cujo tipo é um tipo definido pelo utilizador CLR podem ser indexadas desde que o tipo seja comparável. Para mais informações, consulte Tipos CLR Definidos pelo Usuário.

CAST e CONVERT

Quando se refere a literais de string do tipo de data em colunas computadas indexadas no SQL Server, recomendamos que converta explicitamente o literal para o tipo de data que pretende usando um estilo determinístico de formato de data. Para uma lista dos estilos de formato de data que são determinísticos, veja CAST e CONVERT.

Para mais informações, veja Conversão Não Determinística de cadeias de datas literais em valores DATE.

Nível de compatibilidade

A conversão implícita de dados de caracteres não Unicode entre colações é considerada não determinística, a menos que o nível de compatibilidade seja definido como 80 ou anterior.

Quando a definição do nível de compatibilidade da base de dados é 90, não pode criar índices em colunas computadas que contenham estas expressões. No entanto, as colunas computadas existentes que contêm estas expressões de uma base de dados atualizada são mantíveis. Caso utilize colunas computadas indexadas que contenham conversões implícitas de string para data, para evitar possível corrupção de índice, certifique-se de que as configurações LANGUAGE e DATEFORMAT são consistentes nas suas bases de dados e aplicações.

O nível 90 de compatibilidade corresponde ao SQL Server 2005 (9.x).

Requisitos de precisão

A computed_column_expression tem de ser precisa. Uma computed_column_expression é precisa quando uma ou mais das seguintes afirmações são verdadeiras:

• Não é uma expressão do float ou dos tipos de dados reais .

• Não utiliza um tipo de dados float ou real na sua definição. Por exemplo, na afirmação seguinte, a coluna y é int e determinística mas não precisa.

  CREATE TABLE t2 (a int, b int, c int, x float,
      y AS CASE x
            WHEN 0 THEN a
            WHEN 1 THEN b
            ELSE c
        END);
  

Observação

Qualquer float ou real é considerada imprecisa e não pode ser uma chave de um índice; um float ou real pode ser usado numa vista indexada, mas não como chave. Isto também é verdade para colunas calculadas. Qualquer função, expressão ou função definida pelo utilizador é considerada imprecisa se contiver expressões float ou reais . Isto inclui as lógicas (comparações).

A IsPrecise propriedade da função COLUMNPROPERTY indica se um computed_column_expression é preciso.

Requisitos de tipo de dados

• A computed_column_expression definida para a coluna calculada não pode ser avaliada para os tipos de dados texto, ntext ou imagem .
• Colunas calculadas derivadas de imagem, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml podem ser indexadas desde que o tipo de dados das colunas calculadas seja permitido como coluna de chave de índice.
• Colunas calculadas derivadas de tipos de dados imagem, ntext e texto podem ser colunas não-chave (incluídas) num índice não agrupado, desde que o tipo de dado da coluna calculada seja permitido como coluna de índice não-chave.

Requisitos da opção SET

• A opção de nível de ligação ANSI_NULLS deve ser definida para ON quando a instrução CREATE TABLE ou ALTER TABLE que define a coluna computada for executada. A função OBJECTPROPERTY reporta se a opção está ligada através da IsAnsiNullsOn propriedade.

• A ligação onde o índice é criado, e todas as ligações que tentam INSERT, UPDATE, ou DELETE instruções que alterem valores no índice, devem ter seis SET opções definidas como ON e uma opção definida como OFF. O otimizador ignora um índice numa coluna computada para qualquer instrução SELECT executada por uma ligação que não tenha as mesmas configurações de opções.

  A NUMERIC_ROUNDABORT opção deve ser definida para OFF, e as seguintes opções devem ser definidas para ON:

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

Observação

Definir ANSI_WARNINGS para ON define implicitamente ARITHABORT para ON quando o nível de compatibilidade da base de dados é definido como 90 ou superior.

Criar índices em colunas computadas persistentes

Por vezes pode criar uma coluna computada definida com uma expressão determinística mas imprecisa. Pode fazer isto quando a coluna está marcada PERSISTED na instrução CREATE TABLE ou ALTER TABLE.

Isso significa que o Mecanismo de Banco de Dados armazena os valores computados na tabela e os atualiza quando quaisquer outras colunas das quais a coluna computada depende são atualizadas. O Mecanismo de Banco de Dados usa esses valores persistentes quando cria um índice na coluna e quando o índice é referenciado em uma consulta.

Esta opção permite-lhe criar um índice numa coluna calculada quando o Database Engine não consegue provar com precisão se uma função que devolve expressões de coluna calculadas, particularmente uma função CLR criada no .NET Framework, é tanto determinística como precisa.

Observação

Não podes criar um índice filtrado numa coluna computada.

Próximos passos

• PROPRIEDADE COLUMNA (Transact-SQL)
• CRIAR TABELA (Transact-SQL)
• ALTERAR TABELA (Transact-SQL)