[解決済み】複数ステートメントのテーブル型関数とインラインのテーブル型関数の比較

質問

念のため、いくつか例を挙げておきます。

インライン・テーブル・バリュー

CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS 
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
    FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
        ON a.SaleId = b.SaleId
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.ShipDate IS NULL
GO

マルチステートメント・テーブル値

CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID    INT         NOT NULL,
CustomerID      INT         NOT NULL,
OrderDate       DATETIME    NOT NULL,
OrderQty        INT         NOT NULL)
AS
BEGIN
    DECLARE @MaxDate DATETIME

    SELECT @MaxDate = MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID

    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 = @MaxDate
        AND a.CustomerID = @CustomerID
    RETURN
END
GO

一方のタイプ（インラインまたはマルチステートメント）を使用することで、他方よりも有利になることはありますか？それとも、純粋に構文上の違いなのでしょうか？2つの例のクエリが異なることを行っていることは承知していますが、そのように書く理由があるのでしょうか？

読んでいて、利点や違いがあまり説明されていない。

どのように解決するのか？

Mattのコメントを調査する中で、私は当初の発言を修正しました。彼の言う通り、インラインテーブル値関数（ITVF）とマルチステートメントテーブル値関数（MSTVF）は、どちらも単にSELECT文を実行するだけであっても、パフォーマンスに差が生じます。SQL Serverは、ITVFをまるで VIEW は、該当するテーブルの最新の統計情報を使用して実行計画を計算します。MSTVFは、SELECT文の内容全体をテーブル変数に詰め込んで、それに結合することと同じです。したがって、コンパイラはMSTVF内のテーブルの統計情報を一切使用することができません。したがって、すべての条件が同じであれば（同じになることはほとんどないが）、ITVFの方がMSTVFよりも性能が良くなる。私のテストでは、完了時間における性能差はごくわずかだったが、統計の観点からは顕著であった。

あなたの場合、この2つの関数は機能的に同等ではありません。MSTV関数は、呼び出されるたびに余分なクエリーを実行し、最も重要なのは、顧客IDでフィルタリングすることです。大規模なクエリでは、渡されたcustomerIdごとに関数を呼び出す必要があるため、オプティマイザは他のタイプの結合を利用することができないでしょう。しかし、MSTV関数をこのように書き直した場合。

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

クエリでは、オプティマイザはその関数を一度呼び出すだけで、より良い実行プランを構築することができるだろう。 VIEW .

ITVFは、可能な限りMSTVFよりも優先されるべきです。なぜなら、テーブルの列からデータ型、Nullability、照合順序が、マルチステートメントのテーブル値関数でそれらのプロパティを宣言するのに対し、ITVFからはより良い実行プランが得られるからです。私の経験では、ITVFがVIEWよりも良いオプションである多くの状況を発見したことはありませんが、マイル数は異なる場合があります。

Mattに感謝します。

追加

最近この話題が出たので、Wayne Sheffieldが行った、インラインテーブル値関数とマルチステートメント関数のパフォーマンス差を比較した素晴らしい分析を紹介します。

彼のブログのオリジナル記事です。

SQL Server Centralにコピーする