Partilhar via

Índices em colunas computadas

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Você pode definir índices em colunas computadas contanto que os seguintes requisitos sejam satisfeitos:

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

Observação

SET QUOTED_IDENTIFIER precisará ser ON ao criar ou alterar índices em colunas computadas ou exibições indexadas. Para obter mais informações, confira SET QUOTED_IDENTIFIER (Transact-SQL).

Requisitos de propriedade

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

Requisitos de determinismo

Expressões são determinísticas se elas sempre retornarem o mesmo resultado para um conjunto de entradas especificado. A propriedade IsDeterministic da função COLUMNPROPERTY relata se um computed_column_expression é determinístico.

A computed_column_expression deve ser determinística. Uma computed_column_expression é determinística quando todas as seguintes condições são verdadeiras:

  • Todas as funções mencionadas pela expressão são determinísticas e precisas. Essas funções incluem as funções definidas pelo usuário e internas. Para obter mais informações, veja Funções determinísticas e não determinísticas. Funções podem ser imprecisas se a coluna computada for PERSISTED. Para obter mais informações, veja Criar índices em colunas computadas persistentes, mais adiante neste artigo.

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

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

  • A computed_column_expression não tem acesso a dados do sistema nem a dados do usuário.

Qualquer coluna computada que contenha uma expressão CLR (Common Language Runtime) deve ser determinística e marcada como PERSISTED antes que a coluna possa ser indexada. Expressões de tipo de dado CLR definido pelo usuário são permitidas em definições de coluna computada. Colunas computadas cujo tipo é um tipo de dado CLR definido pelo usuário podem ser indexadas contanto que o tipo seja comparável. Para obter mais informações, veja Tipos CLR definidos pelo usuário.

CAST e CONVERT

Quando você se referir a literais de cadeia de caracteres do tipo de dados de data em colunas computadas indexadas no SQL Server, 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, veja CAST e CONVERT.

Para obter mais informações, confira Conversão não determinística de cadeias de caracteres de data literal em valores de DATA.

Nível de Compatibilidade

A conversão implícita de dados de caractere não Unicode entre ordenações será considerada não determinística, a menos que o nível de compatibilidade seja definido como 80 ou abaixo disso.

Quando o nível de compatibilidade do banco de dados é 90, você não pode criar índices em colunas computadas que contêm essas expressões. Porém, a existência de colunas computadas com essas expressões de um banco de dados atualizado é sustentável. Se você usar colunas computadas indexadas que contêm conversões implícitas de cadeia de caracteres para datas; para evitar um possível índice corrompido, verifique se as configurações LANGUAGE e DATEFORMAT estão consistentes em seus bancos de dados e aplicativos.

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

Requisitos de precisão

A computed_column_expression deve ser precisa. Uma computed_column_expression é precisa quando uma ou mais das seguintes opções é verdadeira:

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

  • Não usa um tipo de dados float ou real em sua definição. Por exemplo, na instrução a seguir, 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 expressão float ou real é considerada imprecisa e não pode ser uma chave de um índice; uma expressão float ou real pode ser usada em uma exibição indexada, mas não como uma chave. Isso também é verdade para colunas computadas. Qualquer função, expressão ou função definida pelo usuário será considerada imprecisa se contiver uma expressão float ou real . Isso inclui as lógicas (comparações).

A propriedade IsPrecise da função COLUMNPROPERTY relata se uma computed_column_expression é precisa.

Requisitos de tipo de dados

  • A computed_column_expression definida para a coluna computada não pode ser avaliada para os tipos de dados text, ntext ou image.
  • Colunas computadas derivadas dos tipos de dados image, ntext, text, varchar(max), nvarchar(max), varbinary(max)e xml podem ser indexadas, desde que o tipo de dados da coluna computada seja permitido como uma coluna de chave de índice.
  • Colunas computadas derivadas dos tipos de dados image, ntexte text podem ser colunas (incluídas) não chave em um índice não clusterizado, desde que o tipo de dados da coluna computada seja permitida como uma coluna de índice não chave.

Requisitos de opção SET

  • A opção de nível de conexão ANSI_NULLS deve ser definida como ON quando a instrução CREATE TABLE ou ALTER TABLE que define a coluna computada é executada. A função OBJECTPROPERTY relata se a opção está ativa pela propriedade IsAnsiNullsOn.

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

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

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

Observação

A definição de ANSI_WARNINGS como ON definirá ARITHABORT implicitamente como ON quando o nível de compatibilidade do banco de dados estiver definido como 90 ou mais.

Criar índices em colunas computadas persistentes

Às vezes, você pode criar uma coluna computada definida por uma expressão determinística, mas imprecisa. Você pode fazer isso quando a coluna estiver 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 as outras colunas das quais a coluna computada depende são atualizadas. O Mecanismo de Banco de Dados usa esses valores persistentes ao criar um índice na coluna e quando o índice é referenciado em uma consulta.

Essa opção permite a você criar um índice em uma coluna computada quando o mecanismo de banco de dados não puder provar, com precisão, se uma função que retorna expressões de coluna computada, particularmente uma função CLR que é criada no .NET Framework, é determinística e precisa.

Observação

Não é possível criar um índice filtrado em uma coluna computada.

Próximas etapas