Mejoras de SQL Server y Azure SQL Database en el control de algunos tipos de datos y operaciones poco frecuentes

Artículo
06/20/2023

En este artículo se presenta cómo se pueden validar las estructuras persistentes en la base de datos de SQL Server como parte del nivel de compatibilidad de actualización y cómo se pueden volver a generar las estructuras afectadas después de actualizar el nivel de compatibilidad.

Versión original del producto: SQL Server 2017, SQL Server 2016
Número de KB original: 4010261

El motor de base de datos de Microsoft SQL Server 2016 y Azure SQL Database incluye mejoras en las conversiones de tipos de datos y otras operaciones. La mayoría de estas mejoras ofrecen una mayor precisión cuando se trabaja con tipos de punto flotante y también con tipos de fecha y hora clásicos.

Estas mejoras están disponibles cuando se usa un nivel de compatibilidad de base de datos de al menos 130. Esto significa que para algunas expresiones (principalmente poco frecuentes), puede ver resultados diferentes para algunos valores de entrada después de actualizar la base de datos al nivel de compatibilidad 130 o una configuración superior. Estos resultados se pueden reflejar en:

estructuras persistentes en la base de datos
datos de tabla incluidos que están sujetos a CHECK restricciones
columnas calculadas persistentes
índices que hacen referencia a columnas calculadas
índices filtrados e vistas indizadas.

Si tiene una base de datos que se creó en una versión anterior de SQL Server, se recomienda realizar una validación adicional después de actualizar a SQL Server 2016 o posterior y antes de cambiar el nivel de compatibilidad de la base de datos.

Si encuentra alguna de las estructuras persistentes de la base de datos afectadas por estos cambios, se recomienda recompilar las estructuras afectadas después de actualizar el nivel de compatibilidad de la base de datos. Al hacerlo, se beneficiará de estas mejoras en SQL Server 2016 o posterior.

En este artículo se describe cómo se pueden validar las estructuras persistentes de la base de datos como parte de la actualización al nivel de compatibilidad 130 o una configuración superior, y cómo se pueden volver a generar las estructuras afectadas después de cambiar el nivel de compatibilidad.

Pasos de validación durante una actualización al nivel de compatibilidad de la base de datos

A partir de SQL Server 2016, SQL Server y Azure SQL Database incluyen mejoras en la precisión de las siguientes operaciones:

Conversiones de tipos de datos poco frecuentes. Estos incluyen lo siguiente:
- Float/integer to/from datetime/smalldatetime
- Real/float a/desde numeric/money/smallmoney
- Float a real
Algunos casos de DATEPART/DATEDIFF y DEGREES
CONVERT que usa un NULL estilo

Para usar estas mejoras en la evaluación de expresiones de la aplicación, cambie el nivel de compatibilidad de las bases de datos a 130 (para SQL Server 2016) o 140 (para SQL Server 2017 y Azure SQL Database). Para obtener más información sobre todos los cambios y algunos ejemplos que muestran los cambios, vea la sección Apéndice A .

Las estructuras siguientes de la base de datos pueden conservar los resultados de una expresión:

Datos de tabla sujetos a CHECK restricciones
Columnas calculadas persistentes
Índices que usan columnas calculadas en la clave o columnas incluidas
Índices filtrados
Vistas indizadas

Considere el caso siguiente:

Tiene una base de datos creada por una versión anterior de SQL Server o que ya se creó en SQL Server 2016 o una versión posterior, pero en un nivel de compatibilidad 120 o anterior.
Se usan expresiones cuya precisión se ha mejorado como parte de la definición de estructuras persistentes en la base de datos.

En este escenario, es posible que haya estructuras persistentes afectadas por las mejoras de precisión implementadas mediante el nivel de compatibilidad 130 o superior. Si este es el caso, se recomienda validar las estructuras persistentes y recompilar cualquier estructura afectada.

Si tiene estructuras afectadas y no las vuelve a generar después de cambiar el nivel de compatibilidad, puede experimentar resultados de consulta ligeramente diferentes. Los resultados dependen de si se usa un índice determinado, una columna calculada o una vista, y si los datos de una tabla podrían considerarse una infracción de una restricción.

Nota:

Marca de seguimiento 139 en SQL Server

La marca de seguimiento global 139 se introduce en SQL Server 2016 CU3 y Service Pack (SP) 1 para forzar la semántica de conversión correcta en el ámbito de los comandos dbCC check como DBCC CHECKDB, DBCC CHECKTABLEy DBCC CHECKCONSTRAINTS cuando se analiza la lógica de precisión y conversión mejorada introducida con el nivel de compatibilidad 130 en una base de datos que tiene un nivel de compatibilidad anterior.

Advertencia

La marca de seguimiento 139 no está pensada para habilitarse continuamente en un entorno de producción y debe usarse con el único fin de realizar las comprobaciones de validación de la base de datos descritas en este artículo. Por lo tanto, debe deshabilitarse mediante dbcc traceoff (139, -1) en la misma sesión, una vez completadas las comprobaciones de validación.

La marca de seguimiento 139 se admite a partir de SQL Server 2016 CU3 y SQL Server 2016 SP1.

Para actualizar el nivel de compatibilidad, siga estos pasos:

Realice la validación para identificar las estructuras persistentes afectadas:
1. Habilite la marca de seguimiento 139 ejecutando DBCC TRACEON(139, -1).
2. Ejecute DBCC CHECKDB/TABLE y CHECKCONSTRAINTS comandos.
3. Para deshabilitar la marca de seguimiento 139, ejecute DBCC TRACEOFF(139, -1).
Cambie el nivel de compatibilidad de la base de datos a 130 (para SQL Server 2016) o 140 (para SQL Server 2017 y Azure SQL Database).
Recompile las estructuras que identificó en el paso 1.

Nota:

Las marcas de seguimiento de la configuración de Azure SQL Database no se admiten en Azure SQL Database. Por lo tanto, debe cambiar el nivel de compatibilidad antes de realizar la validación:

Actualice el nivel de compatibilidad de la base de datos a 140.
Valide para identificar las estructuras persistentes afectadas.
Recompile las estructuras que identificó en el paso 2.

El apéndice A contiene una lista detallada de todas las mejoras de precisión y proporciona un ejemplo para cada uno.
El apéndice B contiene un proceso detallado paso a paso para realizar la validación y recompilar las estructuras afectadas.
El apéndice C y el Apéndice D contienen scripts para ayudar a identificar objetos potencialmente afectados en la base de datos. Por lo tanto, puede definir el ámbito de las validaciones y generar scripts correspondientes para ejecutar las comprobaciones. Para determinar con mayor facilidad si las estructuras persistentes de las bases de datos se ven afectadas por las mejoras de precisión en el nivel de compatibilidad 130, ejecute el script en el Apéndice D para generar las comprobaciones de validación correctas y, a continuación, ejecute este script para realizar la validación.

Apéndice A: Cambios en el nivel de compatibilidad 130

En este apéndice se proporcionan listas detalladas de las mejoras en la evaluación de expresiones en el nivel de compatibilidad 130. Cada cambio incluye una consulta de ejemplo asociada. Las consultas se pueden usar para mostrar las diferencias entre la ejecución en una base de datos que usa un nivel de compatibilidad anterior a 130 en comparación con una base de datos que usa el nivel de compatibilidad 130.

En las tablas siguientes se enumeran las conversiones de tipos de datos y las operaciones adicionales.

Conversiones de tipos de datos

De	En	Cambio	Ejemplo de consulta	Resultado del nivel < de compatibilidad 130	Resultado del nivel de compatibilidad = 130
`float`, `real`, `numeric`, `decimal`, `money` o `smallmoney`	`datetime` o `smalldatetime`	Aumentar la precisión de redondeo. Anteriormente, el día y la hora se convertían por separado y los resultados se truncaban antes de combinarlos.	`DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT)`	1.19999996141975	1.2
`datetime`	`bigint, int, or smallint`	Fecha y hora negativa cuyo tiempo es exactamente medio día o en un tic de medio día se redondea incorrectamente (el resultado está desactivado en 1).	`DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT)`	0	-1
`datetime` o `smalldatetime`	`float, real, numeric, money, or smallmoney`	Precisión mejorada para los últimos 8 bits de precisión en algunos casos.	`DECLARE @p0 DATETIME = '1899-12-31 23:58:00.470' DECLARE @f FLOAT = CONVERT(FLOAT, @p0) SELECT @f, CAST(@f AS VARBINARY(8))`	-0.00138344907407406, 0xBF56AA9B21D85800	-0.00138344907407407, 0xBF56AA9B21D8583B
`float`	`real`	Las comprobaciones de límites son menos estrictas.	`SELECT CAST (3.40282347000E+038 AS REAL)`	Desbordamiento aritmético	3.402823E+38
`numeric`, `money` y `smallmoney`	`float`	Cuando la escala de entrada es cero, hay una imprecisión de redondeo al combinar las cuatro partes numéricas.	`DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f)`	0x4720000000000000	0x4720000000000001
`numeric`, `money` y `smallmoney`	`float`	Cuando la escala de entrada es distinta de cero, hay una imprecisión de redondeo cuando se divide en 10^scale.	`DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8))`	0x41678C29C06522C4	0x41678C29C06522C3
`real` o `float`	numérico	Precisión de redondeo mejorada en algunos casos.	`DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1))`	0,2	0,1
`real` o `float`	numérico	Precisión mejorada al redondear a más de 16 dígitos en algunos casos.	`DECLARE @v decimal(38, 18) = 1E-18 SELECT @v`	0.000000000000000000	0.000000000000000001
`real` o `float`	`money` o `smallmoney`	Precisión mejorada al convertir números grandes en algunos casos.	`DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money)`	562949953421312.2048	562949953421312.25
`(n)(var)char`	`numeric`	Una entrada de más de 39 caracteres ya no desencadena necesariamente un desbordamiento aritmético.	`DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1))`	Desbordamiento aritmético	1.1
`(n)(var)char`	`bit`	Admite espacios iniciales y signos.	`DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit)`	Error de conversión al convertir el `nvarchar` valor "1" en bit de tipo de datos.	1
`datetime`	`time` o `datetime2`	Precisión mejorada al convertir a tipos de fecha y hora con mayor precisión. Tenga en cuenta que los valores datetime se almacenan como tics que representan 1/300th de un segundo. Los tipos de fecha y hora más recientes almacenan un número discreto de dígitos, donde el número de dígitos coincide con la precisión.	`DECLARE @value datetime = '1900-01-01 00:00:00.003' SELECT CAST(@value AS time(7))`	00:00:00.0030000	00:00:00.0033333
`time` o `datetime2`	`datetime`	Redondeo mejorado en algunos casos.	`DECLARE @value time(4) = '00:00:00.0045' SELECT CAST(@value AS datetime)`	1900-01-01 00:00:00.007	1900-01-01 00:00:00.003

operación

Operación	Cambio	Ejemplo de consulta	Resultado del nivel <de compatibilidad 130	Resultado del nivel de compatibilidad 130
Use la `RADIANS` función o `DEGREES` integrada que usa el tipo de datos numérico.	`DEGREES` divide por pi/180, donde anteriormente multiplicaba por 180/pi. Similar para `RADIANS`.	`DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1)`	57.295779513082323000	57.295779513082322865
Suma numérica o resta cuando la escala de un operando es mayor que la escala del resultado.	El redondeo siempre se produce después de la suma o resta, mientras que anteriormente podría ocurrir antes.	`DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2`	8.8	8,9
`CONVERT` con `NULL` estilo.	`CONVERT` con `NULL` style siempre devuelve `NULL` cuando el tipo de destino es numérico.	`SELECT CONVERT (SMALLINT, '0', NULL);`	0	`NULL`
`DATEPART` que usa la opción microsegundos o nanosegundos, con el tipo de datos datetime.	El valor ya no se trunca en el nivel de milisegundos antes de convertir en micro-o nanosegundos.	`DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt);`	3000	3333
`DATEDIFF` que usa la opción microsegundos o nanosegundos, con el tipo de datos datetime.	El valor ya no se trunca en el nivel de milisegundos antes de convertir en micro-o nanosegundos.	`DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME = '1900-01-01 00:00:00.007' SELECT DATEDIFF(MICROSECOND, @d1, @d2)`	3000	3333
Comparación entre los valores datetime y datetime2 con valores distintos de cero para milisegundos.	El valor datetime ya no se trunca en el nivel de milisegundos al ejecutar una comparación con un valor datetime2. Esto significa que determinados valores que anteriormente comparan igual, ya no se comparan iguales.	`DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME2(3) = @d1 SELECT CAST(@d1 AS datetime2(7)), @d2SELECT CASE WHEN (@d1=@d2) THEN 'equal' ELSE 'unequal' END`	1900-01-01 00:00:00.0030000, 1900-01-01 00:00:00.003 igual	1900-01-01 00:00:00.00333333, 1900-01-01 00:00:00.003 desigual
`ROUND` función que usa el tipo de `float` datos.	Los resultados de redondeo difieren.	`SELECT ROUND(CAST (-0.4175 AS FLOAT), 3)`	-0.418	-0.417

Apéndice B: Pasos para comprobar y actualizar estructuras persistentes

Se recomienda determinar si la base de datos tiene estructuras persistentes afectadas por los cambios en el nivel de compatibilidad 130 y que recompile las estructuras afectadas.

Esto solo se aplica a las estructuras persistentes creadas en la base de datos en una versión anterior de SQL Server o mediante un nivel de compatibilidad inferior a 130. Entre las estructuras persistentes que pueden verse afectadas se incluyen las siguientes:

Datos de tabla sujetos a CHECK restricciones
Columnas calculadas persistentes
Índices que usan columnas calculadas en la clave o columnas incluidas
Índices filtrados
Vistas indizadas

En esta situación, ejecute el procedimiento siguiente.

Paso 1: Comprobar el nivel de compatibilidad de la base de datos

Compruebe el nivel de compatibilidad de la base de datos mediante el procedimiento que se documenta en Ver o cambie el nivel de compatibilidad de una base de datos.
Si el nivel de compatibilidad de la base de datos es inferior a 130, se recomienda realizar la validación que se describe en el paso 2 antes de aumentar el nivel de compatibilidad a 130.

Paso 2: Identificación de las estructuras persistentes afectadas

Determine si la base de datos contiene estructuras persistentes afectadas por la lógica de precisión y conversión mejoradas en el nivel de compatibilidad 130 de cualquiera de las siguientes maneras:

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, que valida todas las estructuras de la base de datos.
DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, que valida las estructuras relacionadas con una sola tabla.

La opción WITH EXTENDED_LOGICAL_CHECKS es necesaria para asegurarse de que los valores persistentes se comparan con los valores calculados y para marcar los casos en los que hay una diferencia. Dado que estas comprobaciones son extensas, el tiempo de ejecución de DBCC instrucciones que usan esta opción es mayor que las instrucciones en ejecución DBCC sin la opción . Por lo tanto, la recomendación para las bases de datos de gran tamaño es usar DBCC CHECKTABLE para identificar tablas individuales.

DBCC CHECKCONSTRAINTS se puede usar para validar CHECK restricciones. Esta instrucción se puede usar en la base de datos o en el nivel de tabla.

DBCC CHECK Las instrucciones siempre deben ejecutarse durante una ventana de mantenimiento, debido al posible impacto de las comprobaciones en la carga de trabajo en línea.

Validación de nivel de base de datos

La validación en el nivel de base de datos es adecuada para bases de datos pequeñas y moderadas. Use la validación de nivel de tabla para bases de datos grandes.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS se usa para validar todas las estructuras persistentes de la base de datos.

DBCC CHECKCONSTRAINTS se usa para validar todas las CHECK restricciones de la base de datos.

DBCC CHECKCONSTRAINTS se usa para validar la integridad de las restricciones. Use el siguiente script para validar la base de datos:

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

El uso de la marca de seguimiento garantiza que las comprobaciones se realizan mediante la lógica de precisión y conversión mejoradas que se encuentra en el nivel de compatibilidad 130, lo que fuerza la semántica de conversión correcta incluso cuando la base de datos tiene un nivel de compatibilidad inferior.

Si la CHECKCONSTRAINTS instrucción finaliza y no devuelve un conjunto de resultados, no se necesita ninguna acción adicional.

Si la instrucción devuelve un conjunto de resultados, cada línea de los resultados indica una infracción de una restricción y también incluye los valores que infringen la restricción.

Guarde los nombres de las tablas y restricciones, junto con los valores que provocaron la infracción (la WHERE columna del conjunto de resultados).

En el ejemplo siguiente se muestra una tabla con una CHECK restricción y una sola fila que satisface la restricción en niveles de compatibilidad inferiores, pero que infringe la restricción en el nivel de compatibilidad 130.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
    c2 datetime,
    c3 datetime,
    c4 int,
    CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2,c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(
    convert(datetime, '1900-01-01 00:00:00.997'),
    convert(datetime, '1900-01-01 00:00:01'), 3
)
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

El CHECKCONSTRAINT comando devuelve los siguientes resultados.

Tabla	Restricción	Where
[dbo]. [table1]	[chk1]	[c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'

Este resultado indica que se infringe la restricción [chk1] para la combinación de valores de columna en "Where".

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS valida todas las estructuras persistentes de la base de datos. Esta es la opción más conveniente porque una sola instrucción valida todas las estructuras de la base de datos. Sin embargo, esta opción no es adecuada para bases de datos de gran tamaño debido al tiempo de ejecución esperado de la instrucción .

Use el siguiente script para validar toda la base de datos:

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
GO
DBCC TRACEOFF(139, -1)
GO

Si la CHECKDB instrucción se completa correctamente, no se necesita ninguna acción adicional.

Si la instrucción ha finalizado con errores, siga estos pasos:

Guarde los resultados de la ejecución de la DBCC instrucción, que se encuentra en el panel mensajes de SQL Server Management Studio (SSMS) en un archivo.
Compruebe que cualquiera de los errores notificados esté relacionado con las estructuras persistentes.

Tabla 1: Estructuras persistentes y mensajes de error correspondientes para incoherencias

Tipo de estructura afectado	Mensajes de error observados	Tome nota de
Columnas calculadas persistentes	Mensaje 2537, Error de tabla de nivel 16: id <. de objeto object_id> , id. <de índice index_id> , . Error en la comprobación de registros (columna calculada válida). Los valores son .	object_id de id <. de> objeto e id. <de índice index_id>
Índices que hacen referencia a columnas calculadas en la clave o columnas incluidas Índices filtrados	Error de tabla msg 8951: tabla "<table_name>" (id <. object_id>). La fila de datos no tiene una fila de índice coincidente en el índice "<index_name>" (id <. index_id>) Y/o msg 8952 Error de tabla: tabla "<table_name>" (id <. table_name>). La fila de índice '' (id <. index_id>) no coincide con ninguna fila de datos. Además, puede haber errores secundarios 8955 o 8956. Contiene detalles sobre las filas exactas afectadas. Estos pueden omitirse para este ejercicio.	object_id de id <. de> objeto e id. <de índice index_id>
Vistas indizadas	Msg 8908 La vista indizada "<view_name>" (id <. de objeto object_id>) no contiene todas las filas que genera la definición de vista. Y/o Msg 8907 La vista indizada "<view_name>" (id. <de objeto object_id>) contiene filas que no se generaron mediante la definición de vista.	object_id de identificador de <objeto>

Después de completar la validación de nivel de base de datos, vaya al paso 3.

Validación de nivel de objeto

En el caso de las bases de datos más grandes, resulta útil validar estructuras y restricciones en una tabla o una vista a la vez para reducir el tamaño de las ventanas de mantenimiento o limitar las comprobaciones lógicas extendidas solo a objetos potencialmente afectados.

Use las consultas de la sección Apéndice C para identificar las tablas potencialmente afectadas. El script de la sección Apéndice D se puede usar para generar CHECKTABLE restricciones y CHECKCONSTRAINTS en función de las consultas enumeradas en la sección Apéndice C .

DBCC CHECKCONSTRAINTS

Para validar las restricciones relacionadas con una sola tabla o vista, use el siguiente script:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

El uso de la marca de seguimiento garantiza que las comprobaciones se realizan mediante la lógica de precisión y conversión mejoradas que se encuentra en el nivel de compatibilidad 130, lo que fuerza la semántica mejorada incluso cuando la base de datos tiene un nivel de compatibilidad inferior.

Si la CHECKCONSTRAINTS instrucción finaliza y no devuelve un conjunto de resultados, no se necesita ninguna acción adicional.

Si la instrucción devuelve un conjunto de resultados, cada línea de los resultados indica una infracción de una restricción y también proporciona los valores que infringen la restricción.

Guarde los nombres de las tablas y restricciones, junto con los valores que provocaron la infracción (la WHERE columna del conjunto de resultados).

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

Para validar las estructuras persistentes relacionadas con una sola tabla o vista, use el siguiente script:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

Si la CHECKTABLE instrucción se completa correctamente, no se necesita ninguna acción adicional.

Si la instrucción ha finalizado con errores, siga estos pasos:

Guarde los resultados de la ejecución de la DBCC instrucción, que se encuentra en el panel de mensajes de SSMS, en un archivo.
Compruebe que cualquiera de los errores notificados esté relacionado con las estructuras persistentes, como se muestra en la tabla 1.
Después de completar la validación de nivel de tabla, vaya al paso 3.

Paso 3: Actualización al nivel de compatibilidad 130

Si el nivel de compatibilidad de la base de datos ya es 130, puede omitir este paso.

El nivel de compatibilidad de la base de datos se puede cambiar a 130 mediante el siguiente script:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Nota:

Dado que hay cambios del optimizador de consultas en el nivel de compatibilidad 130, se recomienda habilitar el almacén de consultas antes de cambiar el nivel de compatibilidad. Para obtener más información, consulte la sección Mantener la estabilidad del rendimiento durante la actualización a SQL Server más reciente en escenarios de uso de Almacén de consultas.

Paso 4: Actualización de estructuras persistentes

Si no se encontraron incoherencias durante la validación realizada en el paso 2, ha terminado con la actualización y puede omitir este paso. Si se encontraron incoherencias en el paso 2, se requieren acciones adicionales para quitar las incoherencias de la base de datos. Las acciones necesarias dependen del tipo de estructura que se ve afectada.

Importante

Realice las acciones de reparación de este paso solo después de que el nivel de compatibilidad de la base de datos cambie a 130.

Copia de seguridad de la base de datos (o bases de datos)

Se recomienda realizar una copia de seguridad completa de la base de datos antes de realizar cualquiera de las acciones que se describen en la sección siguiente. Si usa Azure SQL Database, no tiene que realizar una copia de seguridad usted mismo; Siempre puede usar la funcionalidad de restauración a un momento dado para volver en el tiempo en caso de que todo vaya mal con cualquiera de las actualizaciones.

restricciones CHECK

La corrección de CHECK infracciones de restricciones requiere la modificación de los datos de la tabla o de la CHECK propia restricción.

A partir del nombre de la restricción (obtenido en el paso 2), puede obtener la definición de restricción de la siguiente manera:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Para inspeccionar las filas de tabla afectadas, puede usar la información Where que devolvió anteriormente la DBCC CHECKCONSTRAINTS instrucción :

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

Debe actualizar las filas afectadas o cambiar la definición de restricción para asegurarse de que no se infringe la restricción.

Actualización de datos de tabla

No hay ninguna regla difícil que indique cómo se deben actualizar los datos. Por lo general, para cada instrucción Where diferente devuelta por DBCC CHECKCONSTRAINTS, ejecutará la siguiente instrucción de actualización:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Considere la tabla de ejemplo siguiente con una restricción y una fila que infringe la restricción en el nivel de compatibilidad 130:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
c2 datetime,
c3 datetime,
c4 int,
CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2, c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(convert(datetime, '1900-01-01 00:00:00.997'),
convert(datetime, '1900-01-01 00:00:01'), 3)
GO

En este ejemplo, la restricción es sencilla. La columna c4 debe ser igual a una expresión que implique c2 y c3. Para actualizar la tabla, asigne este valor a c4:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130
GO
UPDATE dbo.table1 SET c4 = datediff (ms, c2,c3)
WHERE [c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'
GO

Observe que la WHERE cláusula usada en la instrucción update corresponde a la información Where devuelta por DBCC CHECKCONSTRAINTS.

Actualización de la restricción CHECK

Para cambiar una CHECK restricción, debe quitarla y volver a crearla. Se recomienda realizar ambas en la misma transacción, solo en caso de que haya problemas con la definición de restricción actualizada. Puede usar el siguiente Transact-SQL:

BEGIN TRANSACTION

ALTER TABLE [schema_name].[table_name]

DROP CONSTRAINT [constraint_name]

ALTER TABLE [schema_name].[table_name]

ADD CONSTRAINT [constraint_name]

CHECK (new_constraint_definition)

COMMIT

GO

The following example updates the constraint chk1 in dbo.table1:

BEGIN TRANSACTION

ALTER TABLE dbo.table1

DROP CONSTRAINT chk1

ALTER TABLE dbo.table1

ADD CONSTRAINT chk1

CHECK (c4 <= DATEDIFF (ms, c2, c3))

COMMIT

GO

Columnas calculadas persistentes

La manera más fácil de actualizar las columnas calculadas persistentes es actualizar una de las columnas a las que hace referencia la columna calculada. El nuevo valor de la columna puede ser el mismo que el valor anterior, de modo que la operación no cambia ningún dato de usuario.

Siga estos pasos para cada object_id uno de los relacionados con incoherencias en las columnas calculadas que anotó en el paso 2.

Identificar columnas calculadas:

Ejecute la consulta siguiente para recuperar el nombre de la tabla y los nombres de las columnas calculadas persistentes para el objeto indicado object_id:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS 'table',
QUOTENAME(c1.name) AS 'persisted computed column',
c1.column_id AS 'computed_column_id' ,
definition AS 'computed_column_definition'
FROM sys.tables t
JOIN sys.computed_columns c1 ON t.object_id=c1.object_id
AND c1.is_persisted=1
JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.object_id=object_id

Identificar columnas a las que se hace referencia:

Ejecute la consulta siguiente para identificar las columnas a las que hace referencia la columna calculada. Anote uno de los nombres de columna a los que se hace referencia:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) AS 'referencing object',
o.type_desc AS 'object type', referenced_minor_id AS 'referenced_column_id', c.name AS 'referenced_column_name'
FROM sys.sql_expression_dependencies sed
JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id
JOIN sys.objects o ON sed.referencing_id=o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
JOIN sys.columns c ON o.object_id=c.object_id AND sed.referenced_minor_id=c.column_id
WHERE referencing_class=1 AND referenced_class=1 AND referencing_id=object_id AND referencing_minor_id=computed_column_id

Ejecute una UPDATE instrucción que implique una de las columnas a las que se hace referencia para desencadenar una actualización de la columna calculada:
- La siguiente instrucción desencadenará una actualización de la columna a la que hace referencia la columna calculada y también desencadenará una actualización de la columna calculada.
```
UPDATE [schema_name].[table_name]
SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)
```
- La ISNULL expresión de la instrucción se crea de tal manera que el valor de la columna original no cambia, al tiempo que se asegura de que la columna calculada se actualiza mediante la lógica de evaluación de expresiones de nivel de compatibilidad de base de datos 130.
- Tenga en cuenta que para tablas muy grandes, es posible que no desee actualizar todas las filas de una sola transacción. En tal caso, puede ejecutar la actualización en lotes agregando una WHERE cláusula a la instrucción update que identifica un intervalo de filas; en función de la clave principal, por ejemplo.

Identifique los índices que hacen referencia a la columna calculada.

SELECT i.name AS [index name]
FROM sys.index_columns ic JOIN sys.indexes i ON ic.object_id=i.object_id AND ic.index_id=i.index_id
WHERE i.object_id=object_id AND ic.column_id=computed_column_id

Esta consulta identifica los índices que hacen referencia a la columna calculada persistente. Cualquier índice de este tipo debe volver a compilarse. Para ello, siga los pasos descritos en la sección siguiente.

Índices, índices filtrados y vistas indizadas

Las incoherencias en los índices corresponden a errores 8951 y 8952 (para tablas) o 8907 y 8908 (para vistas) en la DBCC CHECK salida del paso 2.

Para reparar estas incoherencias, ejecute DBCC CHECKTABLE con REPAIR_REBUILD. Esto reparará las estructuras de índices sin pérdida de datos. Sin embargo, la base de datos debe estar en modo de usuario único y, por tanto, no está disponible para otros usuarios mientras se está produciendo la reparación.

También puede recompilar manualmente los índices afectados. Esta opción se debe usar si la carga de trabajo no se puede desconectar, ya que la recompilación de índices se puede realizar como una operación ONLINE (en ediciones compatibles de SQL Server).

Recompilación de índices

Si establecer la base de datos en modo de usuario único no es una opción, puede volver a generar índices individualmente mediante ALTER INDEX REBUILD, para cada índice identificado en el paso 2.

Use la consulta siguiente para obtener los nombres de tabla e índice de un determinado object_id y index_id.

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'table', i.name AS 'index_name'

FROM sys.objects o JOIN sys.indexes i ON o.object_id=i.object_id

WHERE o.object_id = object_id AND i.index_id = index_id

Use la siguiente instrucción para recompilar el índice:

ALTER INDEX index_name ON [schema_name].[table_name] REBUILD WITH (ONLINE=ON)

Nota:

Si usa ediciones Standard, Web o Express, no se admite la compilación de índices en línea. Por lo tanto, la opción WITH (ONLINE=ON) debe quitarse de la ALTER INDEX instrucción .

En el ejemplo siguiente se muestra la recompilación de un índice filtrado:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table2
(
    c2 datetime,
    c3 float
)
GO
INSERT dbo.table2 (c2,c3) VALUES ('1899-12-31 23:58:00.470', -0.00138344907407406)
GO
CREATE INDEX ix_1 ON dbo.table2(c2)
WHERE (c2=-0.00138344907407406)
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130GOALTER INDEX ix_1 ON [dbo].[table2] REBUILD WITH (ONLINE=ON)
GO

Si tiene planes de mantenimiento normales, se recomienda incluir esta recompilación de índices como parte del mantenimiento programado.

Reparación mediante DBCC

Para cada (object_id) relacionado con un índice con incoherencias que anotó en el paso 2, ejecute el siguiente script para realizar la reparación. Este script establece la base de datos en modo de usuario único para la operación de reparación. En el peor de los casos, la reparación realiza una recompilación completa del índice.

USE [database_name]

GO

ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKTABLE (object_id, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

ALTER DATABASE CURRENT SET MULTI_USER

GO

Apéndice C: Consultas para identificar tablas candidatas

Los scripts siguientes identifican las tablas candidatas que es posible que desee validar mediante DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, en función de la existencia de estructuras y restricciones persistentes que usan tipos de datos afectados por las mejoras en el nivel de compatibilidad 130.

El siguiente conjunto de consultas muestra detalles sobre las tablas y estructuras potencialmente afectadas que requieren validación adicional.

Vistas indizadas

La consulta siguiente devuelve todas las vistas indizadas que hacen referencia a columnas mediante tipos de datos afectados o mediante cualquiera de las funciones integradas afectadas:

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'view', QUOTENAME(i.name) AS 'index',QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'referenced table', QUOTENAME(c.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the view definition contains a float or datetime value, or a conversion to such value

s.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.objects o ON sed.referencing_id = o.object_id AND o.type=N'V'

JOIN sys.indexes i ON o.object_id=i.object_id

JOIN sys.sql_modules s ON s.object_id=o.object_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_idJOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (c.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR s.[definition] LIKE '%DATEDIFF%'

OR s.[definition] LIKE '%CONVERT%'

OR s.[definition] LIKE '%CAST%'

OR s.[definition] LIKE '%DATEPART%'

OR s.[definition] LIKE '%DEGREES%')

Columnas calculadas persistentes

La consulta siguiente devuelve todas las tablas con columnas calculadas que hacen referencia a otras columnas mediante tipos de datos afectados o mediante cualquiera de las funciones integradas afectadas, donde se conserva o hace referencia a la columna desde un índice.

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with computed column',

QUOTENAME(c1.name) AS 'computed column', c1.is_persisted,QUOTENAME(c2.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the column definition contains a float or datetime value, or a conversion to such value

c1.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id

JOIN sys.columns c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id=c2.column_id

JOIN sys.types t ON c2.system_type_id=t.system_type_idWHERE referencing_class=1 AND referenced_class=1

AND (c2.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR c1.[definition] LIKE '%DATEDIFF%'

OR c1.[definition] LIKE '%CONVERT%'

OR c1.[definition] LIKE '%DATEPART%'

OR c1.[definition] LIKE '%DEGREES%')

AND (

-- the column is persisted

c1.is_persisted=1

-- OR the column is included in an index

OR EXISTS (SELECT 1 FROM sys.index_columns ic WHERE ic.object_id=c1.object_id AND ic.column_id=c1.column_id)

)

Índices filtrados

La consulta siguiente devuelve todas las tablas con índices filtrados que hacen referencia a columnas de la condición de filtro que tienen tipos de datos afectados:

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with filtered index',

QUOTENAME(i.name) AS 'referencing index',

QUOTENAME(c.name) AS 'referenced column',

t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is where the filter condition contains a float or datetime value

i.filter_definition AS 'filter condition'

FROM sys.sql_expression_dependencies sed

JOIN sys.indexes i ON sed.referencing_id=i.object_id AND sed.referencing_minor_id=i.index_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_id

JOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=7 AND referenced_class=1 AND i.has_filter=1

AND c.system_type_id IN ( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

)

Restricciones CHECK

En la consulta siguiente se enumeran todas las tablas con restricciones check que hacen referencia a tipos de datos afectados o funciones integradas:

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with check constraint',

QUOTENAME(c.name) AS 'constraint_name', c.definition AS 'constraint_definition',

QUOTENAME(col.name) AS 'referenced column', t.name AS 'data type'

FROM sys.sql_expression_dependencies sed

JOIN sys.check_constraints c ON sed.referencing_id=c.object_id AND sed.referencing_class=1

JOIN sys.columns col ON sed.referenced_id=col.object_id AND sed.referenced_minor_id=col.column_id

JOIN sys.types t ON col.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (col.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint)

OR c.[definition] LIKE '%DATEDIFF%'

OR c.[definition] LIKE '%CONVERT%'

OR c.[definition] LIKE '%DATEPART%'

OR c.[definition] LIKE '%DEGREES%')

Apéndice D: Script para crear instrucciones CHECK*

El siguiente script combina las consultas del apéndice anterior y simplifica los resultados mediante la presentación de una lista de tablas y vistas en forma de CHECKCONSTRAINTS instrucciones y CHECKTABLE .

DECLARE @CRLF nvarchar(10) = CHAR(13) + CHAR(10);
DECLARE @sql nvarchar(max) = N'DBCC TRACEON(139,-1); ' + @CRLF ;

SELECT @sql += N'DBCC CHECKTABLE (N''' + object_for_checktable + N''') WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS; ' + @CRLF
FROM
(

--indexed views
SELECT DISTINCT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
 INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id AND o.type = N'V'
 INNER JOIN sys.indexes AS i ON o.object_id = i.object_id
 INNER JOIN sys.sql_modules AS s ON s.object_id = o.object_id
 INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id
 INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id

WHERE referencing_class = 1 AND referenced_class=1 
 AND (c.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR s.[definition] LIKE N'%DATEDIFF%'
 OR s.[definition] LIKE N'%CONVERT%'
 OR s.[definition] LIKE N'%CAST%'
 OR s.[definition] LIKE N'%DATEPART%'
 OR s.[definition] LIKE N'%DEGREES%')

UNION

--persisted computed columns
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.computed_columns AS c1 ON sed.referencing_id = c1.object_id AND sed.referencing_minor_id = c1.column_id
INNER JOIN sys.columns AS c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id = c2.column_id
INNER JOIN sys.types AS t ON c2.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 
 AND (c2.system_type_id IN
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c1.[definition] LIKE N'%DATEDIFF%'
 OR c1.[definition] LIKE N'%CONVERT%'
 OR c1.[definition] LIKE N'%DATEPART%'
 OR c1.[definition] LIKE N'%DEGREES%')
AND (
-- the column is persisted
c1.is_persisted = 1 
-- OR the column is included in an index
OR EXISTS (SELECT 1 FROM sys.index_columns AS ic 
WHERE ic.object_id = c1.object_id AND ic.column_id=c1.column_id)
)

UNION

--indexed views
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.indexes AS i ON sed.referencing_id = i.object_id AND sed.referencing_minor_id = i.index_id
INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id 
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE referencing_class = 7 AND referenced_class = 1 AND i.has_filter = 1
AND c.system_type_id IN ( 
 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
)) AS a

SELECT @sql += N'DBCC CHECKCONSTRAINTS (N''' + object_for_checkconstraints + N'''); ' + @CRLF
FROM
(

SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checkconstraints'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.check_constraints AS c ON sed.referencing_id = c.object_id AND sed.referencing_class = 1
INNER JOIN sys.columns AS col ON sed.referenced_id = col.object_id AND sed.referenced_minor_id = col.column_id
INNER JOIN sys.types AS t ON col.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 AND (col.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c.[definition] LIKE N'%DATEDIFF%'
 OR c.[definition] LIKE N'%CONVERT%'
 OR c.[definition] LIKE N'%DATEPART%'
 OR c.[definition] LIKE N'%DEGREES%')
) a

SET @sql += N'DBCC TRACEOFF(139,-1);';

PRINT @sql;

--to run the script immediately, use the following command:
--EXECUTE sp_executesql @sql;
GO

Compartir a través de

Mejoras de SQL Server y Azure SQL Database en el control de algunos tipos de datos y operaciones poco frecuentes

Pasos de validación durante una actualización al nivel de compatibilidad de la base de datos

Apéndice A: Cambios en el nivel de compatibilidad 130

Apéndice B: Pasos para comprobar y actualizar estructuras persistentes

Apéndice C: Consultas para identificar tablas candidatas

Apéndice D: Script para crear instrucciones CHECK*

Comentarios

Recursos adicionales