Índices en columnas calculadasIndexes on Computed Columns

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Los índices se pueden definir en columnas calculadas si se cumplen estos requisitos:You can define indexes on computed columns as long as the following requirements are met:

  • Requisitos de propiedadOwnership requirements
  • Requisitos de determinismoDeterminism requirements
  • Requisitos de precisiónPrecision requirements
  • Requisitos de tipo de datosData type requirements
  • Requisitos de la opción SETSET option requirements

Requisitos de propiedadOwnership Requirements

Todas las referencias a funciones de la columna calculada deben tener el mismo propietario que la tabla.All function references in the computed column must have the same owner as the table.

Requisitos de determinismoDeterminism Requirements

Las expresiones son deterministas si siempre devuelven el mismo resultado para un conjunto de entradas específico.Expressions are deterministic if they always return the same result for a specified set of inputs. La propiedad IsDeterministic de la función COLUMNPROPERTY informa de si una expresión computed_column_expression es determinista.The IsDeterministic property of the COLUMNPROPERTY function reports whether a computed_column_expression is deterministic.
La expresión computed_column_expression debe ser determinista.The computed_column_expression must be deterministic. Una expresión computed_column_expression es determinista cuando se cumplen todas estas condiciones:A computed_column_expression is deterministic when all of the following are true:

  • Todas las funciones a las que hace referencia la expresión son deterministas y precisas.All functions that are referenced by the expression are deterministic and precise. Esto incluye las funciones definidas por el usuario y las funciones integradas.These functions include both user-defined and built-in functions. Para obtener más información, consulte Deterministic and Nondeterministic Functions.For more information, see Deterministic and Nondeterministic Functions. Puede que las funciones sean imprecisas si el valor de la columna calculada es PERSISTED.Functions might be imprecise if the computed column is PERSISTED. Para obtener más información, vea Crear índices en columnas calculadas persistentes más adelante en este tema.For more information, see Creating Indexes on Persisted Computed Columns later in this topic.

  • Todas las columnas a las que hace referencia la expresión pertenecen a la tabla que contiene la columna calculada.All columns that are referenced in the expression come from the table that contains the computed column.

  • Ninguna referencia a las columnas extrae datos de varias filas.No column reference pulls data from multiple rows. Por ejemplo, las funciones de agregado como SUM o AVG dependen de datos de varias filas y convertirán a computed_column_expression en una expresión no determinista.For example, aggregate functions such as SUM or AVG depend on data from multiple rows and would make a computed_column_expression nondeterministic.

  • La expresión computed_column_expression no tiene acceso a los datos del sistema o de usuario.The computed_column_expression has no system data access or user data access.

Cualquier columna calculada que contenga una expresión CLR (Common Language Runtime) debe ser determinista y se debe marcar como PERSISTED para poder indizarla.Any computed column that contains a common language runtime (CLR) expression must be deterministic and marked PERSISTED before the column can be indexed. Las expresiones con el tipo definido por el usuario CLR se pueden utilizar en las definiciones de columnas calculadas.CLR user-defined type expressions are allowed in computed column definitions. Las columnas calculadas con el tipo definido por el usuario CLR se podrán indizar siempre que el tipo sea comparable.Computed columns whose type is a CLR user-defined type can be indexed as long as the type is comparable. Para obtener más información, vea Tipos definidos por el usuario de CLR.For more information, see CLR User-Defined Types.

CAST y CONVERTCAST and CONVERT

Cuando haga referencia a los literales de cadena del tipo de datos de fecha en las columnas calculadas indizadas de SQL ServerSQL Server, se recomienda convertir explícitamente el literal al tipo de datos deseado mediante un estilo de formato de fecha determinista.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 obtener una lista de los estilos de formato de fecha deterministas, vea CAST y CONVERT.For a list of the date format styles that are deterministic, see CAST and CONVERT.

Para obtener más información, vea Nondeterministic conversion of literal date strings into DATE values (Conversión no determinista de las cadenas de fecha literales en valores DATE).For more information, see Nondeterministic conversion of literal date strings into DATE values.

Nivel de compatibilidadCompatibility level

La conversión implícita de datos de caracteres no Unicode entre intercalaciones se considera no determinista, a menos que el nivel de compatibilidad se establezca en 80 o menos.Implicit conversion of non-Unicode character data between collations is considered nondeterministic, unless the compatibility level is set to 80 or earlier.

Cuando el valor del nivel de compatibilidad de la base de datos es 90, no se pueden crear índices en columnas calculadas que incluyan estas expresiones.When the database compatibility level setting is 90, you cannot create indexes on computed columns that contain these expressions. Sin embargo, se pueden mantener las columnas calculadas existentes que contengan estas expresiones procedentes de una base de datos actualizada.However, existing computed columns that contain these expressions from an upgraded database are maintainable. Si utiliza columnas calculadas indizadas que contienen conversiones implícitas de cadena a fecha, para evitar posibles daños en las vistas indizadas, asegúrese de que las opciones LANGUAGE y DATEFORMAT son coherentes en las bases de datos y las aplicaciones.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.

El nivel de compatibilidad 90 corresponde a SQL Server 2005.Compatibility level 90 corresponds to SQL Server 2005.

Requisitos de precisiónPrecision Requirements

La expresión computed_column_expression debe ser precisa.The computed_column_expression must be precise. Una expresión computed_column_expression es precisa si se cumplen una o varias de las condiciones siguientes:A computed_column_expression is precise when one or more of the following is true:

  • No es una expresión del tipo de datos float o real .It is not an expression of the float or real data types.

  • No utiliza en su definición un tipo de datos float o real .It does not use a float or real data type in its definition. Por ejemplo, en la instrucción siguiente, la columna y es int y determinista, pero no 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);  
    

Nota

Las expresiones float o real se consideran imprecisas y no pueden ser la clave de un índice; una expresión float o real puede utilizarse en una vista indizada, pero no como clave.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. Esto también se aplica a las columnas calculadas.This is true also for computed columns. Las funciones, expresiones o funciones definidas por el usuario se considerarán imprecisas si incluyen expresiones float o real .Any function, expression, or user-defined function is considered imprecise if it contains any float or real expressions. Esto incluye a las lógicas (comparaciones).This includes logical ones (comparisons).

La propiedad IsPrecise de la función COLUMNPROPERTY informa de si una expresión computed_column_expression es precisa.The IsPrecise property of the COLUMNPROPERTY function reports whether a computed_column_expression is precise.

Requisitos de tipo de datosData Type Requirements

  • La expresión computed_column_expression definida para la columna calculada no se puede evaluar para los tipos de datos text, ntexto image .The computed_column_expression defined for the computed column cannot evaluate to the text, ntext, or image data types.
  • Las columnas calculadas derivadas de los tipos de datos image, ntext, text, varchar(max), nvarchar(max), varbinary(max) y xml se pueden indexar, siempre que el tipo de datos de la columna calculada esté disponible como una columna de clave 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.
  • Las columnas calculadas derivadas de los tipos de datos image, ntexty text pueden ser columnas sin clave (incluidas) en un índice no agrupado, siempre que el tipo de datos de la columna calculada esté disponible como una columna índice sin clave.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 la opción SETSET Option Requirements

  • La opción de nivel de conexión ANSI_NULLS debe estar establecida en ON si se ejecuta la instrucción CREATE TABLE o ALTER TABLE que define la columna calculada.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. La función OBJECTPROPERTY informa de si la opción está activada a través de la propiedad IsAnsiNullsOn .The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.

  • La conexión en la que se crea el índice y todos los intentos de conexión de las instrucciones INSERT, UPDATE o DELETE que cambiarán los valores del índice deben tener seis opciones SET con el valor ON y una con el valor 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. El optimizador omitirá un índice de una columna calculada para cualquier instrucción SELECT que se ejecute mediante una conexión que no tenga la misma configuración de las opciones.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.

    • El valor de la opción NUMERIC_ROUNDABORT debe ser OFF y el de las opciones siguientes debe ser 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

Nota

Al establecer ANSI_WARNINGS en ON, ARITHABORT se establece de forma implícita en ON cuando el nivel de compatibilidad de base de datos está establecido en 90 o un valor superior.Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher.

Crear índices en columnas calculadas persistentesCreating Indexes on Persisted Computed Columns

A veces, puede crear una columna calculada que se define con una expresión que es determinista pero imprecisa.Sometimes you can create a computed column that is defined with an expression that is deterministic yet imprecise. Puede hacerlo cuando la columna está marcada con PERSISTED en la instrucción CREATE TABLE o ALTER TABLE.You can do this when the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement.

Esto significa que Motor de base de datosDatabase Engine almacena los valores calculados en la tabla y los actualiza cuando se actualiza cualquier otra columna de la que depende la columna calculada.This means that the Motor de base de datosDatabase Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. Motor de base de datosDatabase Engine utiliza estos valores persistentes cuando crea un índice en la columna y cuando se hace referencia al índice en una consulta.The Motor de base de datosDatabase Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

Esta opción permite crear un índice en una columna calculada cuando el Motor de base de datosDatabase Engine no puede demostrar con exactitud si una función que devuelve expresiones de columnas calculadas, en especial una función CLR creada en .NET Framework.NET Framework, es determinista y precisa.This option enables you to create an index on a computed column when Motor de base de datosDatabase 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)