Criando índices em colunas computadas

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

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 informa se um computed_column_expression é determinístico.

• O computed_column_expression deve ser determinístico. Um computed_column_expression é determinístico quando um ou mais dos seguintes itens for verdadeiro:

  • 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, consulte 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, consulte Criando índices em colunas computadas persistentes posteriormente neste tópico.

  • 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 computed_column_expression não determinístico.

  • Não tem acesso a dados do sistema ou acesso 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, consulte Tipos CLR definidos pelo usuário.

Observação

Quando você se referir a literais de uma cadeia de caracteres de 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, consulte CAST e CONVERT. Expressões que envolvem conversão implícita de cadeias de caracteres para tipos de dados de data são consideradas não determinísticas, a menos que o nível de compatibilidade de banco de dados 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 '30 listopad 1996' significa meses diferentes em idiomas. Semelhantemente, na expressão DATEADD(mm,3,'2000-12-01'), o Mecanismo de Banco de Dados interpreta a cadeia de caracteres '2000-12-01' com base na configuração DATEFORMAT. 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. Quando o nível da configuração da compatibilidade de 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 possível corrupção de índice, verifique se as configurações LANGUAGE e DATEFORMAT estão consistentes em seus bancos de dados e aplicativos.

Requisitos de precisão

O computed_column_expression deve ser preciso. Um computed_column_expression é preciso quando um ou mais dos seguintes itens é verdadeiro:

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

• Não usa float ou tipo de dados 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 qualquer expressão float ou real. Isso inclui as lógicas (comparações).

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

Requisitos de tipo de dados

• Os computed_column_expression definidos para a coluna computada não podem avaliar o text, ntextou tipos de dados image.

• Colunas computadas derivadas de image, ntext, text, varchar(max), nvarchar(max), varbinary(max), e tipos de dados xml podem ser indexados contanto que o tipo de dados de coluna computada seja permitido como coluna de índice chave.

• Colunas computadas derivadas de image, ntext, e tipos de dados text podem ser colunas não-chave (inclusas) em um índice não-clusterizado contanto que o tipo de dados da coluna computada seja permitida como 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 informa 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 de 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

  Definir ANSI_WARNINGS como ON define implicitamente ARITHABORT como ON quando o nível de compatibilidade do banco de dados estiver definido como 90. Se o nível de compatibilidade do banco de dados for definido como 80 ou abaixo disso, a opção ARITHABORT deverá ser definida explicitamente como ON. Para obter mais informações, consulte Opções SET que afetam os resultados.

Criando índices em colunas computadas persistentes

Você pode criar um índice em uma coluna computada que está definida com uma expressão determinística, mas imprecisa, se a coluna for marcada como PERSISTED na instrução CREATE TABLE ou ALTER TABLE. Isso significa que Mecanismo de Banco de Dados armazena os valores computados na tabela, e os atualiza quando qualquer outra coluna da qual depende a coluna computada for atualizada. O Mecanismo de Banco de Dados usa esses valores persistentes quando cria um índice na coluna, e quando o índice é consultado em uma consulta. Essa opção permite a você criar um índice em uma coluna computada quando Mecanismo de Banco de Dados não puder provar, com exatidã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.

Consulte também

Referência

sp_dbcmptlevel (Transact-SQL)

COLUMNPROPERTY (Transact-SQL)

Conceitos

Criando índices (Mecanismo de Banco de Dados)