Partage via

Fonctions définies par l’utilisateur

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceEndpoint SQL analytics dans Microsoft FabricEntrepôt dans Microsoft FabricBase de données SQL dans Microsoft Fabric

À l’instar des fonctions dans les langages de programmation, les fonctions SQL Server définies par l’utilisateur sont des routines qui acceptent des paramètres, exécutent une action, par exemple un calcul complexe, et renvoient le résultat de cette action sous forme de valeur. La valeur renvoyée peut être une valeur scalaire unique ou un jeu de résultats.

Avantages des fonctions définies par l’utilisateur

Pourquoi utiliser des fonctions définies par l’utilisateur (UDF) ?

  • Programmation modulaire. Vous pouvez créer la fonction une fois, la stocker dans la base de données et l’appeler autant de fois que vous le voulez dans votre programme. Les fonctions définies par l’utilisateur sont modifiables indépendamment du code source du programme.

  • Exécution plus rapide. De la même façon que les procédures stockées, les fonctions Transact-SQL définies par l’utilisateur réduisent le coût de compilation du code Transact-SQL en mettant en cache les plans pour les réutiliser au cours d’exécutions répétées. Cela signifie que la fonction définie par l’utilisateur n’a pas besoin d’être réparée et réoptimisée avec chaque utilisation, ce qui entraîne des temps d’exécution plus rapides.

    Les fonctions CLR (Common Language Runtime) offrent des avantages considérables en matière de performances par rapport aux fonctions Transact-SQL pour ce qui concerne les tâches de calcul, la manipulation de chaînes et la logique métier. Les fonctions Transact-SQL sont plus adaptées à une logique intensive d’accès aux données.

  • Réduction du trafic réseau. Une opération filtrant des données en fonction d’une contrainte complexe qui ne peut pas être exprimée dans une même expression scalaire peut être exprimée sous forme de fonction. La fonction peut ensuite être appelée dans la clause WHERE pour réduire le nombre de lignes envoyées au client.

Importante

Les fonctions Transact-SQL définies par l’utilisateur (UDF) figurant dans les requêtes ne peuvent être exécutées que sur un seul thread (plan d’exécution en série). Par conséquent, l’utilisation de fonctions UDF empêche le traitement de requêtes en parallèle. Pour plus d’informations sur le traitement de requêtes en parallèle, consultez le Guide d’architecture de traitement des requêtes.

Types de fonctions

Cette section décrit les différences entre les fonctions scalaires, les fonctions table et les fonctions système.

Fonctions scalaires

Les fonctions scalaires définies par l’utilisateur retournent une valeur de donnée unique dont le type est défini dans la clause RETURNS. Pour une fonction scalaire incluse, la valeur scalaire retournée est le résultat d’une instruction unique. Le corps d’une fonction scalaire à instructions multiples peut comporter une série d’instructions Transact-SQL qui renvoient la valeur unique. Le type de retour peut être n’importe quel type de données, sauf text, ntext, image, cursor et timestamp. Pour obtenir des exemples, consultez Créer des fonctions définies par l’utilisateur (moteur de base de données).

Fonctions table

Les fonctions table définies par l’utilisateur (TVF) renvoient un type de données table . Une fonction table inline ne comporte pas de corps ; la table est le jeu de résultats d’une instruction SELECT unique. Pour obtenir des exemples, consultez Créer des fonctions définies par l’utilisateur (moteur de base de données).

Fonctions système

SQL Server fournit de nombreuses fonctions système vous permettant d’effectuer diverses opérations. Elles ne peuvent pas être modifiées. Pour en savoir plus, consultez En quoi consistent les fonctions de base de données SQL ?, Fonctions système par catégorie pour Transact-SQL et Vues de gestion dynamique du système.

Consignes

Les erreurs Transact-SQL qui provoquent l’annulation d’une instruction et continuent avec l’instruction suivante dans le module (comme les déclencheurs ou les procédures stockées) sont traitées différemment dans une fonction. Dans les fonctions, ces erreurs provoquent l’arrêt de l’exécution de la fonction, lequel provoque à son tour l’annulation de l’instruction qui a invoqué la fonction.

Les instructions contenues dans un bloc BEGIN...END ne peuvent pas avoir d’effets secondaires. Les effets secondaires d’une fonction sont toutes les modifications définitives de l’état d’une ressource dont la portée s’étend hors de la fonction, comme la modification d’une table de base de données. Les instructions d’une fonction ne peuvent modifier que les objets locaux de cette fonction, comme les variables ou les curseurs locaux. Les modifications apportées aux tables de base de données, aux opérations sur les curseurs qui ne sont pas locaux à la fonction, telles que l’envoi de courrier électronique, la tentative de modification du catalogue et la génération d’un jeu de résultats retourné à l’utilisateur, sont des exemples d’actions qui ne peuvent pas être effectuées dans une fonction.

Si une instruction CREATE FUNCTION produit des effets secondaires sur des ressources qui n’existent pas au moment où l’instruction CREATE FUNCTION est émise, SQL Server exécute l’instruction. Toutefois, SQL Server n’exécute pas la fonction lorsqu’elle est appelée.

Le nombre d’exécutions d’une fonction spécifiée dans une requête peut varier d’un plan d’exécution de l’optimiseur à l’autre. C’est par exemple le cas d’une fonction appelée par une sous-requête dans une clause WHERE. Le nombre d’exécutions de la sous-requête et de sa fonction peut varier en fonction du chemin d’accès choisi par l’optimiseur.

Les fonctions déterministes doivent être liées au schéma. Utilisez la clause SCHEMABINDING lors de la création d’une fonction déterministe.

Pour en savoir plus sur les fonctions définies par l’utilisateur et découvrir les considérations relatives aux performances, consultez Créer des fonctions définies par l’utilisateur (moteur de base de données).

Instructions valides dans une fonction

Les types d’instructions valides dans une fonction sont les suivants :

  • Les instructions DECLARE permettent de définir des variables de données et des curseurs locaux de la fonction.

  • Les affectations de valeurs à des objets locaux de la fonction, comme l’attribution de valeurs à des variables locales scalaires ou de table à l’aide de SET.

  • les opérations de curseur faisant référence à des curseurs locaux déclarés, ouverts, fermés et désalloués dans la fonction. Les instructions FETCH renvoyant des données au client ne sont pas autorisées. Seules les instructions FETCH qui affectent des valeurs à des variables locales à l’aide de la clause INTO sont autorisées.

  • Les instructions de contrôle de flux, à l’exception des instructions TRY...CATCH.

  • Les instructions SELECT contenant des listes de sélection avec des expressions qui affectent des valeurs à des variables locales de la fonction.

  • Les instructions UPDATE, INSERT et DELETE modifiant les variables table locales de la fonction.

  • Les instructions EXECUTE appelant une procédure stockée étendue.

Fonctions système intégrées

Les fonctions intégrées non déterministes suivantes peuvent être utilisées dans les fonctions Transact-SQL définies par l’utilisateur.

  • CURRENT_TIMESTAMP
  • GET_TRANSMISSION_STATUS
  • GETDATE
  • GETUTCDATE
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE

Les fonctions intégrées non déterministes suivantes ne peuvent pas être utilisées dans une Transact-SQL fonction définie par l’utilisateur (UDF).

  • NEWID
  • NEWSEQUENTIALID
  • RAND
  • TEXTPTR

Si vous référencez l’une de ces fonctions à l’intérieur d’une fonction UDF, vous obtenez l’erreur suivante :

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

Pour obtenir la liste des fonctions système intégrées déterministes et non déterministes, consultez Fonctions déterministes et non déterministes.

Fonctions liées à un schéma

CREATE FUNCTION prend en charge une clause SCHEMABINDING qui lie la fonction au schéma de tout objet auquel elle fait référence, comme les tables, les vues ou d’autres fonctions définies par l’utilisateur. Toute tentative de modification (ALTER) ou de suppression (DROP) d’un objet référencé par une fonction liée au schéma est vouée à l’échec.

Les conditions suivantes doivent être respectées pour que la clause SCHEMABINDING puisse être spécifiée dans CREATE FUNCTION :

  • Toutes les vues et fonctions définies par l’utilisateur référencées par la fonction doivent être liées au schéma.

  • Tous les objets référencés par la fonction doivent figurer dans la même base de données que la fonction. Les objets doivent être référencés à l’aide de noms à une ou deux composantes.

  • Vous devez disposer de l’autorisation REFERENCES sur tous les objets (tables, vues et fonctions définies par l’utilisateur) référencés dans la fonction.

Vous pouvez utiliser l’instruction ALTER FUNCTION pour supprimer la liaison au schéma. L’instruction ALTER FUNCTION doit redéfinir la fonction sans spécifier WITH SCHEMABINDING.

Spécifier des paramètres

Une fonction définie par l’utilisateur accepte ou n’accepte pas de paramètres d’entrée et retourne une valeur scalaire ou une table. Une fonction peut comprendre jusqu’à 1 024 paramètres d’entrée. Lorsqu’un paramètre de la fonction possède une valeur par défaut, le mot clé DEFAULT doit être spécifié lors de l’appel de la fonction afin d’obtenir la valeur par défaut. Ce comportement est différent de celui des paramètres avec valeurs par défaut des procédures stockées définies par l’utilisateur pour lesquelles l’omission du paramètre implique également la prise en compte de la valeur par défaut. Les fonctions définies par l’utilisateur ne prennent pas en charge les paramètres de sortie.

Contenu associé

  • Last updated on