Trouver les contraintes qui ne sont pas fiables

Publié le par

Pour faciliter le chargement de données, il est parfois nécessaire de désactiver une contrainte de domaine (CHECK) ou de clé étrangère, puis de la réactiver dès la fin du chargement.

Si l’on écrit :

ALTER TABLE maTable CHECK CONSTRAINT maContrainte

La contrainte est alors marquée comme non-fiable, et le moteur de bases de données ne s’en sert plus dans ses plans de requête.
Pire, cela signifie que l’intégrité des données qui ont été importées est alors mise en défaut.

Il faut donc écrire :

ALTER TABLE maTable WITH CHECK CHECK CONSTRAINT maContrainte

Pour que les valeurs ajoutées pendant que la contrainte était désactivée soient vérifiées.

Voici donc une petite requête pour trouver les contraintes qui ne sont plus fiables pour le moteur de base de données …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-------------------------------
-- Nicolas SOUQUET - 26/03/2010
-------------------------------
SELECT    name AS constraint_name
    , CASE KO
      WHEN 0 THEN 1
      WHEN 1 THEN 0
    END AS OK
FROM
(
  SELECT    O.name
      , CASE O.type_desc
        WHEN 'CHECK_CONSTRAINT' THEN CHK.is_not_trusted
        WHEN 'FOREIGN_KEY_CONSTRAINT' THEN FK.is_not_trusted
      END AS KO
  FROM    sys.objects AS O
  LEFT JOIN  sys.check_constraints AS CHK
        ON CHK.object_id = O.object_id
  LEFT JOIN  sys.foreign_keys AS FK
        ON FK.object_id = O.object_id
  WHERE
  (
      CHK.object_id IS NOT NULL
      OR FK.object_id IS NOT NULL
  )
)
AS SUB (name, KO)

Et une autre pour scripter leur réactivation :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-------------------------------
-- Nicolas SOUQUET - 26/03/2009
-------------------------------
SELECT    'ALTER TABLE ' + T.name + ' WITH CHECK CHECK CONSTRAINT ' + O.name AS script
FROM    sys.objects AS O
LEFT JOIN  sys.check_constraints AS CHK
      ON CHK.object_id = O.object_id
LEFT JOIN  sys.foreign_keys AS FK
      ON FK.object_id = O.object_id
LEFT JOIN  sys.tables AS T
      ON O.parent_object_id = T.object_id
WHERE
(
  CHK.is_not_trusted = 1
  OR FK.is_not_trusted = 1
)

Bonne vérification d’intégrité !

ElSüket.

2 réflexions au sujet de « Trouver les contraintes qui ne sont pas fiables »

  1. Pas sur toutes les requêtes. Mais si vous prenez l’exemple des contraintes check qui ne sont pas « trusted » dans le cas de vues partitionnées il y a de très forte chances que l’optimiseur soit obligé de parcourir l’ensemble des tables de la vue même si un prédicat a été défini .. car il est incapable de dire avec exactitude si la table contient ou ne contient pas les données en fonction du prédicat …. Pour les contraintes de clé étrangère, cela peut poser des problèmes de performances dans le cas de vues et des opérations de jointure sous jacente.. Vous sélectionnez que les colonnes d’une table sous jacente mais sqlserver est obligé de parcourir les 2 tables concernées par la jointure au lieu d’une seule … Je suis en train de préparer un billet la dessus …

    >> Elsuket : Bien ton billet .. j’ai fait ressortir XXX contraintes qui n’étaient pas trusted .. je vais pouvoir alerter les développeurs :-)))))

    ++

  2. T’as une idee de l’impact en terme de performances?
    L’optimisateur de requetes ne va pas evaluer la table d’un INNER JOIN dans le cas ou un contrainte n’est pas respectee (comme verifier une valeur negative alors que le CHECK ne specifie que des valeurs positives). Mais ce sont des examples theoriques et ca ne devrait pas ou que rarement arrive; ou sinon c’est qu’il y a un probleme avec nos requetes
    Mais sur des operations normales, est ce que les perfs sont affectees d’une maniere ou d’un autre quand la contrainte est is_not_trusted a 1?

Laisser un commentaire