Différence entre les fonctions scalaires, de valeur de table et d'agrégat dans SQL server?

Fonctions Scalaires

Les fonctions scalaires (parfois appelées fonctions définies par L'utilisateur / UDF) renvoient une seule valeur en tant que valeur de retour, et non en tant que jeu de résultats, et peuvent être utilisées dans la plupart des endroits d'une requête ou d'une instruction SET, à l'exception de la clause FROM (et peut-être d'autres endroits?). En outre, les fonctions scalaires peuvent être appelées via EXEC, tout comme les procédures stockées, bien qu'il n'y ait pas beaucoup d'occasions d'utiliser cette capacité (pour plus de détails sur cette capacité, veuillez voir ma réponse à la question suivante sur DBA.StackExchange: pourquoi les fonctions à valeur scalaire nécessitent une autorisation d'exécution plutôt que de sélection?). Ceux-ci peuvent être créés dans T-SQL et SQLCLR.

• T-SQL (UDF): ces fonctions scalaires sont généralement un problème de performance car elles s'exécutent généralement pour chaque ligne retournée (ou analysée) et interdisent toujours les plans d'exécution parallèles.

• SQLCLR (UDF): ces fonctions scalaires s'exécutent également généralement par ligne retourné ou scanné, mais il y a deux avantages importants par rapport aux UDF T-SQL:

  • à partir de SQL Server 2012, les valeurs de retour peuvent être repliées de manière constante dans le plan d'exécution si L'UDF ne fait pas d'accès aux données, et s'il est marqué IsDeterministic = true. Dans ce cas, la fonction ne s'exécuterait pas pour chaque ligne.
  • les fonctions scalaires SQLCLR peuvent fonctionner dans des plans parallèles ( ) si elles ne font pas d'accès à la base de données{[28].

Table-Valeur Fonctions

Les fonctions à valeur de Table (TVFs) renvoient des ensembles de résultats et peuvent être utilisées dans une clause FROM, JOIN ou CROSS APPLY / OUTER APPLY de toute requête, mais contrairement aux vues simples, ne peut pas être la cible d'instructions DML(INSERT / UPDATE / DELETE). Ceux-ci peuvent également être créés dans T-SQL et SQLCLR.

• T-SQL MultiStatement (TVF): ces TVF, comme leur nom l'indique, peuvent avoir plusieurs instructions, similaires à une procédure stockée. Quels que soient les résultats qu'ils vont revenir sont stockés dans une Variable de Table et retournés à la toute fin; ce qui signifie que rien n'est retourné jusqu'à ce que la fonction soit terminée. Le nombre estimé de lignes qu'ils retourneront, tel que rapporté à L'Optimiseur de requêtes (ce qui affecte le plan d'exécution) dépend de la version de SQL Server:

  • avant SQL Server 2014: ceux-ci rapportent toujours 1 (Oui, juste 1) ligne.
  • SQL Server 2014 et 2016: ceux-ci rapportent toujours 100 lignes.
  • démarrage de SQL Server 2017: La valeur par défaut est reportez 100 lignes, mais dans certaines conditions, le nombre de lignes sera assez précis (basé sur les statistiques actuelles) grâce à la nouvelle fonctionnalitéexécution entrelacée .

• T-SQL Inline (iTVF): ces TVF ne peuvent jamais être qu'une seule instruction, et cette instruction est une requête complète, tout comme une vue. Et en fait, les TVF en ligne sont essentiellement une vue qui accepte les paramètres d'entrée à utiliser dans la requête. Ils ne mettent pas non plus en cache leur propre plan de requête la définition est placée dans la requête dans laquelle ils sont utilisés (contrairement aux autres objets décrits ici), ils peuvent donc être optimisés beaucoup mieux que les autres types de TVFs ( ). Ces TVF fonctionnent assez bien et sont préférés si la logique peut être gérée dans une seule requête.

• SQLCLR (TVF): ces TVF sont similaires à T-SQL MultiStatement TVFs en ce sens qu'ils accumulent l'ensemble des résultats en mémoire (même s'il s'agit d'un fichier swap / page) avant de tout libérer au très fin. Le nombre estimé de lignes qu'ils retourneront, tel que rapporté à L'Optimiseur de requêtes (qui affecte le plan d'exécution) est toujours de 1000 lignes. Étant donné qu'un nombre de lignes fixe est loin d'être idéal, Veuillez soutenir ma demande pour permettre de spécifier le nombre de lignes: Autoriser TVFs (T-SQL et SQLCLR) à fournir des estimations de lignes définies par l'utilisateur à l'optimiseur de requête

• SQLCLR Streaming (sTVF): ces TVF permettent un C# complexe / VB.NET code comme SQLCLR régulier TVFs, mais sont spéciaux en ce sens qu'ils renvoient chaque ligne à la requête appelante au fur et à mesure qu'ils sont générés ( ). Ce modèle permet à la requête appelante de commencer à traiter les résultats dès que le premier est envoyé, de sorte que la requête n'a pas besoin d'attendre que tout le processus de la fonction se termine avant de voir des résultats. Et cela nécessite moins de mémoire puisque les résultats ne sont pas stockés en mémoire jusqu'à la fin du processus. Le nombre estimé de lignes qu'ils retourneront, tel que rapporté à la requête Optimizer (qui affecte le plan d'exécution) est toujours 1000 lignes. Étant donné qu'un nombre de lignes fixe est loin d'être idéal, Veuillez soutenir ma demande pour permettre de spécifier le nombre de lignes: Autoriser TVFs (T-SQL et SQLCLR) à fournir des estimations de lignes définies par l'utilisateur à l'optimiseur de requête

Fonctions D'Agrégation

Agrégats Définis par l'Utilisateur (UDA) sont des agrégats semblables à SUM(), COUNT(), MIN(), MAX(), etc. et nécessitent généralement une clause GROUP BY. Ceux-ci ne peuvent être créé dans SQLCLR, et cette capacité a été introduite dans SQL Server 2005. En outre, à partir de SQL Server 2008, les UDAs ont été améliorés pour permettre plusieurs paramètres d'entrée (). Une lacune particulière est qu'il n'y a aucune connaissance de l'ordre des lignes au sein du groupe, donc créer un total en cours d'exécution, ce qui serait relativement facile si l'ordre pouvait être garanti, n'est pas possible dans un assemblage SAFE.

Voir Aussi:

• CRÉER UNE FONCTION (MSDN la documentation)
• créer un agrégat (documentation MSDN)
• exemple de fonction à valeur de table CLR avec Streaming Complet (STVF / TVF) (article que j'ai écrit)