Índices em colunas computadas

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

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);
  

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

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.

Próximas etapas

• COLUMNPROPERTY (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)