次の方法で共有

インライン ユーザー定義関数

  • [アーティクル]

インライン ユーザー定義関数は、table データ型を返すユーザー定義関数のサブセットです。インライン関数を使用すると、パラメータ化されたビューの機能を使用できるようになります。

次の例では、指定した地域の店舗名と都市が返されます。

USE AdventureWorks;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

このビューは、より汎用的で、表示した際に挿入した地域を指定できるようになれば、さらに優れたものになります。ただし、ビューでは、WHERE 句に指定した検索条件で、パラメータがサポートされません。インライン ユーザー定義関数を使用すると、WHERE 句に指定した検索条件でパラメータを使用できるようになります。次の例では、ユーザーがクエリで地域を指定できるインライン関数を作成します。

USE AdventureWorks;
GO
IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT S.Name AS Store, A.City
        FROM Sales.Store AS S
        JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
        JOIN Person.Address AS A ON A.AddressID = CA.AddressID
        JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
        WHERE SP.Name = @Region
       );
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington');
GO

インライン ユーザー定義関数のルール

インライン ユーザー定義関数のルールは、次のとおりです。

  • RETURNS 句には table 型のキーワードのみを含めます。戻り値の変数の形式を定義する必要はありません。RETURN 句の SELECT ステートメントの結果セットの形式により設定されます。
  • BEGIN と END で区切られた function_body はありません。
  • RETURN 句には、かっこで囲んだ SELECT ステートメントを 1 つ含めます。SELECT ステートメントの結果セットにより、関数から返されたテーブルが形成されます。インライン関数で使用する SELECT ステートメントには、ビューで使用する SELECT ステートメントと同じ制限が適用されます。
  • テーブル値関数では、定数または @local_variable 引数のみを受け取ります。

インライン関数とインデックス付きビュー

インライン関数を使用すると、インデックス付きビューの機能性を高めることもできます。インデックス付きビュー単体では、WHERE 句に指定した検索条件でパラメータを使用できないので、格納された結果セットを特定のユーザーに合わせて調整することができません。ただし、インデックス付きビューを定義してビューと一致するデータの完全なセットをビューに格納し、次にそのインデックス付きビューに対してパラメータ化された検索条件を含むインライン関数を定義すると、ユーザーに合わせて結果を調整することができるようになります。ビューの定義が複雑な場合、ほとんどの結果セットの構築作業では、集計の作成、またはビューへのクラスタ化インデックス作成時に複数のテーブルを結合する作業が伴います。その後、インデックス付きビューを参照するインライン関数を作成すると、その関数では、ユーザーのパラメータ化されたフィルタを適用して、インデックス付きビューの具体化された結果セットから特定の行を返すことができます。次に例を示します。

  1. 全店舗の四半期ごとに集約した売上データを表示する結果セットに、すべての売上データを集計する vw_QuarterlySales というビューを定義します。

  2. vw_QuarterlySales ビューにクラスタ化インデックスを作成して、集約されたデータを含む結果セットを具体化します。

  3. 次に示すように、集約されたデータをフィルタ選択するインライン関数を作成します。

    CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
     ( @StoreID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM SalesDB.dbo.vw_QuarterlySales
        WHERE StoreID = @StoreID
       )

  4. これで、ユーザーはインライン関数から次のように選択して、特定の店舗のデータを取得できるようになります。

    SELECT *
FROM fn_QuarterlySalesByStore(14432)

手順 4. で実行したクエリの条件を満たすために必要な作業の大部分は、四半期ごとの売上データを集計することです。この作業は、手順 2. で一度実行しています。手順 4. の各 SELECT ステートメントでは、fn_QuarterlySalesByStore という関数を使用して、各店舗固有の集計データをフィルタ選択しています。

参照

概念

ユーザー定義関数のデザイン ガイドライン
ユーザー定義テーブル値関数
決定的関数と非決定的関数
関数としてのストアド プロシージャの書き直し
インデックス付きビューの作成

ヘルプおよび情報

SQL Server 2005 の参考資料の入手