Execution Context
Le contexte d'execution (droit/credentials relatif à l'utilisateur SQL connecté) n'est pas un élément à prendre à la légère.
En effet, losrqu'une vue est basée sur une fonction, le résultat dependra fortement de l'application du contexte d'exécution. Dans certains cas de cascade d'appel avec restrictions/droits diverses, le résultat peut même apparaître imprévisible.
Cependant, il existe des cas où le contexte d'exécution (et sa modification) peut représenter un avantage de grande valeur.
Il est possible de modifier le contexte d'exécution d'une fonction en utilisant la syntaxe WITH EXECUTE AS 'UserToUseForContext' lors de la creation de la fonction.
Ainsi, un utilisateur X ne pouvant pas accéder à la table PRODUCT pourrait en lire le contenu via une Inline table-valued fonction ReadProduct( @Parameter ) utilisant un WITH EXECUTE AS 'AnUserAbleToReadTheProductTable'.
Il va de soit que l'utilisateur X doit disposer du droit d'exécution sur la fonction ReadProduct().
Pour plus d'info, voir pg 9-20 du syllabus.
Exemple
CREATE FUNCTION GetOrders RETURNS TABLE
WITH EXECUTE AS 'Pat'
AS
RETURN ( Select * from Sales.Orders )
Views et fonction
Comme l'on s'en doute, il est possible de construire une vue sur le resultat d'une fonction. Si la vue est fortement solicitée, il pourrait même être opportun d'y placer un index persistant.
Pour plus d'information, voir l'article sur les Views et Stored Proc
Indexation de function
Comme déja précisé par le passé, il est possible d'indexer une VIEW.
Cependant, certaines vues peuvent être produite sur base du résultat d'une fonction. Dans ce cas, l'indexation d'une telle vue doit répondre à des conditions complémentaires.
En effet, il ne sera possible d'établir un index sur une fonction non determisniste.
Une fonction non deterministe peu retourner des valeurs differentes pour des paramètres identiques (ex: utilisation de fonction Rand, Convert, cast, checksum ou bien une fonction dépendant de l'état de la base de donnée).
Plus d'information disponible dans le sylabus à la section 9-15 "Deterministic and Nondeterministic Functions".
Converttir une stored Proc en fonction
Lorsque cela est possible, il est préférable de convertir les stored procedure en fonction. La raison principale est l'amélioration des performances.
Ainsi, les table-valued functions pourront être utilisées pour remplacer des stored procedures dans les cas suivants:
- Utilisation de SELECT avec paramètre.
- Pas opération d'UPDATE
- Pas d'execution dynamique de requête SQL.
- Utilisation de table temporaire pour résultats intermédiaire.
Les table-valued functions peuvent également être utilisées pour convertir des stored procedures utilisant des cursor.
Lorsque cela est possible, cette conversion permet de diviser le temps d'exécution jusqu'a 30 fois.
A cette fin, les exemples du cours inclus des fichiers spécialements dédiés à ce sujet.
Voir répertoire /Module XX - Extras/03_Getting_rid_of_Cursors/
A noter que l'un des rares cas ou les curseurs restent les plus performant est l'aggregation (sum, average, max, ...) sur de très très larges tables.
Creation de Foreign Key avec des fonctions
Comme déjà précisé, il n'est pas possible de construire une Foreign Key constraint si la destination n'est pas une clé primaire.
Hors, il existe des cas ou les occurences de la destination d'une Foreign Key puissent être multiples.
Dans ce cas, l'on utilise une fonction.
Etape 1:
Créer une fonction avec SCHEMABIDING qui, par exemple compte le nombre d'occurences dans la table de destination.FUNCTION CountEmployeesOf( @IDManager ) returns Int
WITH SCHEMABINDING
AS
Declare @RetValue int
Select @RetValue = count(*) from employee where employee.Manager = @IDManager
if (@retValue is NULL)
Select @RetValue = 0
Return @RetValue
end
Etape 2:
Créer un CHECK CONSTRAINT faisant office de vérification ForeignKey.Le CHECK CONSTRAINT utilisera la fonction countEmployeesOf( ... ) > 0 pour s'assurer que la condition est bien respectée.
Aucun commentaire:
Enregistrer un commentaire