Í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
.gif "Observação importante") Importante |
|---|
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.
O computed_column_expression não tem acesso a dados do sistema ou 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.
.gif "Observação") Observação |
|---|
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, 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. A 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
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.
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 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 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.