Compartir a través de

Índices en columnas calculadas

Puede definir índices en columnas calculadas siempre que se cumplan los siguientes requisitos:

  • Requisitos de propiedad

  • Requisitos de determinismo

  • Requisitos de precisión

  • Requisitos de tipo de datos

  • Requisitos de la opción SET

Requisitos de propiedad

Todas las referencias de función de la columna calculada deben tener el mismo propietario que la tabla.

Requisitos de determinismo

Importante

Las expresiones son deterministas si siempre devuelven el mismo resultado para un conjunto especificado de entradas. La propiedad IsDeterministic de la función COLUMNPROPERTY informa de si un computed_column_expression es determinista.

El computed_column_expression debe ser determinista. Un computed_column_expression es determinista cuando se cumple uno o varios de los siguientes elementos:

  • Todas las funciones a las que hace referencia la expresión son deterministas y precisas. Estas funciones incluyen funciones definidas por el usuario y integradas. Para obtener más información, consulte Deterministic and Nondeterministic Functions. Las funciones pueden ser imprecisas si la columna calculada es persistente. Para obtener más información, vea Crear índices en columnas calculadas persistentes más adelante en este tema.

  • Todas las columnas a las que se hace referencia en la expresión proceden de la tabla que contiene la columna calculada.

  • Ninguna referencia de columna extrae datos de varias filas. Por ejemplo, las funciones de agregado como SUM o AVG dependen de los datos de varias filas y harían que un computed_column_expression sea indeterminista.

  • El computed_column_expression no tiene acceso a datos del sistema ni acceso a datos de usuario.

Cualquier columna calculada que contenga una expresión de Common Language Runtime (CLR) debe ser determinista y marcada como PERSISTED antes de que se pueda indexar la columna. Las expresiones de tipo CLR definidas por el usuario se permiten en las definiciones de columnas calculadas. Las columnas calculadas cuyo tipo es un tipo definido por el usuario clR se pueden indexar siempre que el tipo sea comparable. Para obtener más información, vea Tipos definidos por el usuario de CLR.

Nota:

Al hacer referencia a literales de cadena del tipo de datos de fecha en columnas calculadas indizadas en SQL Server, recomendamos que convierta explícitamente el literal al tipo de fecha deseado utilizando un estilo de formato de fecha determinista. Para obtener una lista de los estilos de formato de fecha que son deterministas, consulte CAST y CONVERT. Las expresiones que implican la conversión implícita de cadenas de caracteres a tipos de datos de fecha se consideran no deterministas, a menos que el nivel de compatibilidad de la base de datos esté establecido en 80 o en una versión anterior. Esto se debe a que los resultados dependen de la configuración LANGUAGE y DATEFORMAT de la sesión del servidor. Por ejemplo, los resultados de la expresión CONVERT (datetime, '30 listopad 1996', 113) dependen de la configuración LANGUAGE porque la cadena '30 listopad 1996' significa meses diferentes en distintos idiomas. Del mismo modo, en la expresión DATEADD(mm,3,'2000-12-01'), el motor de base de datos interpreta la cadena '2000-12-01' en función de la configuración DATEFORMAT.

La conversión implícita de datos de carácter no Unicode entre intercalaciones también se considera no determinista, a menos que el nivel de compatibilidad esté establecido en 80 o versiones anteriores.

Cuando la configuración del nivel de compatibilidad de la base de datos es 90, no puede crear índices en columnas calculadas que contengan estas expresiones. Sin embargo, se pueden mantener las columnas calculadas existentes que contienen estas expresiones de una base de datos actualizada. Si usa columnas calculadas indexadas que contienen conversiones implícitas de cadena a fecha, para evitar posibles corrupciones del índice, asegúrese de que las configuraciones de LANGUAGE y DATEFORMAT sean coherentes en sus bases de datos y aplicaciones.

Requisitos de precisión

El computed_column_expression debe ser preciso. Un computed_column_expression es preciso cuando se cumple uno o varios de los siguientes elementos:

  • No es una expresión de los float tipos de datos o real .

  • No utiliza un tipo de datos float ni un tipo de datos real en su definición. Por ejemplo, en la declaración siguiente, la columna y es int y determinista pero no 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);

Nota:

Cualquier float expresión o real se considera imprecisa y no puede ser una clave de un índice; una float expresión o real se puede usar en una vista indizada, pero no como clave. Esto también es cierto para las columnas calculadas. Cualquier función, expresión o función definida por el usuario se considera imprecisa si contiene alguna float expresión o real . Esto incluye los aspectos lógicos (comparaciones).

La propiedad IsPrecise de la función COLUMNPROPERTY informa de si un computed_column_expression es preciso.

Requisitos de tipo de datos

  • El computed_column_expression definido para la columna calculada no puede evaluar a los tipos de datos text, ntext o image.

  • Las columnas calculadas derivadas de image, ntext, text, varchar(max), nvarchar(max), varbinary(max), y xml pueden ser indexadas siempre y cuando el tipo de datos de la columna calculada sea admisible como columna de clave de índice.

  • Las columnas calculadas derivadas de imagelos tipos de datos , ntexty text pueden ser columnas no clave (incluidas) en un índice no clúster siempre que el tipo de datos de columna calculada sea permitido como una columna de índice no clave.

Requisitos de la opción SET

  • La opción de nivel de conexión ANSI_NULLS debe establecerse en ON cuando se ejecuta la instrucción CREATE TABLE o ALTER TABLE que define la columna calculada. La función OBJECTPROPERTY informa de si la opción está activada a través de la propiedad IsAnsiNullsOn .

  • La conexión en la que se crea el índice y todas las conexiones que intentan instrucciones INSERT, UPDATE o DELETE que cambiarán los valores del índice deben tener seis opciones SET establecidas en ON y una opción establecida en OFF. El optimizador omite un índice en una columna calculada para cualquier instrucción SELECT ejecutada por una conexión que no tenga estos mismos valores de opción.

    • La opción NUMERIC_ROUNDABORT debe establecerse en OFF y las siguientes opciones deben establecerse en ON:

    • ANSI_NULLS

    • ANSI_PADDING

    • ANSI_WARNINGS

    • ARITHABORT

    • CONCAT_NULL_YIELDS_NULL

    • QUOTED_IDENTIFIER (identificador entre comillas)

    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.

Creación de índices en columnas calculadas persistentes

Puede crear un índice en una columna calculada definida con una expresión determinista, pero imprecisa, si la columna está marcada como PERSISTED en la instrucción CREATE TABLE o ALTER TABLE. Esto significa que el motor de base de datos usa estos valores persistentes cuando crea un índice en la columna y cuando se hace referencia al índice en una consulta. Esta opción permite crear un índice en una columna calculada cuando .NET Framework es determinista y preciso.

Contenido relacionado

COLUMNPROPERTY (Transact-SQL)