ビューとストアドプロシージャの強力な代替手段「ユーザー定義関数」

はじめに

　ビューやストアドプロシージャを使用すれば、Transact-SQL（T-SQL）コードを適切にモジュール化したり、切り離したりすることができます。しかし、それ以上のことを望んだことはありませんか？ 例えば、SELECT文の中でパラメータドリブンのビューやストアドプロシージャを使えたらいいのに、と思ったことはないでしょうか。SQL Serverには、あまり目立たないながら検討に値するビューとストアドプロシージャの代替手段があります。その代替手段とは、テーブル値ユーザー定義関数（UDF）です。テーブル値UDFは、ビューとストアドプロシージャの重要な機能をすべて備えているだけでなく、ビューとストアドプロシージャにはない別の機能も備えています。

　例えば、私の開発チームでは、テーブル値UDFを使って、旧式のASPビジネスインテリジェンスアプリケーションに、新しいフィルタリングのレイヤを追加しました。SELECT文のFROM句内のテーブルの代わりにパラメータドリブンUDFを使用するだけで、核のT-SQLの大部分は変更せずに済みました。さらに、「検索と置換」を使用することで、ほとんどの変更を自動化することもできました。

　本稿では、SQL Server 2005に付属している「AdventureWorks」サンプルデータベースを修正したサンプルを使って、T-SQLコード内でテーブル値UDFを効果的に使用する方法について説明します。「AdventureWorks」データベースに格納されているUDFを確認するには、Management Studioを使って、図1に示すデータベース内の領域にナビゲートします。

図1　「AdventureWorks」データベース内のUDFの位置

　すべてのUDFは、ストアドプロシージャやビューと同様にT-SQLで記述され、ストアドプロシージャと同様にパラメータ値を持ちます。UDFを定義するには、CREATE FUNCTION文を使用します。

ユーザー定義関数の定義

　ユーザー定義関数には、次の2種類があります。

• スカラ値UDF
ほとんど場合、単一のデータ型を返すことができます。
• テーブル値UDF
複数の行から成る一時テーブルを返します。

　スカラUDFは、DML文（INSERT、UPDATE、SELECTなど）またはT-SQL文の内部で使用できます。テーブル値UDFにはいくつかの制限があり、一般にはSELECT文のFROM句で使用します。本稿では、テーブル値UDFの使用に焦点を当てます。

テーブル値UDFの概要

　テーブル値UDFには、次の2種類があります。

• インラインテーブル値関数
TABLEデータ型を返します。各関数は、1つのT-SQL文で構成されます。
• 複数ステートメントテーブル値関数
定義されたテーブルを返します。関数は、複数のT-SQL文で構成されます。

　どちらのテーブル値UDFも、単一の結果セットを返します。インラインテーブル値UDFの定義の例を以下に示します。

CREATE FUNCTION dbo.TestInlineFunctionName
(

)
RETURNS TABLE
AS
RETURN
(
   SELECT 0 as RetVal,* from [Person].[Contact]
)

　複数ステートメントUDFはインラインUDFに似ていますが、大きな違いが1つあります。それは、次のサンプルコードに示すように、RETURNSディレクティブの後にテーブル定義が続くことです。

RETURNS @retContactInformation TABLE
(
   -- Columns returned by the function
   [ContactID] int PRIMARY KEY NOT NULL,
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL,
   [JobTitle] [nvarchar](50) NULL,
   [ContactType] [nvarchar](50) NULL
)

　複数ステートメントUDFには複数のSELECT文（他のT-SQL文も同様）を指定できるため、次のサンプルコードのようにして、返すテーブルに明示的にデータを割り当てる必要があります。

INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

　複数ステートメントテーブル値UDFを使用すると、返されるテーブルの内容を変更するといった処理を行うことができます。例えば、次のコードは、複数ステートメントUDFの中で完全に有効です。

UPDATE @retContactInformation SET [JobTitle] = ’None’

　テーブル値関数を理解できたところで、これをビューとストアドプロシージャに置き換えて使用する方法について説明しましょう。

ビューの代替手段として利用する

　テーブル値UDFは、パラメータドリブンのビューと非常によく似た動作をします。そのため、テーブルドリブンUDFを使用すると、ビューの柔軟性に加えて、パラメータによってデータをフィルタリングすることができ、新たなセキュリティレイヤとして利用することができます。ビューと同様に、テーブル値UDFを実行するにはパーミッションが必要であるため、テーブル値UDFは一種のセキュリティメカニズムとして使用できます。

　以下のコードは、SELECT文内でのテーブル値UDFの使い方を示しています。

select * from ufnGetContactInformation_MDY(1209)

　SELECT文のFROM句の中で、ビューの代わりにテーブル値UDFを使用し、パラメータも指定していることに注意してください。サンプルコードではパラメータがハードコーディングされていますが、このテーブル値UDFに変数を渡すこともできます。

　テーブル値UDFを複数のビューの代わりに使用して、結果セットをフィルタリングすることもできます。例えば、テーブル値UDFの中でIF/ELSE文を使用して、異なるパラメータ値をフィルタリングできます。前述の開発シナリオの場合、ビューの代わりにテーブル値UDFを使用しなければ、すべてのストアドプロシージャにIF/ELSE文を指定しなければなりません。データベースに含まれるストアドプロシージャの数は50を超えるため、コーディングの量は膨大です。テーブル値UDFを使用すれば、IF/ELSE文の指定を5つの関数に限定できます。

　また、ビューと同様に、テーブル値関数の中ではORDER BY文は無効です。

　さらに、後述するとおり、テーブル値UDFはストアドプロシージャのほとんどの機能を備えています。

ストアドプロシージャの代替手段として利用する

　テーブル値UDFは、機能的にはビューに似ていますが、構造的にはストアドプロシージャに似ています。ストアドプロシージャと同様、テーブル値UDFは1つ以上のT-SQL文と最大1,024個のパラメータから成ります。また、ストアドプロシージャと同様に、変数を宣言し、他の関数を使用することができます（これには制限がありますが、その制限については後述します）。

　ストアドプロシージャと同様、テーブル値UDFでは、次の再帰的なサンプルに示すように共通のテーブル式を使用できます。

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
   SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
   FROM HumanResources.Employee
   WHERE ManagerID IS NULL
   UNION ALL
   SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
   FROM HumanResources.Employee e
      INNER JOIN DirectReports d
      ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports )

　前述のように、テーブル値UDFはストアドプロシージャとは異なり、SELECT文のFROM句内で使用できます。前述の開発シナリオの場合、テーブル値UDFをFROM句に追加できるため、動的コードとEXEC文を使って、50を超えるすべてのストアドプロシージャを完全に再作成する手間が省けます。

　また、テーブル値UDFは、その他の面でもストアドプロシージャより多くの機能を備えています。例えば相関サブクエリのように、単一値の結果セットをテーブル値UDFのパラメータとして使用できます。例えば、以下のコードは有効です。

select * from ufnGetContactInformation_MDY
   ((SELECT MAX(ContactID)FROM Person.Contact))

　もちろん、機能には代償がつきものです。テーブル値UDFには、使用上の制限がいくつかあります。

テーブル値UDFの制限

　ソフトウェア開発の常として、機能にはトレードオフが伴います。ご想像の通り、テーブル値UDFで返すデータの量には、実際的な制限があります。テーブル値UDFのデータ制限は、一時テーブルの制限に似ています。テーブル値UDFにはパラメータを追加できるため、通常は、多くのパラメータを追加して結果セットをフィルタリングすることにより、このような制限を回避します。

　ストアドプロシージャとは異なり、テーブル値UDFでは、単一の結果セットだけが返されます。

　テーブル値UDF内では、決定性関数のみ使用することができます。非決定性関数は使用できません。SQL Server Books Onlineでは、非決定性関数のことを「呼び出されるたびに異なる結果を返す関数」として定義しています。例えば、テーブル値UDF内ではEXEC文もGETDATE()文も使用できません。

　テキストフィールドとイメージフィールドの処理方法にも制限があります。テキストフィールドまたはイメージフィールドから返されるデータは256バイトに制限され、READTEXT文、WRITETEXT文、およびUPDATETEXT文は使用できません。

優れた代替手段

　これまでの解説からお分かりの通り、テーブル値UDFは、ビューとストアドプロシージャの代わりに使用できる優れた手段です。テーブル値UDFは、ビューと同様の機能を持ち、ストアドプロシージャと同様の構造をしていますが、ビューにはない豊富なコーディング機能を備えており、ストアドプロシージャとは異なり、SELECT文内の任意の場所で使用できる柔軟性を兼ね備えています。