Índices em colunas computadasIndexes on Computed Columns

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure SQL nãoAzure Synapse Analytics (SQL DW) nãoData Warehouse Paralelo APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Você pode definir índices em colunas computadas contanto que os seguintes requisitos sejam satisfeitos:You can define indexes on computed columns as long as the following requirements are met:

  • Requisitos de propriedadeOwnership requirements
  • Requisitos de determinismoDeterminism requirements
  • Requisitos de precisãoPrecision requirements
  • Requisitos de tipo de dadosData type requirements
  • Requisitos de opção SETSET option requirements

Requisitos de propriedadeOwnership Requirements

Todas as referências de função na coluna computada devem ter o mesmo proprietário da tabela.All function references in the computed column must have the same owner as the table.

Requisitos de determinismoDeterminism Requirements

Expressões são determinísticas se elas sempre retornarem o mesmo resultado para um conjunto de entradas especificado.Expressions are deterministic if they always return the same result for a specified set of inputs. A propriedade IsDeterministic da função COLUMNPROPERTY relata se um computed_column_expression é determinístico.The IsDeterministic property of the COLUMNPROPERTY function reports whether a computed_column_expression is deterministic.
A computed_column_expression deve ser determinística.The computed_column_expression must be deterministic. Uma computed_column_expression é determinística quando todas as seguintes condições são verdadeiras:A computed_column_expression is deterministic when all of the following are true:

  • Todas as funções mencionadas pela expressão são determinísticas e precisas.All functions that are referenced by the expression are deterministic and precise. Essas funções incluem as funções definidas pelo usuário e internas.These functions include both user-defined and built-in functions. Para obter mais informações, veja Funções determinísticas e não determinísticas.For more information, see Deterministic and Nondeterministic Functions. Funções podem ser imprecisas se a coluna computada for PERSISTED.Functions might be imprecise if the computed column is PERSISTED. Para obter mais informações, veja Criando índices em colunas computadas persistentes , mais adiante neste tópico.For more information, see Creating Indexes on Persisted Computed Columns later in this topic.

  • Todas as colunas mencionadas na expressão vêm da tabela que contém a coluna computada.All columns that are referenced in the expression come from the table that contains the computed column.

  • Nenhuma referência de coluna recebe dados de várias linhas.No column reference pulls data from multiple rows. 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.For example, aggregate functions such as SUM or AVG depend on data from multiple rows and would make a computed_column_expression nondeterministic.

  • A computed_column_expression não tem acesso a dados do sistema nem a dados do usuário.The computed_column_expression has no system data access or user data access.

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.Any computed column that contains a common language runtime (CLR) expression must be deterministic and marked PERSISTED before the column can be indexed. Expressões de tipo de dado CLR definido pelo usuário são permitidas em definições de coluna computada.CLR user-defined type expressions are allowed in computed column definitions. Colunas computadas cujo tipo é um tipo de dado CLR definido pelo usuário podem ser indexadas contanto que o tipo seja comparável.Computed columns whose type is a CLR user-defined type can be indexed as long as the type is comparable. Para obter mais informações, veja Tipos CLR definidos pelo usuário.For more information, see CLR User-Defined Types.

CAST e CONVERTCAST and CONVERT

Quando você se referir a literais de cadeia de caracteres do tipo de dados de data em colunas computadas indexadas no SQL ServerSQL Server, recomendamos que você converta explicitamente o literal para o tipo de data desejado, usando um estilo de formato de data determinístico.When you refer to string literals of the date data type in indexed computed columns in SQL ServerSQL Server, we recommend that you explicitly convert the literal to the date type that you want by using a deterministic date format style. Para obter uma lista de estilos de formato de data determinísticos, veja CAST e CONVERT.For a list of the date format styles that are deterministic, see CAST and CONVERT.

Para obter mais informações, confira Conversão não determinística de cadeias de caracteres de data literal em valores de DATA.For more information, see Nondeterministic conversion of literal date strings into DATE values.

Nível de CompatibilidadeCompatibility level

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.Implicit conversion of non-Unicode character data between collations is considered nondeterministic, unless the compatibility level is set to 80 or earlier.

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.When the database compatibility level setting is 90, you cannot create indexes on computed columns that contain these expressions. Porém, a existência de colunas computadas com essas expressões de um banco de dados atualizado é sustentável.However, existing computed columns that contain these expressions from an upgraded database are maintainable. 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.If you use indexed computed columns that contain implicit string to date conversions, to avoid possible index corruption, make sure that the LANGUAGE and DATEFORMAT settings are consistent in your databases and applications.

O nível de compatibilidade 90 corresponde ao SQL Server 2005.Compatibility level 90 corresponds to SQL Server 2005.

Requisitos de precisãoPrecision Requirements

A computed_column_expression deve ser precisa.The computed_column_expression must be precise. Uma computed_column_expression é precisa quando uma ou mais das seguintes opções é verdadeira:A computed_column_expression is precise when one or more of the following is true:

  • Não é uma expressão dos tipos de dados float ou real .It is not an expression of the float or real data types.

  • Não usa um tipo de dados float ou real na definição.It does not use a float or real data type in its definition. Por exemplo, na instrução a seguir, a coluna y é int e determinística, mas não é precisa.For example, in the following statement, column y is int and deterministic but not precise.

    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.Any float or real expression is considered imprecise and cannot be a key of an index; a float or real expression can be used in an indexed view but not as a key. Isso também é verdade para colunas computadas.This is true also for computed columns. Qualquer função, expressão ou função definida pelo usuário será considerada imprecisa se contiver uma expressão float ou real .Any function, expression, or user-defined function is considered imprecise if it contains any float or real expressions. Isso inclui as lógicas (comparações).This includes logical ones (comparisons).

A propriedade IsPrecise da função COLUMNPROPERTY relata se uma computed_column_expression é precisa.The IsPrecise property of the COLUMNPROPERTY function reports whether a computed_column_expression is precise.

Requisitos de tipo de dadosData Type Requirements

  • A computed_column_expression definida para a coluna computada não pode ser avaliada para os tipos de dados text, ntextou image .The computed_column_expression defined for the computed column cannot evaluate to the text, ntext, or image data types.
  • 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.Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be indexed as long as the computed column data type is allowable as an index key column.
  • 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.Computed columns derived from image, ntext, and text data types can be nonkey (included) columns in a nonclustered index as long as the computed column data type is allowable as a nonkey index column.

Requisitos de opção SETSET Option Requirements

  • 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.The ANSI_NULLS connection-level option must be set to ON when the CREATE TABLE or ALTER TABLE statement that defines the computed column is executed. A função OBJECTPROPERTY relata se a opção está ativa pela propriedade IsAnsiNullsOn .The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.

  • 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.The connection on which the index is created, and all connections trying INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to 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.The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.

    • A opção de NUMERIC_ROUNDABORT deve ser definida como OFF e as opções seguintes devem ser definidas como ON:The NUMERIC_ROUNDABORT option must be set to OFF, and the following options must be set to ON:
    • ANSI_NULLSANSI_NULLS
    • ANSI_PADDINGANSI_PADDING
    • ANSI_WARNINGSANSI_WARNINGS
    • ARITHABORTARITHABORT
    • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIERQUOTED_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.Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher.

Criando índices em colunas computadas persistentesCreating Indexes on Persisted Computed Columns

Às vezes, você pode criar uma coluna computada definida por uma expressão determinística, mas imprecisa.Sometimes you can create a computed column that is defined with an expression that is deterministic yet imprecise. Você pode fazer isso quando a coluna está marcada como PERSISTED na instrução CREATE TABLE ou ALTER TABLE.You can do this when the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement.

Isso significa que o Mecanismo de Banco de DadosDatabase Engine armazena os valores computados na tabela e os atualiza quando as outras colunas das quais a coluna computada depende são atualizadas.This means that the Mecanismo de Banco de DadosDatabase Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. O Mecanismo de Banco de DadosDatabase Engine usa esses valores persistentes ao criar um índice na coluna e quando o índice é referenciado em uma consulta.The Mecanismo de Banco de DadosDatabase Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

Essa opção permite a você criar um índice em uma coluna computada quando Mecanismo de Banco de DadosDatabase Engine 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.NET Framework, é determinística e precisa.This option enables you to create an index on a computed column when Mecanismo de Banco de DadosDatabase Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework.NET Framework, is both deterministic and precise.

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