Multi-déclaration de la Fonction à valeur de Table vs Inline Fonction à valeur de Table

Dans la recherche de Matt commentaire, j'ai révisé mon original de la déclaration. Il est correct, il y aura une différence de performances entre une ligne de la fonction table (ITVF) et un multi-déclaration de la fonction table (MSTVF) même si ils simplement exécuter une instruction SELECT. SQL Server traiter une ITVF un peu comme un VIEW en ce qu'elle permettra de calculer un plan d'exécution en utilisant les statistiques les plus récentes sur les tableaux en question. Un MSTVF est équivalent à la farce de la totalité du contenu de votre instruction SELECT dans une table variable, puis de rejoindre. Ainsi, le compilateur ne peut pas utiliser les statistiques de la table sur les tables de la MSTVF. Donc, toutes choses étant égales par ailleurs, (ce qui arrive rarement), l'ITVF sera mieux que la MSTVF. Dans mes tests, la différence de performances en temps de réalisation a été négligeable, cependant, d'un point de vue statistique, il a été perceptible.

Dans votre cas, les deux fonctions ne sont pas fonctionnellement équivalent. La ZONE de fonction ne une requête supplémentaire à chaque fois qu'il est appelé et, plus important encore, les filtres sur l'id de client. Dans une grande requête, l'optimiseur ne serait pas en mesure de tirer parti d'autres types de jointures, comme il serait nécessaire d'appeler la fonction pour chaque customerId passé. Toutefois, si vous ré-écrit votre ZONE de fonction comme ceci:

CREATE FUNCTION MyNS.GetLastShipped()
RETURNS @CustomerOrder TABLE
    (
    SaleOrderID    INT         NOT NULL,
    CustomerID      INT         NOT NULL,
    OrderDate       DATETIME    NOT NULL,
    OrderQty        INT         NOT NULL
    )
AS
BEGIN
    INSERT @CustomerOrder
    SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
    FROM Sales.SalesOrderHeader a 
        INNER JOIN Sales.SalesOrderHeader b
            ON a.SalesOrderID = b.SalesOrderID
        INNER JOIN Production.Product c 
            ON b.ProductID = c.ProductID
    WHERE a.OrderDate = (
                        Select Max(SH1.OrderDate)
                        FROM Sales.SalesOrderHeader As SH1
                        WHERE SH1.CustomerID = A.CustomerId
                        )
    RETURN
END
GO

Dans une requête, l'optimiseur va être en mesure d'appeler cette fonction à la fois et de construire un meilleur plan d'exécution, mais il ne serait pas encore mieux qu'un équivalent, non paramétrée ITVS ou un VIEW.

ITVFs doit être préférée à une MSTVFs lorsque cela est possible parce que les types de données, la possibilité de valeur null et classement dans les colonnes de la table alors que vous déclarez ces propriétés dans un environnement multi-déclaration de la fonction à valeur de table et, surtout, vous obtiendrez de meilleurs plans d'exécution de l'ITVF. Dans mon expérience, je n'ai pas trouvé de nombreuses circonstances où un ITVF était une meilleure option que d'un point de VUE, mais le kilométrage peut varier.

Merci à Matt.

Plus

Depuis que j'ai vu ce sont en place récemment, voici une excellente analyse faite par Wayne Sheffield en comparant la différence de performances entre Inline fonctions à valeur de Table et Multi-Déclaration de fonctions.

Son blog original.

Copie sur SQL Serveur Central

OriginalL'auteur Thomas

Il y a une autre différence. Une fonction table en ligne peuvent être insérées, mises à jour et supprimées de - tout comme d'un point de vue. Des restrictions similaires s'appliquent - impossible de mettre à jour les fonctions à l'aide des agrégats, impossible de mettre à jour les colonnes calculées, et ainsi de suite.

OriginalL'auteur Craig Beere

regarder La comparaison en ligne et Multi-Déclaration des Fonctions Table vous pouvez trouver de bonnes descriptions et des tests de performances,

OriginalL'auteur hmfarimani

Un autre cas d'utilisation d'une ligne multi fonction serait de contourner sql server à partir de pousser vers le bas la clause where.

Par exemple, j'ai une table avec une table des noms et certains noms de table sont formatés comme C05_2019 et C12_2018 et, et tous les tableaux formatés qui ont le même schéma. Je voulais fusionner toutes ces données dans un tableau et d'analyser les 05 et 12 pour un CompNo colonne et 2018,2019 dans une colonne d'année. Cependant, il y a d'autres tables comme ACA_StupidTable que je ne peux pas extraire CompNo et CompYr et obtenez une erreur de conversion si j'ai essayé. Donc, ma question est en deux partie, une requête interne qui a retourné seulement les tableaux formatés comme 'C_______"alors que l'extérieur de la requête n'a une sous-chaîne et int de conversion. ie Cast(Substring(2, 2) comme int) CompNo. Tout a l'air bon, sauf que sql server a décidé de mettre ma fonction de distribution avant que les résultats ont été filtrés et si je reçois un esprit de brouillage erreur de conversion. Un multi déclaration de la table de fonction peut empêcher cela de se produire, puisque, fondamentalement, c'est un "nouveau" de la table.

OriginalL'auteur William Egge