Índices en columnas calculadas

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

Los índices se pueden definir en columnas calculadas si se cumplen estos requisitos:

• Requisitos de propiedad
• Requisitos de determinismo
• Requisitos de precisión
• Requisitos de tipo de datos
• SET requisitos de la opción

Requisitos de propiedad

Todas las referencias a funciones de la columna calculada deben tener el mismo propietario que la tabla.

Requisitos de determinismo

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

La expresión computed_column_expression debe ser determinista. Una expresión computed_column_expression es determinista cuando se cumplen todas estas condiciones:

• Todas las funciones a las que hace referencia la expresión son deterministas y precisas. Esto incluye las funciones definidas por el usuario y las funciones integradas. Para obtener más información, consulte Deterministic and Nondeterministic Functions. Puede que las funciones sean imprecisas si el valor de la columna calculada es PERSISTED. Para obtener más información, vea Crear índices en columnas calculadas persistentes más adelante en este artículo.

• Todas las columnas a las que hace referencia la expresión pertenecen a la tabla que contiene la columna calculada.

• Ninguna referencia a las columnas extrae datos de varias filas. Por ejemplo, las funciones de agregado como SUM o AVG dependen de datos de varias filas y convertirán a computed_column_expression en no determinista.

• La expresión computed_column_expression no tiene acceso a datos del sistema ni a datos de usuario.

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

CAST y CONVERT

Cuando haga referencia a los literales de cadena del tipo de datos de fecha en las columnas calculadas indexadas de SQL Server, se recomienda convertir explícitamente el literal al tipo de datos deseado mediante un estilo de formato de fecha determinista. Para obtener una lista de los estilos de formato de fecha deterministas, vea CAST y CONVERT.

Para obtener más información, vea Conversión no determinista de las cadenas de fecha literales en valores DATE.

Nivel de compatibilidad

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.

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. Sin embargo, se pueden mantener las columnas calculadas existentes que contengan estas expresiones procedentes de una base de datos actualizada. Si utiliza columnas calculadas indexadas que contienen conversiones implícitas de cadena a fecha, para evitar una posible corrupción del índice, asegúrese de que la configuración de LANGUAGE y DATEFORMAT sea coherente en sus bases de datos y aplicaciones.

El nivel de compatibilidad 90 corresponde a SQL Server 2005 (9.x).

Requisitos de precisión

La expresión computed_column_expression debe ser precisa. Una expresión computed_column_expression es precisa si se cumplen una o varias de las condiciones siguientes:

• No es una expresión del tipo de datos float o real.

• No utiliza en su definición un tipo de datos float o real. Por ejemplo, en la instrucció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);
  

La propiedad IsPrecise de la función COLUMNPROPERTY informa de si una expresión computed_column_expression es precisa.

Requisitos de tipo de datos

• La expresión computed_column_expression definida para la columna calculada no se puede evaluar para los tipos de datos text, ntext o image.
• 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.
• 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.

SET requisitos de la opción

• 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. 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 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. El optimizador ignora un índice en una columna calculada para cualquier instrucción SELECT ejecutada por una conexión que no tenga esta misma configuración de opciones.

  El valor de la opción NUMERIC_ROUNDABORT debe ser OFF y el de las opciones siguientes debe ser ON:

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

Crear índices en columnas calculadas persistentes

A veces, puede crear una columna calculada que se define con una expresión que es determinista pero imprecisa. Puede hacerlo cuando la columna esté marcada como PERSISTED en la instrucción CREATE TABLE o ALTER TABLE.

Esto significa que Motor de base de datos almacena los valores calculados en la tabla y los actualiza cuando se actualiza cualquier otra columna de la que depende la columna calculada. Motor de base de datos utiliza 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 el motor de base de datos 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, es determinista y precisa.

Pasos siguientes

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