インライン ユーザー定義関数
インライン ユーザー定義関数は、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 句に指定した検索条件でパラメータを使用できないので、格納された結果セットを特定のユーザーに合わせて調整することができません。ただし、インデックス付きビューを定義してビューと一致するデータの完全なセットをビューに格納し、次にそのインデックス付きビューに対してパラメータ化された検索条件を含むインライン関数を定義すると、ユーザーに合わせて結果を調整することができるようになります。ビューの定義が複雑な場合、ほとんどの結果セットの構築作業では、集計の作成、またはビューへのクラスタ化インデックス作成時に複数のテーブルを結合する作業が伴います。その後、インデックス付きビューを参照するインライン関数を作成すると、その関数では、ユーザーのパラメータ化されたフィルタを適用して、インデックス付きビューの具体化された結果セットから特定の行を返すことができます。次に例を示します。
全店舗の四半期ごとに集約した売上データを表示する結果セットに、すべての売上データを集計する
vw_QuarterlySalesというビューを定義します。vw_QuarterlySalesビューにクラスタ化インデックスを作成して、集約されたデータを含む結果セットを具体化します。次に示すように、集約されたデータをフィルタ選択するインライン関数を作成します。
CREATE FUNCTION dbo.ufn_QuarterlySalesByStore ( @StoreID int ) RETURNS table AS RETURN ( SELECT * FROM SalesDB.dbo.vw_QuarterlySales WHERE StoreID = @StoreID )これで、ユーザーはインライン関数から次のように選択して、特定の店舗のデータを取得できるようになります。
SELECT * FROM fn_QuarterlySalesByStore(14432)
手順 4. で実行したクエリの条件を満たすために必要な作業の大部分は、四半期ごとの売上データを集計することです。この作業は、手順 2. で一度実行しています。手順 4. の各 SELECT ステートメントでは、fn_QuarterlySalesByStore という関数を使用して、各店舗固有の集計データをフィルタ選択しています。
参照
概念
ユーザー定義関数のデザイン ガイドライン
ユーザー定義テーブル値関数
決定的関数と非決定的関数
関数としてのストアド プロシージャの書き直し
インデックス付きビューの作成