SQL Server でユーザ定義ファンクションを定義する方法のメモを記載します。

動作確認環境

  • 実行環境：Windows 2003 Server
  • 確認環境：SQL Server 2005 Developer Edition

1. スカラー関数の定義

スカラー関数をCREATE FUNCTION を使用します。次のように定義します。関数の削除をDROP FUNCTIONを使用します。

IF OBJECT_ID(N'[dbo].[udf_AbsInt]') IS NOT NULL  
  DROP FUNCTION [dbo].[udf_AbsInt];   
GO
-- スカラー関数の定義
CREATE FUNCTION dbo.udf_AbsInt
(
  @v AS int = 0
) RETURNS int
AS
BEGIN
  IF @v < 0 
    BEGIN
      SET @v = -1 * @v
    END

  RETURN @v
END
GO

-- 関数の使用
SELECT dbo.udf_AbsInt(-1)
GO

-- 関数の削除
DROP FUNCTION dbo.udf_AbsInt
GO

 

2. インライン関数の定義

インライン関数は関数定義にRETURNS句のみを記述しSELECT文を記述します。返り値はTABLE型となります。

IF OBJECT_ID(N'[dbo].[udf_getArticle]') IS NOT NULL  
  DROP FUNCTION [dbo].[udf_getArticle];   
GO
-- インライン関数の定義
CREATE FUNCTION dbo.udf_getArticle
(
  @articleId AS int = 0
) RETURNS TABLE
AS
  RETURN SELECT ArticleID, Title, Abstract
           FROM dbo.Article
          WHERE ArticleID = @articleId
GO

-- 関数の使用
SELECT *
  FROM dbo.udf_getArticle(10)
GO

-- 関数の削除
DROP FUNCTION dbo.udf_getArticle
GO

3. マルチステートメントユーザ定義関数の定義

インライン関数と同様にTABLE型を返り値となります。インライン関数は、1つのSELECT文のみを使用できますが、マルチステートメントテーブル関数の場合は、関数定義内の複数のステートメントを記述することができます。RETURNSにテーブル変数の定義を行い、関数内部で値をINSERTします。

IF OBJECT_ID(N'[dbo].[udf_getArticleWithCommentsCount]') IS NOT NULL  
  DROP FUNCTION [dbo].[udf_getArticleWithCommentsCount];   
GO
-- インライン関数の定義
CREATE FUNCTION dbo.udf_getArticleWithCommentsCount
(
  @articleId AS int = 0
) RETURNS @artcle TABLE (ArticleID int, Title varchar(200), Abstract varchar(2000), CNT int)
AS
BEGIN
  DECLARE @cnt int

  SELECT @cnt = COUNT(*)
    FROM dbo.Comment
   WHERE ArticleID = @articleId

  INSERT INTO @artcle
    SELECT ArticleID, Title, Abstract, @cnt As Comments
             FROM dbo.Article
            WHERE ArticleID = @articleId

  RETURN 
END
GO

-- 関数の使用
SELECT *
  FROM dbo.udf_getArticleWithCommentsCount(10)
GO

-- 関数の削除
DROP FUNCTION dbo.udf_getArticle
GO

関数の修正はALTER FUNCTIONステートメントを使用します。

4. ユーザ定義関数のメタデータを表示

sys.sql_modulesカタログビューを使用するとユーザ定義関数のメタデータを取得することができます。

-- ユーザ定義関数のメタデータを取得する
SELECT s.*
  FROM sys.sql_modules s
INNER JOIN sys.objects o
    ON s.object_id = s.object_id
WHERE TYPE IN ('IF','TF','FN')

4. まとめ

以上で修正が終わりです。関数はストアドと同様に実行プランがキャッシュされるので、再利用時にパフォーマンスの恩恵をうけることができます。