インライン ユーザー定義関数
インライン ユーザー定義関数は、table データ型を返すユーザー定義関数のサブセットです。インライン関数を使用すると、パラメーター化されたビューの機能を使用できるようになります。
次の例では、指定した地域の店舗名と都市が返されます。
USE AdventureWorks2008R2;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
JOIN Sales.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = S.BusinessEntityID
JOIN Person.Address AS A ON A.AddressID = BEA.AddressID
JOIN Person.StateProvince SP ON
SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO
このビューは、より汎用的で、表示した際に挿入した地域を指定できるようになれば、さらに優れたものになります。ただし、ビューでは、WHERE 句に指定した検索条件で、パラメーターがサポートされません。インライン ユーザー定義関数を使用すると、WHERE 句に指定した検索条件でパラメーターを使用できるようになります。次の例では、ユーザーがクエリで地域を指定できるインライン関数を作成します。
USE AdventureWorks2008R2;
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
INNER JOIN Person.BusinessEntityAddress AS bea
ON bea.BusinessEntityID = s.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = bea.AddressID
INNER JOIN Person.StateProvince AS 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')
ORDER BY City;
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 という関数を使用して、各店舗固有の集計データをフィルター選択しています。