> Tech > Conclusions

Conclusions

Tech - Par iTPro - Publié le 24 juin 2010
email

L'expression de table CTE peut simplifier l'écriture de requêtes complexes. Les requêtes récursives ne doivent être employées que lorsque la récursivité apparaît comme la seule solution. Si vous faites une erreur dans l'écriture de votre requête récursive, n'ayez pas peur, par défaut le nombre de cycles de récursion est limité

Conclusions

à 100. Vous pouvez outrepasser cette limite en fixant vous-même la valeur à l’aide de la clause OPTION (MAXRECURSION n), qui doit figurer en dernier dans la requête.

Enfin, sachez que la norme SQL:1999 propose des compléments syntaxiques pour piloter votre SQL récursif. Par exemple vous pouvez naviguer dans les données DEPTH FIRST ou BREADTH FIRST (en profondeur ou en largeur en premier lieu) et aussi constituer une colonne contenant toutes les données des étapes intermédiaires dans un tableau de ligne (ARRAY of ROW) dont la taille doit être "suffisante" pour couvrir tous les cas de figure. Voici la syntaxe complète de la CTE avec récursivité :

WITH [ RECURSIVE ] [ ( <liste_colonne> ) ] AS ( <requete_select> ) [ <clause_cycle_recherche> ] with : <clause_cycle_recherche> ::= <clause_recherche> | <clause_cycle> | <clause_recherche> <clause_cycle> and : <clause_recherche> ::= SEARCH { DEPTH FIRTS BY | BREADTH FIRST BY } <liste_specification_ordre> SET <colonne_sequence> <clause_cycle> ::= CYCLE <colonne_cycle1> [ { , <colonne_cycle2> } … ] SET <colonne_marquage_cycle> TO <valeur_marque_cycle> DEFAULT <valeur_marque_non_cycle> USING <colonne_chemin>

En bonus (CTE, requête récursive appliquée)

Voici une procédure stockée qui donne l’ordre exact des DELETE à effectuer dans des tables afin de vider une table de ses données, lorsque cette dernière est liée par l’intégrité référentielle :

CREATE PROCEDURE P_WHAT_TO_DELETE_BEFORE
@TABLE_TO_DELETE VARCHAR(128), — targettable to delete
@DB VARCHAR(128), — target database
@USR VARCHAR(128) — target schema (dbo in most cases)
AS

WITH

T_CONTRAINTES (table_name, father_table_name)
AS (SELECT DISTINCT CTU.TABLE_NAME, TCT.TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RFC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU
ON RFC.CONSTRAINT_CATALOG = CTU.CONSTRAINT_CATALOG
AND RFC.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA
AND RFC.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
ON RFC.UNIQUE_CONSTRAINT_CATALOG = TCT.CONSTRAINT_CATALOG
AND RFC.UNIQUE_CONSTRAINT_SCHEMA = TCT.CONSTRAINT_SCHEMA
AND RFC.UNIQUE_CONSTRAINT_NAME = TCT.CONSTRAINT_NAME
WHERE CTU.TABLE_CATALOG = @DB AND CTU.TABLE_SCHEMA = @USR),
T_TREE_CONTRAINTES (table_to_delete, level)
AS (SELECT DISTINCT table_name, 0
FROM T_CONTRAINTES WHERE father_table_name = @TABLE_TO_DELETE
UNION ALL SELECT priorT.table_name, level – 1
FROM T_CONTRAINTES priorT
INNER JOIN T_TREE_CONTRAINTES
beginT ON beginT.table_to_delete = priorT.father_table_name
WHERE priorT.father_table_name <> priorT.table_name)
SELECT DISTINCT * FROM T_TREE_CONTRAINTES
ORDER BY level

GO

Le cas d’auto référence est en principe intégré. Les paramètres sont :
@DB : nom de la base,
@USR : nom du schema, par défaut dbo,
@TABLE_TO_DELETE : nom de la table que vous voulez vider

Téléchargez gratuitement cette ressource

Protection des Données : 10 Best Practices

Protection des Données : 10 Best Practices

Le TOP 10 des meilleures pratiques, processus et solutions de sécurité pour mettre en œuvre une protection efficace des données et limiter au maximum les répercutions d’une violation de données.

Tech - Par iTPro - Publié le 24 juin 2010