ビューの代替手段として利用する
テーブル値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文内の任意の場所で使用できる柔軟性を兼ね備えています。
