améliorations apportées SQL Server et Azure SQL Database dans la gestion de certains types de données et d’opérations inhabituelles

Cet article présente comment les structures persistantes de votre base de données SQL Server peuvent être validées dans le cadre du niveau de compatibilité de mise à niveau, et comment toutes les structures affectées peuvent être reconstruites après la mise à niveau du niveau de compatibilité.

Version du produit d’origine : SQL Server 2017, SQL Server 2016
Numéro de la base de connaissances d’origine : 4010261

Le moteur de base de données dans Microsoft SQL Server 2016 et Azure SQL Database inclut des améliorations dans les conversions de types de données et plusieurs autres opérations. La plupart de ces améliorations offrent une précision accrue lorsque vous travaillez avec des types à virgule flottante, ainsi qu’avec des types datetime classiques.

Ces améliorations sont toutes disponibles lorsque vous utilisez un niveau de compatibilité de base de données d’au moins 130. Cela signifie que pour certaines expressions (principalement rares), vous pouvez voir des résultats différents pour certaines valeurs d’entrée après la mise à niveau de la base de données vers le niveau de compatibilité 130 ou un paramètre supérieur. Ces résultats peuvent être reflétés dans :

• structures persistantes dans la base de données
• données de table incluses soumises à des CHECK contraintes
• colonnes calculées persistantes
• index référençant des colonnes calculées
• index filtrés et vues indexées.

Si vous avez une base de données créée dans une version antérieure de SQL Server, nous vous recommandons d’effectuer une validation supplémentaire après la mise à niveau vers SQL Server 2016 ou une version ultérieure, et avant de modifier le niveau de compatibilité de la base de données.

Si vous constatez que l’une des structures persistantes de votre base de données est affectée par ces modifications, nous vous recommandons de reconstruire les structures affectées après la mise à niveau du niveau de compatibilité de la base de données. Ce faisant, vous tirerez parti de ces améliorations dans SQL Server 2016 ou version ultérieure.

Cet article décrit comment les structures persistantes de votre base de données peuvent être validées dans le cadre de la mise à niveau vers le niveau de compatibilité 130 ou un paramètre supérieur, et comment toutes les structures affectées peuvent être reconstruites après avoir modifié le niveau de compatibilité.

Étapes de validation pendant une mise à niveau vers le niveau de compatibilité de base de données

À compter de SQL Server 2016, SQL Server et Azure SQL Database incluent des améliorations de la précision des opérations suivantes :

• Conversions de types de données rares. Elles incluent notamment les éléments suivants :
  • Float/integer to/from datetime/smalldatetime
  • Real/float to/from numeric/money/smallmoney
  • Float to real
• Certains cas de DATEPART/DATEDIFF et DEGREES
• CONVERT qui utilise un NULL style

Pour utiliser ces améliorations de l’évaluation des expressions dans votre application, définissez le niveau de compatibilité de vos bases de données sur 130 (pour SQL Server 2016) ou 140 (pour SQL Server 2017 et Azure SQL Database). Pour plus d’informations sur toutes les modifications et quelques exemples qui illustrent les modifications, consultez la section Annexe A .

Les structures suivantes dans la base de données peuvent conserver les résultats d’une expression :

• Données de table soumises à des CHECK contraintes
• Colonnes calculées persistantes
• Index qui utilisent des colonnes calculées dans la clé ou des colonnes incluses
• Index filtrés
• Vues indexées

Prenons l’exemple du scénario suivant :

• Vous disposez d’une base de données créée par une version antérieure de SQL Server, ou qui a déjà été créée dans SQL Server 2016 ou une version ultérieure, mais à un niveau de compatibilité 120 ou antérieur.

• Vous utilisez toutes les expressions dont la précision a été améliorée dans le cadre de la définition des structures persistantes dans votre base de données.

Dans ce scénario, vous avez peut-être des structures persistantes qui sont affectées par les améliorations de précision implémentées à l’aide du niveau de compatibilité 130 ou supérieur. Si c’est le cas, nous vous recommandons de valider les structures persistantes et de reconstruire toute structure affectée.

Si vous avez des structures affectées et que vous ne les reconstruisez pas après avoir modifié le niveau de compatibilité, vous pouvez rencontrer des résultats de requête légèrement différents. Les résultats varient selon qu’un index particulier, une colonne calculée ou une vue est utilisé et si les données d’une table peuvent être considérées comme une violation d’une contrainte.

L’indicateur de trace 139 est pris en charge à partir de SQL Server 2016 CU3 et SQL Server 2016 SP1.

Pour mettre à niveau le niveau de compatibilité, procédez comme suit :

1. Effectuez une validation pour identifier les structures persistantes affectées :
   1. Activez l’indicateur de trace 139 en exécutant DBCC TRACEON(139, -1).
   2. Exécutez DBCC CHECKDB/TABLE les commandes et CHECKCONSTRAINTS .
   3. Désactivez l’indicateur de trace 139 en exécutant DBCC TRACEOFF(139, -1).
2. Remplacez le niveau de compatibilité de la base de données par 130 (pour SQL Server 2016) ou 140 (pour SQL Server 2017 et Azure SQL Database).
3. Régénérez toutes les structures que vous avez identifiées à l’étape 1.

• L’annexe A contient une liste détaillée de toutes les améliorations de précision et fournit un exemple pour chacune d’elles.

• L’annexe B contient un processus détaillé de validation et de reconstruction des structures affectées.

• Les annexes C et D contiennent des scripts permettant d’identifier les objets potentiellement affectés dans la base de données. Par conséquent, vous pouvez définir l’étendue de vos validations et générer des scripts correspondants pour exécuter les vérifications. Pour déterminer le plus facilement si des structures persistantes dans vos bases de données sont affectées par les améliorations de précision du niveau de compatibilité 130, exécutez le script dans l’Annexe D afin de générer les vérifications de validation correctes, puis exécutez ce script pour effectuer la validation.

Annexe A : Modifications du niveau de compatibilité 130

Cette annexe fournit des listes détaillées des améliorations apportées à l’évaluation des expressions au niveau de compatibilité 130. Chaque modification inclut un exemple de requête associé. Les requêtes peuvent être utilisées pour afficher les différences entre l’exécution dans une base de données qui utilise un niveau de compatibilité antérieur à 130 et une base de données qui utilise le niveau de compatibilité 130.

Les tableaux suivants répertorient les conversions de types de données et les opérations supplémentaires.

Conversions de types de données

De	À	Remplacez	Exemple de requête	Résultat pour le niveau < de compatibilité 130	Résultat pour le niveau de compatibilité = 130
float, real, numeric, decimal, money, ou smallmoney	datetime ou smalldatetime	Augmentez la précision d’arrondi. Auparavant, le jour et l’heure étaient convertis séparément, et les résultats étaient tronqués avant de les combiner.	DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT)	1.19999996141975	1.2
datetime	bigint, int, or smallint	Une date/heure négative dont la partie heure est exactement une demi-journée ou une demi-journée est arrondie de manière incorrecte (le résultat est désactivé de 1).	DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT)	0	-1
datetime ou smalldatetime	float, real, numeric, money, or smallmoney	Amélioration de la précision pour les 8 derniers bits de précision dans certains cas.	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	Les contrôles de limites sont moins stricts.	SELECT CAST (3.40282347000E+038 AS REAL)	Dépassement arithmétique	3.402823E+38
numeric, money et smallmoney	float	Lorsque l’échelle d’entrée est égale à zéro, il y a une imprécision d’arrondi lorsque vous combinez les quatre parties de numeric.	DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f)	0x4720000000000000	0x4720000000000001
numeric, money et smallmoney	float	Lorsque l’échelle d’entrée est différente de zéro, il y a une imprécision d’arrondi lorsque vous divisez par 10^échelle.	DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8))	0x41678C29C06522C4	0x41678C29C06522C3
real ou float	Numérique	Amélioration de la précision d’arrondi dans certains cas.	DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1))	0.2	0,1
real ou float	Numérique	Amélioration de la précision lorsque vous arrondissez à plus de 16 chiffres dans certains cas.	DECLARE @v decimal(38, 18) = 1E-18 SELECT @v	0.000000000000000000	0.000000000000000001
real ou float	money ou smallmoney	Amélioration de la précision lorsque vous convertissez de grands nombres dans certains cas.	DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money)	562949953421312.2048	562949953421312.25
(n)(var)char	numeric	Une entrée de plus de 39 caractères ne déclenche plus nécessairement un dépassement arithmétique.	DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1))	Dépassement arithmétique	1.1
(n)(var)char	bit	Prend en charge les espaces et les signes de début.	DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit)	La conversion a échoué lors de la conversion de la nvarchar valeur « 1 » en bit de type de données.	1
datetime	time ou datetime2	Amélioration de la précision lorsque vous convertissez en types date/heure avec une précision plus élevée. N’oubliez pas que les valeurs datetime sont stockées sous forme de cycles représentant 1/300e de seconde. Les types time et datetime2 les plus récents stockent un nombre discret de chiffres, où le nombre de chiffres correspond à la précision.	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 ou datetime2	datetime	Amélioration de l’arrondi dans certains cas.	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

Opération

Opération	Remplacez	Exemple de requête	Résultat pour le niveau <de compatibilité 130	Résultat pour le niveau de compatibilité 130
Utilisez la RADIANS fonction intégrée ou DEGREES qui utilise le type de données numérique.	DEGREES divise par pi/180, où il a été précédemment multiplié par 180/pi. Similaire pour RADIANS.	DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1)	57.295779513082323000	57.295779513082322865
Addition ou soustraction numérique lorsque l’échelle d’un opérande est supérieure à l’échelle du résultat.	L’arrondi se produit toujours après l’addition ou la soustraction, alors qu’auparavant il pouvait parfois se produire avant.	DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2	8.8	8.9
CONVERT avec NULL style.	CONVERT avec NULL style retourne NULL toujours lorsque le type cible est numérique.	SELECT CONVERT (SMALLINT, '0', NULL);	0	NULL
DATEPART qui utilise l’option microsecondes ou nanosecondes, avec le type de données datetime.	La valeur n’est plus tronquée au niveau de la milliseconde avant la conversion en micro- ou nanosecondes.	DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt);	3000	3333
DATEDIFF qui utilise l’option microsecondes ou nanosecondes, avec le type de données datetime.	La valeur n’est plus tronquée au niveau de la milliseconde avant la conversion en micro- ou nanosecondes.	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
Comparaison entre les valeurs datetime et datetime2 avec des valeurs différentes de zéro pour les millisecondes.	La valeur Datetime n’est plus tronquée au niveau de la milliseconde lorsque vous exécutez une comparaison avec une valeur datetime2. Cela signifie que certaines valeurs précédemment comparées sont égales, et non plus égales.	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 égal à	1900-01-01 00 :00 :00.003333, 1900-01-01 00 :00 :00.003 inégaux
ROUND fonction qui utilise le type de float données.	Les résultats de l’arrondi diffèrent.	SELECT ROUND(CAST (-0.4175 AS FLOAT), 3)	-0.418	-0.417

Annexe B : Étapes de vérification et de mise à jour des structures persistantes

Nous vous recommandons de déterminer si la base de données a des structures persistantes affectées par les modifications apportées au niveau de compatibilité 130, et de reconstruire les structures affectées.

Cela s’applique uniquement aux structures persistantes créées dans la base de données sur une version antérieure de SQL Server ou à l’aide d’un niveau de compatibilité inférieur à 130. Les structures persistantes potentiellement affectées sont les suivantes :

• Données de table soumises à des CHECK contraintes
• Colonnes calculées persistantes
• Index qui utilisent des colonnes calculées dans la clé ou des colonnes incluses
• Index filtrés
• Vues indexées

Dans ce cas, exécutez la procédure suivante.

Étape 1 : Vérifier le niveau de compatibilité de la base de données

1. Vérifiez le niveau de compatibilité de votre base de données à l’aide de la procédure décrite dans Afficher ou modifiez le niveau de compatibilité d’une base de données.
2. Si le niveau de compatibilité de la base de données est inférieur à 130, nous vous recommandons d’effectuer la validation décrite à l’étape 2 avant d’augmenter le niveau de compatibilité à 130.

Étape 2 : Identifier les structures persistantes affectées

Déterminez si la base de données contient des structures persistantes affectées par l’amélioration de la précision et de la logique de conversion au niveau de compatibilité 130 de l’une des manières suivantes :

• DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, qui valide toutes les structures de la base de données.
• DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, qui valide les structures associées à une seule table.

L’option WITH EXTENDED_LOGICAL_CHECKS est nécessaire pour s’assurer que les valeurs persistantes sont comparées aux valeurs calculées et pour marquer les cas dans lesquels il existe une différence. Étant donné que ces vérifications sont complètes, le runtime des DBCC instructions qui utilisent cette option est plus long que l’exécution DBCC d’instructions sans l’option . Par conséquent, la recommandation pour les bases de données volumineuses est d’utiliser DBCC CHECKTABLE pour identifier des tables individuelles.

DBCC CHECKCONSTRAINTS peut être utilisé pour valider CHECK des contraintes. Cette instruction peut être utilisée au niveau de la base de données ou de la table.

DBCC CHECK les instructions doivent toujours être exécutées pendant une fenêtre de maintenance, en raison de l’impact potentiel des vérifications sur la charge de travail en ligne.

Validation au niveau de la base de données

La validation au niveau de la base de données convient aux bases de données de petite taille et de taille moyenne. Utilisez la validation au niveau de la table pour les bases de données volumineuses.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS est utilisé pour valider toutes les structures persistantes dans la base de données.

DBCC CHECKCONSTRAINTS est utilisé pour valider toutes les CHECK contraintes dans la base de données.

DBCC CHECKCONSTRAINTS est utilisé pour valider l’intégrité des contraintes. Utilisez le script suivant pour valider la base de données :

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

L’utilisation de l’indicateur de trace permet de s’assurer que les vérifications sont effectuées à l’aide de la précision et de la logique de conversion améliorées qui se trouvent au niveau de compatibilité 130, ce qui force la sémantique de conversion correcte même lorsque la base de données a un niveau de compatibilité inférieur.

Si l’instruction CHECKCONSTRAINTS est terminée et ne retourne pas de jeu de résultats, aucune action supplémentaire n’est nécessaire.

Si l’instruction retourne un jeu de résultats, chaque ligne des résultats indique une violation d’une contrainte et inclut également les valeurs qui enfreignent la contrainte.

• Enregistrez les noms des tables et des contraintes, ainsi que les valeurs qui ont provoqué la violation (colonne WHERE dans le jeu de résultats).

L’exemple suivant montre une table avec une CHECK contrainte et une seule ligne qui satisfait la contrainte sous des niveaux de compatibilité inférieurs, mais qui enfreint la contrainte au niveau de compatibilité 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

La CHECKCONSTRAINT commande retourne les résultats suivants.

Tableau	Contrainte	Où
[dbo]. [table1]	[chk1]	[c2] = '1900-01-01 00 :00 :00.997' AND [c3] = '1900-01-01 00 :00 :01.000' AND [c4] = '3'

Ce résultat indique que la contrainte [chk1] est violée pour la combinaison de valeurs de colonne dans « Where ».

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS valide toutes les structures persistantes dans la base de données. Il s’agit de l’option la plus pratique, car une seule instruction valide toutes les structures de la base de données. Toutefois, cette option ne convient pas aux bases de données volumineuses en raison de l’exécution attendue de l’instruction.

Utilisez le script suivant pour valider l’ensemble de la base de données :

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

L’utilisation de l’indicateur de trace permet de s’assurer que les vérifications sont effectuées à l’aide de la précision et de la logique de conversion améliorées qui se trouvent au niveau de compatibilité 130, ce qui force la sémantique de conversion correcte même lorsque la base de données a un niveau de compatibilité inférieur.

Si l’instruction CHECKDB est terminée avec succès, aucune action supplémentaire n’est nécessaire.

Si l’instruction est terminée avec des erreurs, procédez comme suit :

1. Enregistrez les résultats de l’exécution de l’instructionDBCC, qui se trouvent dans le volet messages de SQL Server Management Studio (SSMS), dans un fichier.
2. Vérifiez que les erreurs signalées sont liées à des structures persistantes

Tableau 1 : Structures persistantes et messages d’erreur correspondants pour les incohérences

Type de structure affecté	Messages d’erreur observés	Prenez note de
Colonnes calculées persistantes	Msg 2537, Erreur de table de niveau 16 : ID d’objet <object_id> , ID d’index <index_id> , . L’enregistrement case activée (colonne calculée valide) a échoué. Les valeurs sont .	ID d’objet <object_id> et ID d’index <index_id>
Index référençant des colonnes calculées dans la clé ou des colonnes incluses Index filtrés	Erreur de table msg 8951 : table «< table_name> » (ID <object_id>). La ligne de données n’a pas de ligne d’index correspondante dans l’index '< index_name>' (ID <index_id>) Et/ou Erreur de table msg 8952 : table '<table_name>' (ID <table_name>). La ligne d’index dans l’index '' (ID <index_id>) ne correspond à aucune ligne de données. En outre, il peut y avoir des erreurs secondaires 8955 et/ou 8956. Il contient des détails sur les lignes exactes impactées. Ceux-ci peuvent être ignorés pour cet exercice.	ID d’objet <object_id> et ID d’index <index_id>
Vues indexées	Msg 8908 La vue indexée «< view_name> » (ID d’objet <object_id>) ne contient pas toutes les lignes que la définition de la vue produit. Et/ou Msg 8907 La vue indexée «< view_name> » (ID d’objet <object_id>) contient des lignes qui n’ont pas été produites par la définition de la vue.	id d’objet <object_id>

Une fois la validation au niveau de la base de données terminée, passez à l’étape 3.

Validation au niveau de l’objet

Pour les bases de données volumineuses, il est utile de valider les structures et les contraintes sur une table ou une vue à la fois afin de réduire la taille des fenêtres de maintenance, ou de limiter les vérifications logiques étendues uniquement aux objets potentiellement affectés.

Utilisez les requêtes de la section Annexe C pour identifier les tables potentiellement affectées. Le script de la section Annexe D peut être utilisé pour générer des CHECKTABLE contraintes et CHECKCONSTRAINTS basées sur les requêtes répertoriées dans la section Annexe C .

DBCC CHECKCONSTRAINTS

Pour valider les contraintes liées à une seule table ou vue, utilisez le script suivant :

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

L’utilisation de l’indicateur de trace permet de s’assurer que les vérifications sont effectuées à l’aide de la précision et de la logique de conversion améliorées qui se trouvent au niveau de compatibilité 130, ce qui force la sémantique améliorée même lorsque la base de données a un niveau de compatibilité inférieur.

Si l’instruction CHECKCONSTRAINTS est terminée et ne retourne pas de jeu de résultats, aucune action supplémentaire n’est nécessaire.

Si l’instruction retourne un jeu de résultats, chaque ligne des résultats indique une violation d’une contrainte et fournit également les valeurs qui enfreignent la contrainte.

Enregistrez les noms des tables et des contraintes, ainsi que les valeurs qui ont provoqué la violation (colonne WHERE dans le jeu de résultats).

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

Pour valider les structures persistantes liées à une seule table ou vue, utilisez le script suivant :

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 l’instruction CHECKTABLE est terminée avec succès, aucune action supplémentaire n’est nécessaire.

Si l’instruction est terminée avec des erreurs, procédez comme suit :

1. Enregistrez les résultats de l’exécution de l’instruction DBCC , qui se trouve dans le volet messages de SSMS, dans un fichier.
2. Vérifiez que les erreurs signalées sont liées aux structures persistantes répertoriées dans le tableau 1.
3. Une fois la validation au niveau de la table terminée, passez à l’étape 3.

Étape 3 : Mise à niveau vers le niveau de compatibilité 130

Si le niveau de compatibilité de la base de données est déjà 130, vous pouvez ignorer cette étape.

Le niveau de compatibilité de la base de données peut être remplacé par 130 à l’aide du script suivant :

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Étape 4 : Mettre à jour les structures persistantes

Si aucune incohérence n’a été trouvée lors de la validation effectuée à l’étape 2, vous avez terminé la mise à niveau et vous pouvez ignorer cette étape. Si des incohérences ont été détectées à l’étape 2, des actions supplémentaires sont nécessaires pour supprimer les incohérences de la base de données. Les actions requises dépendent du type de structure affecté.

Sauvegarder votre base de données (ou bases de données)

Nous vous recommandons d’effectuer une sauvegarde complète de la base de données avant d’effectuer les actions décrites dans la section suivante. Si vous utilisez Azure SQL Database, vous n’avez pas besoin d’effectuer une sauvegarde vous-même. Vous pouvez toujours utiliser la fonctionnalité de restauration à un instant dans le temps pour revenir en arrière dans le temps en cas de problème avec l’une des mises à jour.

Contraintes CHECK

La correction des violations de CHECK contrainte nécessite la modification des données de la table ou de la CHECK contrainte elle-même.

À partir du nom de la contrainte (obtenu à l’étape 2), vous pouvez obtenir la définition de contrainte comme suit :

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Pour inspecter les lignes de table affectées, vous pouvez utiliser les informations Where précédemment retournées par l’instruction DBCC CHECKCONSTRAINTS :

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

Vous devez mettre à jour les lignes affectées ou modifier la définition de la contrainte pour vous assurer que la contrainte n’est pas violée.

Mise à jour des données de table

Il n’existe aucune règle stricte indiquant comment les données doivent être mises à jour. En règle générale, pour chaque instruction Where différente retournée par DBCC CHECKCONSTRAINTS, vous exécutez l’instruction de mise à jour suivante :

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Prenons l’exemple de tableau suivant avec une contrainte et une ligne qui enfreint la contrainte au niveau de compatibilité 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

Dans cet exemple, la contrainte est simple. La colonne c4 doit être égale à une expression impliquant c2 et c3. Pour mettre à jour la table, affectez cette valeur à 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

Notez que la WHERE clause utilisée dans l’instruction update correspond aux informations Where retournées par DBCC CHECKCONSTRAINTS.

Mise à jour de la contrainte CHECK

Pour modifier une CHECK contrainte, vous devez la supprimer et la recréer. Nous vous recommandons d’effectuer les deux opérations dans la même transaction, au cas où il y avait des problèmes avec la définition de contrainte mise à jour. Vous pouvez utiliser le code Transact-SQL suivant :

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

Colonnes calculées persistantes

Le moyen le plus simple de mettre à jour les colonnes calculées persistantes consiste à mettre à jour l’une des colonnes référencées par la colonne calculée. La nouvelle valeur de la colonne peut être identique à l’ancienne valeur, de sorte que l’opération ne modifie aucune donnée utilisateur.

Suivez ces étapes pour toutes les object_id incohérences dans les colonnes calculées que vous avez notées à l’étape 2.

1. Identifier les colonnes calculées :

   • Exécutez la requête suivante pour récupérer le nom de la table et les noms des colonnes calculées persistantes pour le noté 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
     

2. Identifier les colonnes référencées :

• Exécutez la requête suivante pour identifier les colonnes référencées par la colonne calculée. Notez l’un des noms de colonnes référencés :

  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
  

1. Exécutez une UPDATE instruction impliquant l’une des colonnes référencées pour déclencher une mise à jour de la colonne calculée :

   • L’instruction suivante déclenche une mise à jour de la colonne référencée par la colonne calculée et déclenche également une mise à jour de la colonne calculée.

     UPDATE [schema_name].[table_name]
     SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)
     

   • L’expression ISNULL de l’instruction est conçue de telle sorte que la valeur de la colonne d’origine ne soit pas modifiée, tout en veillant à ce que la colonne calculée soit mise à jour à l’aide de la logique d’évaluation d’expression de niveau de compatibilité de base de données 130.

   • N’oubliez pas que pour les tables très volumineuses, vous ne souhaiterez peut-être pas mettre à jour toutes les lignes dans une seule transaction. Dans ce cas, vous pouvez exécuter la mise à jour par lots en ajoutant une WHERE clause à l’instruction de mise à jour qui identifie une plage de lignes, en fonction de la clé primaire, par exemple.

2. Identifiez les index référençant la colonne calculée.

   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
   

Cette requête identifie tous les index qui référencent la colonne calculée persistante. Tout index de ce type doit être reconstruit. Pour ce faire, suivez les étapes de la section suivante.

Index, index filtrés et vues indexées

Les incohérences dans les index correspondent aux erreurs 8951 et 8952 (pour les tables) ou 8907 et 8908 (pour les vues) dans la sortie de l’étape DBCC CHECK 2.

Pour réparer ces incohérences, exécutez DBCC CHECKTABLE avec REPAIR_REBUILD. Cela permet de réparer les structures d’index sans perte de données. Toutefois, la base de données doit être en mode mono-utilisateur et n’est donc pas disponible pour les autres utilisateurs pendant la réparation.

Vous pouvez également reconstruire manuellement les index affectés. Cette option doit être utilisée si la charge de travail ne peut pas être mise hors connexion, car la reconstruction d’index peut être effectuée en tant qu’opération ONLINE (dans les éditions prises en charge de SQL Server).

Reconstruire des index

Si la définition de la base de données en mode mono-utilisateur n’est pas une option, vous pouvez reconstruire individuellement les index à l’aide ALTER INDEX REBUILDde , pour chaque index identifié à l’étape 2.

Utilisez la requête suivante pour obtenir les noms de table et d’index d’un et object_id d’un 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

Utilisez l’instruction suivante pour reconstruire l’index :

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

L’exemple suivant illustre la reconstruction d’un index filtré :

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 vous avez des plans de maintenance réguliers, nous vous recommandons d’inclure cette reconstruction d’index dans le cadre de votre maintenance planifiée.

Réparer à l’aide de DBCC

Pour chaque (object_id) lié à un index avec des incohérences que vous avez notées à l’étape 2, exécutez le script suivant pour effectuer la réparation. Ce script définit la base de données en mode mono-utilisateur pour l’opération de réparation. Dans le pire des cas, la réparation effectue une reconstruction complète de l’index.

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

Annexe C : Requêtes pour identifier les tables candidates

Les scripts suivants identifient les tables candidates que vous pouvez valider à l’aide DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKSde , en fonction de l’existence de structures persistantes et de contraintes qui utilisent des types de données affectés par les améliorations du niveau de compatibilité 130.

L’ensemble de requêtes suivant répertorie des détails sur les tables et les structures potentiellement affectées qui nécessitent une validation supplémentaire.

Vues indexées

La requête suivante retourne toutes les vues indexées référençant des colonnes à l’aide de types de données affectés ou à l’aide de l’une des fonctions intégrées affectées :

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%')

Colonnes calculées persistantes

La requête suivante retourne toutes les tables avec des colonnes calculées référençant d’autres colonnes à l’aide de types de données affectés ou à l’aide de l’une des fonctions intégrées affectées, où la colonne est conservée ou référencée à partir d’un index.

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)

)

Index filtrés

La requête suivante retourne toutes les tables avec des index filtrés qui référencent des colonnes dans la condition de filtre qui ont des types de données affectés :

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

)

Vérifier les contraintes

La requête suivante répertorie toutes les tables avec des contraintes case activée qui référencent des types de données affectés ou des fonctions intégrées :

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%')

Annexe D : Script pour créer des instructions CHECK*

Le script suivant combine les requêtes de l’annexe précédente et simplifie les résultats en présentant une liste de tables et de vues sous la forme d’instructions CHECKCONSTRAINTS et 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