Améliorations apportées à SQL Server et Azure SQL Database pour gérer certains types de données et opérations rares

Cet article explique 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 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 apportées aux 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 et 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 persistées
• 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 trouvez l’une des structures persistantes dans votre base de données sont affectées 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. En procédant ainsi, vous bénéficierez de ces améliorations dans SQL Server 2016 ou version ultérieure.

Cet article explique 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 la base de données

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

• Conversions de types de données rares. Il s’agit notamment des éléments suivants :
  • Float/integer to/from datetime/smalldatetime
  • Real/float to/from numeric/money/smallmoney
  • Float to real
• Certains cas et DATEPART/DATEDIFFDEGREES
• CONVERT qui utilise un NULL style

Pour utiliser ces améliorations pour l’évaluation des expressions dans votre application, remplacez le niveau de compatibilité de vos bases de données par 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 montrent 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 les colonnes clés ou incluses
• Index filtrés
• Vues indexées

Examinez le cas suivant :

• Vous disposez d’une base de données créée par une version antérieure de SQL Server, ou déjà créée dans SQL Server 2016 ou une version ultérieure, mais au 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 de structures persistantes dans votre base de données.

Dans ce scénario, vous avez peut-être conservé des structures 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 affecté des structures et que vous ne les régénérez 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 dépendent de l’utilisation d’un index particulier, d’une colonne calculée ou d’une vue, 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 la validation pour identifier les structures persistantes affectées :
   1. Activez l’indicateur de trace 139 en exécutant DBCC TRACEON(139, -1).
   2. Exécuter DBCC CHECKDB/TABLE et CHECKCONSTRAINTS commandes.
   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 chacun d’eux.

• L’annexe B contient un processus détaillé pas à pas pour effectuer la validation et reconstruire les structures affectées.

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

Annexe A : Modifications apportées au niveau de compatibilité 130

Cette annexe fournit des listes détaillées des améliorations apportées à l’évaluation des expressions dans le 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 par rapport à 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

Du	À	Modifier	Exemple de requête	Résultat du niveau < de compatibilité 130	Résultat du niveau de compatibilité = 130
float, real, numeric, decimal, money ou smallmoney	datetime ou smalldatetime	Augmentez la précision d’arrondi. Auparavant, la journée et l’heure ont été converties séparément, et les résultats ont été 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	Datetime négative dont la partie d’heure est exactement une demi-journée ou dans une graduation d’une demi-journée est arrondie incorrectement (le résultat est désactivé par 1).	DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT)	0	-1
datetime ou smalldatetime	float, real, numeric, money, or smallmoney	Précision améliorée 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 vérifications des limites sont moins strictes.	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 existe une imprécision arrondie lorsque vous combinez les quatre parties numériques.	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 n’est pas nulle, il existe une imprécision arrondie 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	numeric	Précision d’arrondi améliorée dans certains cas.	DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1))	0.2	0.1
real ou float	numeric	Précision améliorée 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)	Échec de la conversion lors de la conversion de la nvarchar valeur « 1 » en bit de type de données.	1
datetime	time ou datetime2	Précision améliorée lorsque vous convertissez en types date/heure avec une précision supérieure. N’oubliez pas que les valeurs datetime sont stockées sous forme de graduations représentant 1/300e d’une seconde. Les types datetime2 et heure 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	Modifier	Exemple de requête	Résultat du niveau <de compatibilité 130	Résultat du 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 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 elle 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 le 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érente 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, qu’elles ne sont 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.0033333, 1900-01-01 00:00:00.003 inégaux
ROUND fonction qui utilise le type de float données.	Les résultats d’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 que vous régénérez 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 les colonnes clés ou 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 documentée dans l’affichage 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 la logique de précision et de conversion améliorée dans le 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 table unique.

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

DBCC CHECKCONSTRAINTS peut être utilisé pour valider CHECK les 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 modérée. 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 garantit que les vérifications sont effectuées à l’aide de la logique de précision et de conversion améliorée qui est au niveau de compatibilité 130, forçant 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 un 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 violent la contrainte.

• Enregistrez les noms des tables et des contraintes, ainsi que les valeurs qui ont provoqué la violation (la WHERE colonne 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 sous le 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.

Table	Contrainte	Where
[dbo]. [table1]	[chk1]	[c2] = '1900-01-01 00: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 l’emplacement « 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 instruction unique 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 garantit que les vérifications sont effectuées à l’aide de la logique de précision et de conversion améliorée qui est au niveau de compatibilité 130, forçant 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’instruction DBCC , trouvés dans le volet messages de SQL Server Management Studio (SSMS), dans un fichier.
2. Vérifiez que l’une des erreurs signalées est liée aux 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, Level 16 Table error : OBJECT ID <object_id> , index ID <index_id> , . Échec de la vérification de l’enregistrement (colonne calculée valide). Les valeurs sont .	ID d’objet <object_id> et ID d’index <index_id>
Index faisant référence à des colonnes calculées dans les index filtrés de colonnes clés ou incluses	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>) Erreur de table et/ou 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. Cela contient des détails sur les lignes exactes affecté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 produites par la définition de vue. 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 vue.	ID d’objet <object_id>

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

Validation au niveau de l’objet

Pour les bases de données plus volumineuses, il est utile de valider des structures et des contraintes sur une table ou une vue à la fois pour réduire la taille des fenêtres de maintenance ou pour 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 et CHECKCONSTRAINTS contraintes CHECKTABLE en fonction des 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 garantit que les vérifications sont effectuées à l’aide de la logique de précision et de conversion améliorée qui est au niveau de compatibilité 130, forçant 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 un 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 violent la contrainte.

Enregistrez les noms des tables et des contraintes, ainsi que les valeurs qui ont provoqué la violation (la WHERE colonne 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 , trouvés dans le volet messages de SSMS, dans un fichier.
2. Vérifiez que l’une des erreurs signalées est liée à des structures persistantes, comme indiqué 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 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 base de données avant d’effectuer l’une des actions décrites dans la section suivante. Si vous utilisez Azure SQL Database, vous n’avez pas à effectuer de sauvegarde vous-même ; vous pouvez toujours utiliser la fonctionnalité de restauration à un point 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 (obtenue à 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 qui ont été retournées précédemment 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 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 difficile 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 allez exécuter 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 de mise à jour 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, dans le cas où il existe des problèmes avec la définition de contrainte mise à jour. Vous pouvez utiliser les opérations Transact-SQL suivantes :

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 les colonnes indiquées 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 manière que la valeur de la colonne d’origine n’est pas modifiée, tout en veillant à ce que la colonne calculée soit mise à jour à l’aide de la logique d’évaluation d’expression du 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 font référence à la colonne calculée persistante. Tout index de ce type doit être reconstruit. Pour ce faire, suivez les étapes décrites dans 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 répare 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 d’autres utilisateurs lors de 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 des 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 pour un nom donné object_id et 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 montre 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 en utilisant DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, en fonction de l’existence de structures persistantes et de contraintes qui utilisent des types de données affectés par les améliorations apportées au niveau de compatibilité 130.

L’ensemble de requêtes suivant répertorie les 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 faisant référence à 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 affecté les types de données :

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

)

Contraintes de validation

La requête suivante répertorie toutes les tables avec des contraintes de vérification qui font référence aux types de données affectés ou aux 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 et CHECKTABLE les CHECKCONSTRAINTS instructions.

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