SQL Server 2005 Beta 2 Transact-SQL の機能強化

[アーティクル]
03/19/2008

Itzik Ben-Gan
Solid Quality Learning

December 2004

対象 :
Transact-SQL
Microsoft SQL Server 2005 Beta 2

概要 : この資料では、Microsoft SQL Server 2005 Beta 2 で新しく強化された Transact-SQL の機能をいくつか紹介します。これらの新しい機能により、表現力、クエリのパフォーマンス、エラー管理機能を向上できます。また、概念的に新しいリレーショナルな機能の強化に主に重点を置き、実例を使用して新しい機能を紹介します。この資料には、新しい Transact-SQL 機能すべての説明が含まれているわけではありません。

紹介と対象範囲
クエリの表現力と DRI サポートの向上
パーティション分割
1 つの親を持つ環境 : 従業員組織図
複数の親を持つ環境 : 部品表
相関サブクエリのテーブル値関数
パフォーマンスとエラー処理の機能強化
Transact-SQL に影響を与える SQL Server 2005 Beta 2 の他の機能
まとめ

紹介と対象範囲

この資料では、Microsoft SQL Server 2005 Beta 2 で新しく強化された Transact-SQL の機能をいくつか紹介します。これらの新しい機能により、表現力、クエリのパフォーマンス、エラー管理機能を向上できます。この資料では、概念的に新しいリレーショナルな機能の強化に主に重点を置いており、実例を使用して新しい機能を紹介します。ここには、新しい Transact-SQL 機能すべての説明が含まれているわけではありません。

前提となる知識 : 対象読者は、Microsoft SQL Server 2000 のアドホッククエリに、および Microsoft SQL Server 2000 のアプリケーションのコンポーネントとして、Transact-SQL を使用するスキルを備えている必要があります。

クエリの表現力と DRI サポートの向上

ここでは、次の新しいリレーショナル機能と強化された機能を紹介します。

新しい順位付け関数
共通テーブル式 (CTE) に基づいた新しい再帰クエリ
新しいリレーショナル演算子の PIVOT と APPLY
宣言参照整合性 (DRI) の機能強化

順位付け関数

SQL Server 2005 には、ROW_NUMBER、RANK、DENSE_RANK、NTILE という 4 つの新しい順位付け関数が導入されます。この新しい関数を使用すると、効率的にデータを分析し、クエリの結果行に順位付けの値を提供できます。新しい関数が役立つ一般的なシナリオには、プレゼンテーションの目的での結果行への一連番号の割り当て、ページ切り替え、ランク付け、ヒストグラムなどがあります。

講演者の統計シナリオ

次の講演者の統計シナリオを使用して、さまざまな関数とそこで使用される句について説明および紹介します。コンピュータ関連の大きなカンファレンスが開催されました。トラックはデータベース、開発、システム管理の 3 つです。カンファレンスでは 11 人の講演者が講演し、各セッションについて 1 から 9 までのスコアで出席者にランク付けを求めました。次の SpeakerStats テーブルに結果をまとめ、保存しました。

USE tempdb -- または独自のテスト用データベース
CREATE TABLE SpeakerStats
(
  speaker        VARCHAR(10) NOT NULL PRIMARY KEY,
  track          VARCHAR(10) NOT NULL,
  score          INT         NOT NULL,
  pctfilledevals INT         NOT NULL,
  numsessions    INT         NOT NULL
)

SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan',     'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron',     'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy',   'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB',  9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe',     'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert',  'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike',    'DB',  8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian',   'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin',   'DB',  7, 25, 4)

テーブルには、各講演者について、氏名、トラック、平均スコア、セッション参加者に対する評価者の割合、およびその講演者が行ったセッション数が 1 行に格納されています。ここでは、講演者の統計データを分析し、新しい順位付け関数によって有用な情報を生成する方法について具体的に示します。

セマンティクス

4 つの順位付け関数が取る構文はすべて同様のパターンです。

順位付け関数

<function_name>() OVER(
  [PARTITION BY <partition_by_list>]
  ORDER BY <order_by_list>)

順位付け関数はクエリの SELECT 句または ORDER BY 句だけで指定できます。以降のセクションで、各関数について詳しく説明します。

ROW_NUMBER

ROW_NUMBER 関数を使用して、クエリの結果行に整数の一連番号を割り当てることができます。たとえば、すべての講演者の speaker、track、score を返し、スコアの降順に 1 から始まる連続値を結果行に割り当てるとします。 ROW_NUMBER 関数で OVER (ORDER BY score DESC) を指定した次のクエリを実行すると、希望する結果が出力されます。

SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, 
  speaker, track, score
FROM SpeakerStats
ORDER BY score DESC

結果セットは以下のようになります。

rownum speaker    track      score
------ ---------- ---------- -----------
1      Jessica    Dev        9
2      Ron        Dev        9
3      Suzanne    DB         9
4      Kathy      Sys        8
5      Michele    Sys        8
6      Mike       DB         8
7      Kevin      DB         7
8      Brian      Sys        7
9      Joe        Dev        6
10     Robert     Dev        6
11     Dan        Sys        3

スコアが最も高い講演者には行番号 1 が割り当てられ、スコアが最も低い講演者は行番号 11 が割り当てられます。ROW_NUMBER 関数は、要求した順序に従ってすべての行に異なる行番号を返します。 OVER() オプションの中で指定した ORDER BY リストが一意ではない場合、結果は 1 とおりに決まりません。つまり、正しいクエリの結果が 2 つ以上存在することになります。クエリを呼び出すごとに異なる結果が得られることもあります。たとえば、上記の例では 3 人の講演者 Jessica、Ron、Suzanne に同一の最高スコア (9) が与えられています。異なる講演者には異なる行番号が割り当たることになっているので、Jessica、Ron、Suzanne にそれぞれ割り当てられた値 1、2、3 は、この 3 人に順不同で割り当てられたと考えてください。値 1、2、3 がそれぞれ Ron、Suzanne、Jessica に割り当てられたとしても、結果が正しいことに変わりありません。

一意の ORDER BY リストを指定した場合、結果は常に 1 とおりに決まります。たとえば、複数の講演者のスコアが同一の場合、pctfilledevals 値が最も高い講演者を上位にするとします。それでも決着が付かない場合、numsessions 値が最も高い講演者を上位にするとします。さらにそれでも決着が付かない場合、speaker の名前が辞書順で前に来る講演者を上位にします。次の例では、ORDER BY リスト (score、pctfilledevals、numsessions、および speaker) が一意なので、結果が 1 とおりに決まります。

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,
                           numsessions DESC, speaker) AS rownum, 
  speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

結果セットは以下のようになります。

rownum speaker    track      score       pctfilledevals numsessions
------ ---------- ---------- ----------- -------------- -----------
1      Ron        Dev        9           30             3
2      Suzanne    DB         9           30             3
3      Jessica    Dev        9           19             1
4      Michele    Sys        8           31             4
5      Kathy      Sys        8           27             2
6      Mike       DB         8           20             3
7      Kevin      DB         7           25             4
8      Brian      Sys        7           22             3
9      Robert     Dev        6           28             2
10     Joe        Dev        6           20             2
11     Dan        Sys        3           22             4

新しい順位付け関数の重要な利点の 1 つに、効率性があります。 SQL Server のオプティマイザでは、値を計算するためだけにデータを 1 度スキャンする必要があります。その方法は、並べ替えに使用する列のインデックスを順序どおりにスキャンするか、適切なインデックスが作成されていない場合は 1 度データをスキャンしてデータを並べ替えるかのいずれかです。

また、構文が単純であることも利点です。 SQL Server のこれまでのリリースのように設定をあらかじめ行っておいてから順位付けの値を計算する方法が、どのくらい困難で効率が悪いかをわかっていただくため、上記のクエリと同じ結果を返す次の SQL Server 2000 クエリを見てください。

SELECT
  (SELECT COUNT(*)
   FROM SpeakerStats AS S2
   WHERE S2.score > S1.score
     OR (S2.score = S1.score
         AND S2.pctfilledevals > S1.pctfilledevals)
     OR (S2.score = S1.score
         AND S2.pctfilledevals = S1.pctfilledevals
         AND S2.numsessions > S1.numsessions)
     OR (S2.score = S1.score
         AND S2.pctfilledevals = S1.pctfilledevals
         AND S2.numsessions = S1.numsessions
         AND S2.speaker < S1.speaker)) + 1 AS rownum,
  speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats AS S1
ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

このクエリが SQL Server 2005 クエリよりもはるかに複雑であることは明らかです。さらに、SpeakerStats テーブル内のベース行ごとに、一致するすべての行を別のテーブルインスタンスでスキャンする必要があります。平均的に、ベーステーブルの行 1 行につき、最少でもテーブルのおよそ半分の行をスキャンする必要があります。 SQL Server 2005 クエリはパフォーマンスの低下が直線的ですが、SQL Server 2000 クエリはその低下が指数関数的です。非常に小さなテーブルでも、パフォーマンスの違いは明白です。たとえば、次のクエリのパフォーマンスをテストします。AdventureWorks データベースの SalesOrderHeader テーブルにクエリし、SalesOrderID ごとの発注件数を計算します。 SalesOrderHeader テーブルは 31,465 行です。最初のクエリでは SQL Server 2005 の ROW_NUMBER 関数を使用し、2 番目のクエリでは SQL Server 2000 のサブクエリ方式を使用します。

-- SQL Server 2005 クエリ
SELECT SalesOrderID,
  ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader
-- SQL Server 2000 クエリ
SELECT SalesOrderID,
  (SELECT COUNT(*)
   FROM Sales.SalesOrderHeader AS S2
   WHERE S2.SalesOrderID <= S1.SalesOrderID) AS rownum
FROM Sales.SalesOrderHeader AS S1

このテストは筆者のノートパソコン (Compaq Presario X1020U、CPU: Centrino 1.4 GHz、RAM: 1GB、ローカル HD) で実施しました。SQL Server 2005 クエリがわずか 1 秒で完了したのに対し、SQL Server 2000 クエリは約 12 分かかりました。

行数を使用する一般的な応用例に、クエリの結果によるページ切り替えがあります。行数でページサイズを指定し、ページ番号を指定したときに、そのページに含まれる行を返す必要があります。たとえば、score DESC, speaker の順で 3 行ごとにページが構成されている場合に、SpeakerStats テーブルから 2 ページ目の行を返すとします。次のクエリは、指定した順序で並べ替えた派生テーブル D の行番号を計算し、行番号が 4 から 6 までの 2 ページ目に相当する行だけを抽出します。

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, 
        speaker, track, score
      FROM SpeakerStats) AS D
WHERE rownum BETWEEN 4 AND 6
ORDER BY score DESC, speaker

結果セットは以下のようになります。

rownum speaker    track      score
------ ---------- ---------- -----------
4      Kathy      Sys        8
5      Michele    Sys        8
6      Mike       DB         8

さらに一般化するなら、@pagenum 変数にページ番号を指定し、@pagesize 変数にページサイズを指定して次のクエリを実行すると、指定したページに含まれる行が返されます。

DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, 
        speaker, track, score
      FROM SpeakerStats) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

上記の方式は、特定の 1 ページに含まれる行だけに注目した一時的な要求には適しています。しかし、クエリを呼び出すたびにテーブル全体をスキャンして行番号を計算する必要があるので、ユーザーが複数の要求を実行する場合はこの方式は適しません。ユーザーが複数のページを繰り返し要求する可能性がある場合に効率的なページ切り替えを行うには、まずベーステーブルのすべての行を、計算した行番号と共に一時テーブルに格納して、その行番号を含む列にインデックスを設定します。

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *
INTO #SpeakerStatsRN
FROM SpeakerStats
CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)

次に、要求された各ページに対し、次のクエリを実行します。

SELECT rownum, speaker, track, score
FROM #SpeakerStatsRN
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker

該当するページに含まれる行だけがスキャンされます。

パーティション分割

テーブル内のすべての行を 1 つのグループとして順位付けの値を計算する方法とは対照的に、行のグループ内での値をグループごとに計算できます。これを行うには、PARTITION BY 句を使用して、順位付けの値を個別に計算する行グループを特定する式のリストを指定します。たとえば、次のクエリは、トラック内を score DESC, speaker の順に並べ替えてトラックごとに個別の行番号を割り当てます。

SELECT track, 
  ROW_NUMBER() OVER(
    PARTITION BY track 
    ORDER BY score DESC, speaker) AS pos, 
  speaker, score
FROM SpeakerStats
ORDER BY track, score DESC, speaker

結果セットは以下のようになります。

track      pos speaker    score
---------- --- ---------- -----------
DB         1   Suzanne    9
DB         2   Mike       8
DB         3   Kevin      7
Dev        1   Jessica    9
Dev        2   Ron        9
Dev        3   Joe        6
Dev        4   Robert     6
Sys        1   Kathy      8
Sys        2   Michele    8
Sys        3   Brian      7
Sys        4   Dan        3

PARTITION BY 句で track 列を指定すると、同一トラック内の行のグループごとに行番号が計算されます。

RANK、DENSE_RANK

RANK 関数と DENSE_RANK 関数は、指定した順序で順位付けの値を計算でき、オプションとしてそれを行のグループ (パーティション) 内部でも行うことができるという意味で ROW_NUMBER 関数と非常によく似ています。ただし ROW_NUMBER とは異なり、RANK と DENSE_RANK は並べ替えに使用する列の値が同一である行には同一の順位を割り当てます。 RANK と DENSE_RANK は、ORDER BY リストが一意でない場合に、ORDER BY リストの値が同一である行に異なる順位を割り当てたくないときに有用です。 RANK と DENSE_RANK の用途と両者の違いについては、例を使うと非常にわかりやすく説明できます。次のクエリは、score DESC の順に講演者の行番号、および RANK と DENSE_RANK の値を計算します。

SELECT speaker, track, score,
  ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
  RANK() OVER(ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER(ORDER BY score DESC) AS drnk
FROM SpeakerStats
ORDER BY score DESC

結果セットは以下のようになります。

speaker    track      score       rownum rnk drnk
---------- ---------- ----------- ------ --- ----
Jessica    Dev        9           1      1   1
Ron        Dev        9           2      1   1
Suzanne    DB         9           3      1   1
Kathy      Sys        8           4      4   2
Michele    Sys        8           5      4   2
Mike       DB         8           6      4   2
Kevin      DB         7           7      7   3
Brian      Sys        7           8      7   3
Joe        Dev        6           9      9   4
Robert     Dev        6           10     9   4
Dan        Sys        3           11     11  5

既に説明したとおり、score 列が一意でないので複数の講演者に同一のスコアが与えられる場合があります。行番号はスコアの降順になってはいますが、同一スコアの講演者に対し、異なる行番号が与えられています。しかし、結果を見ると、RANK と DENSE_RANK の値は同一スコアのすべての講演者に同一の値が与えられています。つまり、ROW_NUMBER では ORDER BY リストが一意でない場合、結果が 1 とおりに決まりませんが、RANK と DENSE_RANK では常に結果が 1 とおりに決まります。 RANK と DENSE_RANK の値の違いは、RANK が "その行よりもスコアが高い行の総数" プラス 1 を表すのに対し、DENSE_RANK は "その行よりも高いスコアの数" プラス 1 を表します。これまでの説明から、ORDER BY リストが一意のときは、ROW_NUMBER、RANK、および DENSE_RANK がまったく同一の値を出力することがわかります。

NTILE

NTILE を使用して、クエリの結果行を、指定した順序に従って指定した個数のグループ (タイル) に分割できます。それぞれの行グループには 1 から始まる一意の番号が与えられます。最初のグループは 1、2 番目のグループには 2 というように続きます。関数名に続けてかっこ内に希望するグループの数を指定し、OVER オプションの ORDER BY 句に希望する並べ替え順序を指定します。 1 つのグループに含まれる行数は "行の総数 / グループ数" で計算できます。このとき、剰余を n とすると、最初の n 個のグループは行が 1 行多くなります。したがって、すべてのグループの行数が等しくなるとは限りませんが、グループサイズの違いは最大でも 1 行です。たとえば、次のクエリは講演者の行をスコアの降順に 3 つのグループメンバに割り当てます。

SELECT speaker, track, score,
  ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,
  NTILE(3) OVER(ORDER BY score DESC) AS tile
FROM SpeakerStats
ORDER BY score DESC

結果セットは以下のようになります。

speaker    track      score       rownum tile
---------- ---------- ----------- ------ ----
Jessica    Dev        9           1      1
Ron        Dev        9           2      1
Suzanne    DB         9           3      1
Kathy      Sys        8           4      1
Michele    Sys        8           5      2
Mike       DB         8           6      2
Kevin      DB         7           7      2
Brian      Sys        7           8      2
Joe        Dev        6           9      3
Robert     Dev        6           10     3
Dan        Sys        3           11     3

SpeakerStats テーブルには 11 人の講演者がいます。 11 を 3 で割ると、グループサイズが 3、剰余が 2 であることがわかります。つまり、最初の 2 個のグループは行が 1 行多くなり (各グループとも 4 行)、3 番目のグループは行が追加されません (3 行)。行 1 から 4 にグループ番号 (タイル番号) 1、行 5 から 8 にグループ番号 2、行 9 から 11 にグループ番号 3 が割り当てられます。この情報から、区間ごとに項目を均一に分散させたヒストグラムを作成できます。上記の例では、最初の区間が上位スコアの講演者、2 番目の区間が中位スコアの講演者、3 番目の区間が下位スコアの講演者をそれぞれ表します。 CASE 式を使用して、グループ番号を説明的で意味のある名前に変更することもできます。

SELECT speaker, track, score,
  CASE NTILE(3) OVER(ORDER BY score DESC)
    WHEN 1 THEN 'High'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'Low'
  END AS scorecategory
FROM SpeakerStats
ORDER BY track, speaker

結果セットは以下のようになります。

speaker    track      score       scorecategory
---------- ---------- ----------- -------------
Kevin      DB         7           Medium
Mike       DB         8           Medium
Suzanne    DB         9           High
Jessica    Dev        9           High
Joe        Dev        6           Low
Robert     Dev        6           Low
Ron        Dev        9           High
Brian      Sys        7           Medium
Dan        Sys        3           Low
Kathy      Sys        8           High
Michele    Sys        8           Medium

再帰クエリと共通テーブル式

ここでは、再帰 CTE 式の優れた点を調べ、それを従来のアプローチを非常に簡素化する形で、一般的な問題点に対するソリューションとして応用します。

共通テーブル式

共通テーブル式 (CTE) は、定義しているステートメントによって参照できる名前付きの一時的な結果セットです。単純な形式の CTE は、非永続的なビューとよくに似た、改良版の派生テーブルと見なすことができます。派生テーブルやビューを参照する場合と同様に、クエリの FROM 句で CTE を参照します。 CTE は 1 回だけ定義します。その後、クエリ内で CTE を複数回参照できます。 CTE の定義では、同一バッチ内に定義された変数を参照できます。また、ビューを使用する場合と同様に、INSERT、UPDATE、DELETE、および CREATE VIEW の各ステートメントでも CTE を使用できます。しかし、CTE の真の能力は、自己を参照する再帰的な機能にあります。この資料では、単純な形式の CTE を前半で、再帰的な形式の CTE を後半で説明します。また、CTE を使った SELECT クエリを扱います。

クエリの結果をテーブル同様に参照したいけれどもデータベースに永続的なビューを作成することは望まないとき、派生テーブルを使用します。しかし派生テーブルは、CTE とは異なり、クエリの中で一度定義しておき何度も再利用することができません。代わりに、同じクエリを使用して、いくつかの派生テーブルを定義する必要があります。一方、CTE は、データベースに保存しなくても、一度定義すればクエリ内で複数回使用することができます。

CTE の実例を紹介する前に、CTE の基本構文を派生テーブルやビューと比較します。以下は、ビュー、派生テーブル、および CTE 内のクエリの一般的な形式です。

ビュー

CREATE VIEW <view_name>(<column_aliases>)
AS
<view_query>
GO
SELECT *
FROM <view_name>

派生テーブル

SELECT *
FROM (<derived_table_query>) AS <derived_table_alias>(<column_aliases>)

CTE

WITH <cte_alias>(<column_aliases>)
AS
(

  <cte_query>
)
SELECT *
FROM <cte_alias>

WITH キーワードの後に、CTE の別名と結果列の別名のリスト (省略可能) を指定し、CTE の本体を記述し、外部クエリから参照します。

CTE の WITH 句がバッチ内の最初のステートメントでない場合、WITH 句の前にセミコロン (;) を記述し、先行するステートメントと区切る必要があることに注意してください。セミコロンは、WITH 句の他の用途 (たとえば、テーブルヒント) とのあいまいさをなくすために使用します。必ずしもセミコロンを指定する必要はありませんが、常に使用することをお勧めします。

実例として、AdventureWorks データベースの HumanResources.Employee テーブルと Purchasing.PurchaseOrderHeader テーブルを取り上げます。各従業員は、ManagerID 列で指定されたマネージャの下に配属されています。 Employee テーブルの各従業員は、PurchaseOrderHeader テーブル内の注文との関連付けがある場合があります。従業員ごとに注文数と最終注文日付を返し、同じ行にマネージャについて同様の詳細を返すとします。以下の例で、ビュー、派生テーブル、および CTE を使用してソリューションを実装できる方法を示します。

ビュー

CREATE VIEW VEmpOrders(EmployeeID, NumOrders, MaxDate)
AS
SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
GO
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
  E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
  JOIN VEmpOrders AS OE
    ON E.EmployeeID = OE.EmployeeID
  LEFT OUTER JOIN VEmpOrders AS OM
    ON E.ManagerID = OM.EmployeeID

派生テーブル

SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
  E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E

  JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
        FROM Purchasing.PurchaseOrderHeader
        GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate)
    ON E.EmployeeID = OE.EmployeeID
  LEFT OUTER JOIN
       (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
        FROM Purchasing.PurchaseOrderHeader
        GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate)
    ON E.ManagerID = OM.EmployeeID

CTE

WITH EmpOrdersCTE(EmployeeID, NumOrders, MaxDate)
AS
(
  SELECT EmployeeID, COUNT(*), MAX(OrderDate)
  FROM Purchasing.PurchaseOrderHeader
  GROUP BY EmployeeID
)
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate,
  E.ManagerID, OM.NumOrders, OM.MaxDate
FROM HumanResources.Employee AS E
  JOIN EmpOrdersCTE AS OE
    ON E.EmployeeID = OE.EmployeeID
  LEFT OUTER JOIN EmpOrdersCTE AS OM
    ON E.ManagerID = OM.EmployeeID

CTE は外部クエリの前に定義する必要があります。ただし、CTE の定義は参照する場合としない場合があります。他の介入しているステートメントの後のバッチ内で CTE を後で参照することはできません。 1 つの WITH 句にいくつかの CTE を定義して、それぞれが前に定義した CTE を参照するように指定できます。 CTE を区切るには、コンマを使用します。たとえば、従業員の注文数の最大値、最小値、および差異を計算するとします。

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Purchasing.PurchaseOrderHeader

  GROUP BY EmployeeID
),
MinMaxCTE(MN, MX, Diff)
AS
(
  SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
  FROM EmpOrdersCTE
)
SELECT * FROM MinMaxCTE

結果セットは以下のようになります。

MN          MX          Diff       
----------- ----------- -----------
160         400         240

EmpOrdersCTE で、各従業員の注文数を計算します。 MinMaxCTE で、注文数の最大値、最小値、差異を計算するために EmpOrdersCTE を参照します。

注 CTE 内では、その CTE の直前に定義された CTE しか参照できないという制限はありません。前に定義されたすべての CTE を参照できます。前方参照は許可されていないことに注意してください。つまり、ある CTE はそれより前に定義された CTE とその CTE 自体 (この資料後半の「再帰クエリ」を参照) を参照できますが、その CTE の後に定義されている CTE を参照することはできません。たとえば、同一の WITH ステートメント内で CTE C1、C2、C3 を定義した場合、C2 は C1 と C2 を参照できますが、C3 を参照することはできません。

別の例として、以下のコードでは、注文数の最小値から最大値までを 4 つに分けた範囲のそれぞれに含まれる従業員の数を計算するヒストグラムが生成されます。計算が少し複雑に感じる場合、この計算を理解することに時間を費やす必要はありません。この例の目的は、実用的なシナリオを使用して、前方参照を行う CTE を含めた複数の CTE を同一の WITH ステートメント内で宣言する実例を示すことにあります。

WITH
EmpOrdersCTE(EmployeeID, Cnt)
AS
(
  SELECT EmployeeID, COUNT(*)
  FROM Purchasing.PurchaseOrderHeader
  GROUP BY EmployeeID
),

MinMaxCTE(MN, MX, Diff)
AS
(
  SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
  FROM EmpOrdersCTE
),
NumsCTE(Num)
AS
(
  SELECT 1 AS Num
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
),
StepsCTE(Step, Fromval, Toval)
AS
(
  SELECT
    Num,
    CAST(MN + ROUND((Num-1)*((Diff+1)/4.), 0) AS INT),
    CAST(MN + ROUND((Num)*((Diff+1)/4.), 0) - 1 AS INT)
  FROM MinMaxCTE CROSS JOIN NumsCTE
),
HistogramCTE(Step, Fromval, Toval, Samples)
AS
(
  SELECT S.Step, S.Fromval, S.Toval, COUNT(EmployeeID)
  FROM StepsCTE AS S
    LEFT OUTER JOIN EmpOrdersCTE AS OE
      ON OE.Cnt BETWEEN S.Fromval AND S.Toval
  GROUP BY S.Step, S.Fromval, S.Toval
)
SELECT * FROM HistogramCTE

結果セットは以下のようになります。

Step        Fromval     Toval       Samples
----------- ----------- ----------- -----------
1           160         219         2
2           220         280         0
3           281         340         0
4           341         400         10

上の例から次のことがわかります。2 番目の CTE (MinMaxCTE) が最初の CTE (EmpOrdersCTE) を参照しています。3 番目の CTE (NumsCTE) はどの CTE も参照していません。 4 番目の CTE (StepsCTE) は 2 番目と 3 番目の CTE を参照しています。5 番目の CTE (HistogramCTE) は最初と 4 番目の CTE を参照しています。

再帰クエリ

非再帰 CTE を使用すると、表現力が増します。それにもかかわらず、非再帰 CTE を使用するコードの各部分を記述する場合、通常、派生テーブルなどの他の Transact-SQL 構造を使用するよりも短いコードで同じ結果を得られます。再帰 CTE はこの点が異なります。ここでは、再帰クエリのセマンティクスについて説明し、従業員組織図の階層、および部品表 (BOM) のシナリオの実用的な実装を提供します。

セマンティクス

CTE が自己を参照する場合、"再帰している" と考えます。再帰 CTE は、少なくとも 2 個のクエリ (再帰クエリ用語では "メンバ") で構成されます。 1 つのクエリは非再帰クエリであり、"アンカーメンバ (AM)" とも呼ばれます。もう 1 つのクエリは再帰クエリで、"再帰メンバ (RM)" とも呼ばれます。 2 つのクエリは、UNION ALL 演算子で区切ります。以下の例で、再帰 CTE の一般的な形式を簡単に示します。

WITH RecursiveCTE(<column_list>)
AS
(
  -- アンカー メンバ :
  -- RecursiveCTE を参照しない SELECT クエリ
  SELECT ... 
  FROM <some_table(s)>
  ...
  UNION ALL
  -- 再帰メンバ
  -- RecursiveCTE を参照する SELECT クエリ
  SELECT ...
  FROM <some_table(s)>
    JOIN RecursiveCTE

  ...
)
-- 外部クエリ
SELECT ...
FROM RecursiveCTE
...

論理的には再帰 CTE を実装するアルゴリズムを以下のように考えることができます。

アンカーメンバがアクティブになります。 SET R0 (R は結果を表します) が生成されます。
再帰メンバがアクティブになり、RecursiveCTE を参照するときに、入力として SET Ri (i = 手順番号) を取得します。 SET Ri + 1 が生成されます。
手順 2 のロジックは、空の SET が返されるまで繰り返し実行されます (繰り返されるたびに、手順番号が増加します)。
外部クエリが実行され、RecursiveCTE を参照するときに、以前の手順すべての累積 (UNION ALL) 結果が取得されます。

CTE にはメンバを 3 つ以上含めることができます。しかし、再帰メンバと他のメンバ (再帰または非再帰) の間に使用できる演算子は、UNION ALL 演算子のみです。 UNION などの他の演算子は、非再帰メンバ間にしか使用できません。暗黙の変換をサポートする通常の UNION や UNION ALL 演算子とは異なり、再帰 CTE では、同じデータ型、長さ、有効桁数など、すべてのメンバの列が正確に一致する必要があります。

再帰 CTE と従来の (必ずしも SQL Server に固有ではない) 再帰ルーチンの間には類似性があります。再帰ルーチンは、通常 3 つの重要な要素で構成されます。3 つの要素とは、ルーチンの初回の呼び出し、再帰の終了チェック、および同じルーチンへの再帰呼び出しです。再帰 CTE では、アンカーメンバが従来の再帰ルーチンの初回の呼び出しに相当します。再帰メンバはルーチンの再帰呼び出しに相当します。終了チェックは、再帰ルーチンの場合は (IF ステートメントを終了するなどの方法で) 明示的に行うのが一般的ですが、再帰 CTE では暗黙的に行われます。直前の呼び出しで行が返されなかった場合に再帰が終了します。

以降のセクションでは、1 つおよび複数の親を持つ環境での再帰 CTE の実例と用途を紹介します。

1 つの親を持つ環境 : 従業員組織図

1 つの親を持つ階層のシナリオには、従業員組織図を使用します。

注ここでの例では、AdventureWorks の HumanResources.Employee テーブルとは異なる構造を持つ Employees というテーブルを使用します。コードは、独自のテスト用データベース、または tempdb で実行してください。AdventureWorks は使用しないでください。

以下のコードを実行して、Employees テーブルを生成し、そのテーブルにサンプルデータを作成します。

USE tempdb -- または独自のテスト用データベース
CREATE TABLE Employees
(
  empid   int         NOT NULL,
  mgrid   int         NULL,
  empname varchar(25) NOT NULL,
  salary  money       NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT FK_Employees_mgrid_empid
    FOREIGN KEY(mgrid)
    REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00) 
INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

各従業員は、mgrid 列に格納されている ID を持つマネージャの下に所属します。 empid 列を参照する mgrid 列に、外部キーが定義されています。つまり、マネージャ ID はテーブル内の有効な従業員 ID に合致するか、NULL である必要があります。主任の Nancy は、mgrid 列が NULL になります。マネージャと従業員のリレーションシップを図 1 に示します。

図 1. 従業員組織図

次に、Employees テーブルに対し実行する可能性がある一般的な要求を示します。

Robert (empid=7) および彼の部下全員 (すべてのレベル) の詳細を確認する。
Janet (empid=3) から 2 レベル下のすべての従業員の詳細を確認する。
James (empid=14) までの管理者のつながりを確認する。
各マネージャに直接または間接的に所属している従業員の数を確認する。
階層の依存関係を見やすくした形ですべての従業員を表示する。

上記の要求は、再帰 CTE を使用して処理できます。これらは再帰的な性質があるので、処理の際、データベースに階層に関する追加情報を保持する必要はありません。

最初の要求は、最も一般的な要求であると考えられます。つまり、従業員 (たとえば、empid=7 の Robert) およびその人物より下位のすべてのレベルに存在する部下を返します。この要求へのソリューションは、以下の CTE により提供されます。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 

  -- アンカー メンバ (AM)
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 7
  UNION ALL
  
  -- 再帰メンバ (RM)
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

結果セットは以下のようになります。

empid       empname                   mgrid       lvl        
----------- ------------------------- ----------- -----------
7           Robert                    3           0          
11          David                     7           1          
12          Ron                       7           1          
13          Dan                       7           1          
14          James                     11          2

この CTE は、前に説明した再帰 CTE ロジックに従って、次のように処理されます。

アンカーメンバがアクティブになり、Employees テーブルから Robert の行を返します。 lvl 結果列に定数 0 が返されることに注目してください。

再帰メンバが繰り返しアクティブになり、Employees と EmpCTE の結合操作により直前の結果の直属の部下を返します。以下のように、Employees は部下を表し、EmpCTE (直前の呼び出しの結果を含んでいます) はマネージャを表します。
- まず、Robert の部下である David、Ron、および James が返されます。
- 次に、David、Ron、および Dan の部下が返されます。つまり、James だけが返されます。
- 最後に、James の部下が返されます。誰も返されず、その場合、再帰が終了します。
外部クエリにより、EmpCTE から全行が返されます。

再帰呼び出しがあるごとに、lvl 値が繰り返しインクリメントされることがわかります。

このレベルカウンタを使用すると、再帰の繰り返し数を制限できます。たとえば、以下の CTE を使用して、Janet から 2 レベル下のすべての従業員を返します。

WITH EmpCTEJanet(empid, empname, mgrid, lvl)
AS
( 
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 3
  UNION ALL
  
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees as E
    JOIN EmpCTEJanet as M
      ON E.mgrid = M.empid
  WHERE lvl < 2
)
SELECT empid, empname
FROM EmpCTEJanet
WHERE lvl = 2

結果セットは以下のようになります。

empid       empname                  
----------- -------------------------
11          David                    
12          Ron                      
13          Dan

1 つ前のコード例と比較してこのコード例に追加された部分は、太字で示してあります。再帰メンバのフィルタ WHERE lvl < 2 は、再帰終了チェックとして使用されます。lvl = 2 のとき、返される行がないので、再帰が停止します。外部クエリのフィルタ WHERE lvl = 2 を使用して、レベル 2 以下のすべてのレベルを取り出します。理論上は、外部クエリのフィルタ (lvl = 2) 自体があれば希望する行だけを返すには十分です。再帰メンバのフィルタ (lvl < 2) は、Janet から 2 レベル下が返された直後に再帰を停止するため、パフォーマンス上の理由から追加されています。

上記のとおり、CTE は同一バッチ内で定義されているローカル変数を参照できます。たとえば、クエリをより汎用的にするため、従業員 ID とレベルに定数ではなく変数を使用できます。

DECLARE @empid AS INT, @lvl AS INT
SET @empid = 3 -- Janet
SET @lvl   = 2 -- 2 レベル
WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = @empid
  UNION ALL
  
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees as E
    JOIN EmpCTE as M
      ON E.mgrid = M.empid
  WHERE lvl < @lvl
)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = @lvl

再帰を特定回数繰り返し呼び出した後でクエリを強制終了するには、ヒントを使用します。この処理を行うには、外部クエリの末尾に OPTION(MAXRECURSION value) を追加します。以下に例を示します。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 1
  UNION ALL

  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees as E
    JOIN EmpCTE as M
      ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
OPTION (MAXRECURSION 2)

結果セットは以下のようになります。

empid       empname                   mgrid       lvl        
----------- ------------------------- ----------- -----------
1           Nancy                     NULL        0          
2           Andrew                    1           1          
3           Janet                     1           1          
4           Margaret                  1           1          
10          Ina                       4           2          
7           Robert                    3           2          
8           Laura                     3           2          
9           Ann                       3           2          
.Net SqlClient Data Provider: メッセージ 530、レベル 16、状態 1、行 1
ステートメントが終了しました。ステートメントの完了前に最大再帰数 2 に達しました。

ここまでに生成された結果がおそらく返され (返されるとは限りません)、エラー 530 が発生します。 Janet より下位 2 レベルの従業員を返す要求を実現するために、再帰メンバのフィルタではなく MAXRECURSION オプションを使用する方法を考えることもできます。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 3
  UNION ALL
  
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees as E
    JOIN EmpCTE as M
      ON E.mgrid = M.empid

)
SELECT empid, empname
FROM EmpCTE
WHERE lvl = 2
OPTION (MAXRECURSION 2)

結果セットは以下のようになります。

empid       empname                  
----------- -------------------------
11          David                    
12          Ron                      
13          Dan                      
.Net SqlClient Data Provider: メッセージ 530、レベル 16、状態 1、行 1
ステートメントが終了しました。ステートメントの完了前に最大再帰数 2 に達しました。

ただし、結果が返る保証がない上、クライアントでエラーが発生することを覚えておいてください。正常な状況でエラーを返すコードを使用するのは、適切なプログラミング手法とはいえません。前に使用したフィルタを使用することをお勧めします。MAXRECURSION ヒントは、必要に応じて、無限ループに陥らないための予防手段として使用することをお勧めします。

このヒントを指定しない場合、既定値 100 が適用されます。この値は、循環再帰呼び出しが疑われる場合の予防手段として使用できます。再帰呼び出しの回数を制限しない場合、ヒントの MAXRECURSION を 0 に設定してください。

リレーションシップが循環する例として、データに不具合があり、Nancy の (本来は存在しないはずの) マネージャが James に変更されてしまったとします。

UPDATE Employees SET mgrid = 14 WHERE empid = 1

1->3->7->11->14->1 という循環ができあがります。 Nancy とすべてのレベルに属する彼女の直接および間接の部下を返すコードを実行すると、ステートメントが完了する前に既定の最大再帰回数である 100 回に達したことを示すエラーが発生します。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 1
  UNION ALL
  
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1
  FROM Employees AS E

    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
メッセージ 530、レベル 16、状態 1、行 1
ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。

無限再帰呼び出しを防ぐ安全策を設けことをお勧めしますが、MAXRECURSION を使用しても、循環を見つけてデータの不具合を解消する際にはそれほど役立ちません。循環を見つけるため、従業員ごとに、その従業員に至るまでのすべての従業員 ID を列挙したパスを構築する CTE を使用できます。この結果列のパスを呼び出します。再帰メンバの中で CASE 式を使用して、現在の従業員 ID がマネージャのパスに既に現れているかどうかを LIKE 述語でチェックします。現れている場合、循環があることになります。循環を見つけたら cycle という結果列に 1 を返し、それ以外の場合 0 を返します。また、循環が見つからなかったマネージャの部下のみを返すフィルタを再帰メンバに追加します。最後に、循環が見つかった (cycle = 1) 従業員のみを返すフィルタを外部クエリに追加します。

WITH EmpCTE(empid, path, cycle)
AS
( 
  SELECT empid,
    CAST('.' + CAST(empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
    0
  FROM Employees
  WHERE empid = 1
  UNION ALL
  
  SELECT E.empid,
    CAST(M.path + CAST(E.empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
    CASE
      WHEN M.path LIKE '%.' + CAST(E.empid AS VARCHAR(10)) + '.%' THEN 1
      ELSE 0
    END
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
  WHERE M.cycle = 0

)
SELECT path FROM EmpCTE
WHERE cycle = 1
path
---------------
.1.3.7.11.14.1.

アンカーメンバと再帰メンバの両方の対応する列が、同じデータ型、長さ、および有効桁数を保持することに注意してください。そのために、path 値を生成する式を "いずれのメンバも" varbinary(900) に変換します。循環が見つかったら、Nancy にはマネージャがいないように変更してデータの不具合を修正できます。

UPDATE Employees SET mgrid = NULL WHERE empid = 1

これまでに説明した再帰の例には、マネージャであるアンカーメンバ、および部下を取得する再帰メンバが含まれています。 James の管理者パス (James と、すべてのレベルの彼のマネージャ全員) を返す場合など、一部の要求ではその逆が必要になります。以下のコードで、この要求への回答を提供します。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
( 
  SELECT empid, empname, mgrid, 0
  FROM Employees
  WHERE empid = 14
  UNION ALL
  
  SELECT M.empid, M.empname, M.mgrid, E.lvl+1
  FROM Employees as M
    JOIN EmpCTE as E
      ON M.empid = E.mgrid
)
SELECT * FROM EmpCTE

結果セットは以下のようになります。

empid       empname                   mgrid       lvl        
----------- ------------------------- ----------- -----------
14          James                     11          0          
11          David                     7           1          
7           Robert                    3           2          
3           Janet                     1           3          
1           Nancy                     NULL        4

アンカーメンバは James の行を返します。ここでは 1 つの親を持つ階層を使用しており、要求が 1 人の従業員から開始されているので、再帰メンバは、以前返された従業員の複数のマネージャまたは 1 人のマネージャを返します。

また、再帰クエリは、各マネージャに直接または間接的に所属する部下の数など、集計を計算するためにも使用できます。

WITH MgrCTE(mgrid, lvl)
AS
(
  SELECT mgrid, 0
  FROM Employees
  WHERE mgrid IS NOT NULL
  UNION ALL
  SELECT M.mgrid, lvl + 1
  FROM Employees AS M
    JOIN MgrCTE AS E
      ON E.mgrid = M.empid
  WHERE M.mgrid IS NOT NULL
)
SELECT mgrid, COUNT(*) AS cnt
FROM MgrCTE
GROUP BY mgrid

結果セットは以下のようになります。

mgrid       cnt        
----------- -----------
1           13         
2           2          
3           7          
4           1          
7           4          
11          1

アンカーメンバは、各従業員のマネージャ ID が保持されている行を返します。マネージャ ID 列が NULL の場合、特定のマネージャが存在しないことを表すので、NULL は除外されます。再帰メンバが、既に返されたマネージャを管理するマネージャの ID を返します。ここでも、NULL は除外されます。最終的に、CTE には、マネージャごとに直接または間接的な部下の数が保持されます。外部クエリは、マネージャ ID ごとに結果をグループ化し、部下数を返す作業を引き続き行います。

1 つの親を持つ階層に対する要求のもう 1 つの例として、Nancy の部下を階層の依存関係に従って並べ替えとインデントを行った状態で返すとします。これを行うには、以下のコードを使用します。従業員 ID によって兄弟を並べ替えます。

WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
( 
  SELECT empid, empname, mgrid, 0,
    CAST(empid AS VARBINARY(900))
  FROM Employees
  WHERE empid = 1
  UNION ALL
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
    CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
  FROM Employees AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT
  REPLICATE(' | ', lvl)
    + '(' + (CAST(empid AS VARCHAR(10))) + ') '
    + empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
 | (2) Andrew
 |  | (5) Steven
 |  | (6) Michael
 | (3) Janet
 |  | (7) Robert
 |  |  | (11) David
 |  |  |  | (14) James
 |  |  | (12) Ron
 |  |  | (13) Dan
 |  | (8) Laura
 |  | (9) Ann

 | (4) Margaret
 |  | (10) Ina

empid 値によって兄弟を並べ替えるには、従業員ごとに sortcol というバイナリ文字列を作成します。この文字列は、各従業員に至るまでの管理者の従業員 ID をバイナリ値に変換し、鎖状に連結したもので構成されます。開始点はアンカーメンバです。アンカーメンバがルートの従業員の empid からバイナリ値を作成します。繰り返しのたびに、再帰メンバがバイナリ値に変換した現在の従業員 ID を、マネージャの sortcol に追加します。その後、外部クエリが sortcol で結果を並べ替えます。アンカーメンバと再帰メンバの両方の対応する列が、同じデータ型、長さ、および有効桁数を保持することに注意してください。そのため、整数値をバイナリ表現にすると 4 バイト必要であっても、sortcol 値を生成する式を varbinary(900) に変換します。900 バイトは 225 レベルに相当し、上限値としては十分だといえます。それ以上のレベルをサポートしたい場合、バイト長を増やすことができますが、両方のメンバでその操作を行う必要があります。そうしないと、エラーが発生します。

階層をインデントで表すには、従業員数のレベルと同じ回数だけ文字列 (この場合は ' | ') を複製します。続けて、従業員 ID 自体がかっこ内に追加され、最後に従業員の名前が追加されます。

他の属性で兄弟を並べ替える場合も、小さな固定長のバイナリ値に変換できれば同様の技法を使用できます。たとえば、smalldatetime 列で従業員の入社年月日を並べ替える場合です。小さな固定長のバイナリ値に変換できない属性 (従業員名など) で兄弟を並べ替える場合、希望する並べ替え順であるマネージャ ID でパーティション分割するため、最初に整数の行番号を作成します (行番号の詳細については、本書の「順位付け関数」を参照してください)。次に例を示します。

SELECT empid, empname, mgrid,
  ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
FROM Employees

次に、従業員 ID をバイナリ値に変換して連結するのではなく、従業員の位置をバイナリ値に変換して連結します。

WITH EmpPos(empid, empname, mgrid, pos)
AS
(
  SELECT empid, empname, mgrid,
    ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
  FROM Employees
),
EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
( 
  SELECT empid, empname, mgrid, 0,

    CAST(pos AS VARBINARY(900))
  FROM EmpPos
  WHERE empid = 1
  UNION ALL
  SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
    CAST(sortcol + CAST(E.pos AS BINARY(4)) AS VARBINARY(900))
  FROM EmpPos AS E
    JOIN EmpCTE AS M
      ON E.mgrid = M.empid
)
SELECT
  REPLICATE(' | ', lvl)
    + '(' + (CAST(empid AS VARCHAR(10))) + ') '
    + empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
 | (2) Andrew
 |  | (6) Michael
 |  | (5) Steven
 | (3) Janet
 |  | (9) Ann
 |  | (8) Laura
 |  | (7) Robert
 |  |  | (13) Dan
 |  |  | (11) David
 |  |  |  | (14) James
 |  |  | (12) Ron
 | (4) Margaret
 |  | (10) Ina

他の属性、または属性の組み合わせを使用して兄弟を並べ替えるには、ROW_NUMBER 関数の OVER オプションにある ORDER BY リストに、empname ではなく希望する属性を指定するだけです。

複数の親を持つ環境 : 部品表

前のセクションでは、CTE を使用して、ツリー内の各ノードに親が 1 つだけ存在する階層を扱いました。リレーションシップのより複雑なシナリオとして、各ノードに複数の親が存在する可能性があるグラフがあります。ここでは、部品表 (BOM) シナリオでの CTE の使用について説明します。 BOM は、非循環有向グラフの 1 つです。各ノードの親は複数個でもよく、ノードを直接または間接的にそのノード自体の親にすることができず、2 つのノード間のリレーションシップは対称的ではありません (たとえば、A は C を含みますが、C は A を含みません)。図 2 では、BOM シナリオでの項目間のリレーションシップを示します。

図 2. 複数の親を持つ環境

たとえば、項目 A は D、B、C を含み、項目 C は B と E を含み、項目 B は項目 A と C に含まれます。以下のコードを実行して、Items テーブルと BOM テーブルを作成し、それらのテーブルにサンプルデータを作成します。

CREATE TABLE Items
(
  itemid   VARCHAR(5)  NOT NULL PRIMARY KEY,
  itemname VARCHAR(25) NOT NULL,
  /* unit_price、 measurement_unit など、他の列 */
)
CREATE TABLE BOM
(
  itemid     VARCHAR(5) NOT NULL REFERENCES Items,
  containsid VARCHAR(5) NOT NULL REFERENCES Items,
  qty        INT        NOT NULL
  /* quantity など、他の列 */
  PRIMARY KEY(itemid, containsid),

  CHECK (itemid <> containsid)
)
SET NOCOUNT ON
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A')
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B')
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C')
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D')
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E')
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F')
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G')
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H')
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I')
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J')
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K')
INSERT INTO BOM(itemid, containsid, qty) VALUES('E', 'J', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'E', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'C', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'C', 4)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'F', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'G', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'D', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'I', 1)

Items テーブルは、各項目の行を格納します。 BOM テーブルは、グラフのノード間のリレーションシップを格納します。各リレーションシップは、親の項目 ID (itemid)、子の項目 ID (containsid)、および itemid 内の containsid の数量 (qty) で構成されます。

BOM シナリオの一般的な要求は、項目を "展開" することです。つまり、グラフを特定の項目からスキャンし、直接または間接的にその項目に含まれるすべての項目を返します。この処理は、従業員組織図のようなツリーに含まれるサブツリーを返すことに似ているので、理解しやすいかもしれません。ただし、有向グラフは、含まれている 1 つの項目に、いくつかの異なる親項目から異なる経路で到達できるので、要求は概念上少し複雑になります。たとえば、項目 A を展開するとします。項目 A から項目 B に到達する 2 つの異なる経路 A->B と A->C->B が存在することに気付きます。つまり、項目 B には 2 回到達します。これは、B に含まれるすべての項目 (F と G) に 2 回到達することを意味します。幸いにも、CTE を使用すると、このような要求は、ツリーに含まれるサブツリーを取得する要求と同じくらい単純に実現できます。以下に例を示します。

WITH BOMCTE
AS
(
  SELECT *
  FROM BOM
  WHERE itemid = 'A'
  UNION ALL
  SELECT BOM.*
  FROM BOM
    JOIN BOMCTE
      ON BOM.itemid = BOMCTE.containsid
)
SELECT * FROM BOMCTE

結果セットは以下のようになります。

itemid containsid qty        
------ ---------- -----------
A      B          2          
A      C          2          
A      D          2          
C      B          2          
C      E          3          
E      J          1          
B      F          1          
B      G          3          
B      F          1          
B      G          3

アンカーメンバは、BOM から A の直下のすべての項目を返します。再帰メンバは、CTE の直前の繰り返しで返された子項目のそれぞれについて、そこに含まれる項目を BOM と BOMCTE を結合して返します。論理上は、(出力順は必ずしもこのとおりとは限りませんが) まず (A, B)、(A, C)、(A, D) が返され、次は (B, F)、(B, G)、(C, B)、(C, E)、最後は (B, F)、(B, G)、(E, J) です。 BOM からの要求のほとんどは、最終結果に項目を複数回表示する必要がないことに注意してください。どの項目が展開に使われているのかだけがわかればよい場合、DISTINCT 句を使用して重複する項目を除去できます。以下に例を示します。

WITH BOMCTE
AS
(
  SELECT *
  FROM BOM
  WHERE itemid = 'A'
  UNION ALL
  SELECT BOM.*
  FROM BOM
    JOIN BOMCTE
      ON BOM.itemid = BOMCTE.containsid
)
SELECT DISTINCT containsid FROM BOMCTE

結果セットは以下のようになります。

containsid
----------
B         
C         
D         
E         
F         
G         
J

部品展開の処理を理解しやすくするために、すべての項目を子項目に展開したツリーで中間結果をビジュアルに示します。図 3 に、部品 A と H を展開することによって形成したツリーを項目の量と共に示します。

図 3. 部品展開

元の要求をさらに発展させると、項目自体を取得するのではなく、各項目の累積数量を取得することに関心が向かいます。たとえば、A は C が 2 個必要です。C は E が 3 個必要です。E は J が 1 個必要です。A に必要な J の総数は、A から J までの経路上の数量の積で、2*3*1 = 6 となります。図 4 は、項目を集計する前に A を構成する各項目の累積数量を示しています。

図 4. 部品展開 — 計算された数量

次の CTE は、数量を累積した積を計算します。

WITH BOMCTE(itemid, containsid, qty, cumulativeqty)
AS
(
  SELECT *, qty
  FROM BOM
  WHERE itemid = 'A'
  UNION ALL
  SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty
  FROM BOM
    JOIN BOMCTE
      ON BOM.itemid = BOMCTE.containsid
)
SELECT * FROM BOMCTE

結果セットは以下のようになります。

itemid containsid qty         cumulativeqty
------ ---------- ----------- -------------
A      B          2           2            
A      C          2           2            
A      D          2           2            
C      B          2           4            
C      E          3           6            
E      J          1           6            
B      F          1           4            
B      G          3           12           
B      F          1           2            
B      G          3           6

この CTE では、cumulativeqty 列が直前の CTE に追加されます。アンカーメンバは、含まれている項目の数量を cumulativeqty として返します。再帰メンバが、次のレベルに含まれている項目ごとに、項目の数量とその項目が含まれている項目の累積数量を乗算します。複数の経路から到達できた項目は、経路ごとの累積数量と共に複数回結果に現れることに注意してください。このような出力は、それ自体ではあまり意味がありませんが、最終的な結果までの、各項目が 1 度だけ現れる中間ステップを理解するのに役立ちます。 A に含まれる各項目の合計数量を取得するには、以下のように外部クエリで結果を containsid ごとにグループ化します。

WITH BOMCTE(itemid, containsid, qty, cumulativeqty)
AS
(

  SELECT *, qty
  FROM BOM
  WHERE itemid = 'A'
  UNION ALL
  SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty
  FROM BOM
    JOIN BOMCTE
      ON BOM.itemid = BOMCTE.containsid
)
SELECT containsid AS itemid, SUM(cumulativeqty) AS totalqty
FROM BOMCTE
GROUP BY containsid

結果セットは以下のようになります。

itemid totalqty   
------ -----------
B      6          
C      2          
D      2          
E      6          
F      6          
G      18         
J      6

PIVOT と UNPIVOT

PIVOT と UNPIVOT は、クエリの FROM 句で指定する新しいリレーショナル演算子です。これらのリレーショナル演算子を使用すると、入力テーブル値式で何らかの操作が実行され、出力テーブルを結果として生成できます。 PIVOT 演算子は、行を列に変換し、変換中に集計を実行します。指定したピボット列に基づいて入力テーブル式を拡大して、ピボット列内の一意の値それぞれに相当する列を持つ出力テーブルを生成します。 UNPIVOT 演算子は PIVOT 演算子と逆の演算を実行します。列から行への変換を行います。ピボットテーブル列に基づいて入力テーブル式を限定します。

PIVOT

PIVOT 演算子は、オープンスキーマのシナリオの処理、およびクロス集計レポートの生成に役立ちます。

オープンスキーマのシナリオでは、あらかじめわからない、またはエンティティの種類ごとに異なる一連の属性を持つエンティティを管理します。属性はアプリケーションのユーザーが動的に定義します。テーブルに多くの列を事前に定義しておいて多くの NULL 値を格納するのではなく、属性を異なる行に分割してエンティティの各インスタンスに関連する属性のみを格納します。

PIVOT を使用すると、オープンスキーマのシナリオや他のシナリオ向けのクロス集計レポートを生成できます。その際、行を列に変換し、変換中に集計を計算して、使いやすい形式でデータを表示できます。

オープンスキーマのシナリオの例として、オークションに出品した品物を追跡するデータベースがあります。品物の種類、製造日、初期価格など、一部の属性はオークションのすべての出品物に関連します。すべての品物に関連する属性だけが AuctionItems テーブルに格納されます。

CREATE TABLE AuctionItems
(
  itemid       INT          NOT NULL PRIMARY KEY NONCLUSTERED,
  itemtype     NVARCHAR(30) NOT NULL,
  whenmade     INT          NOT NULL,
  initialprice MONEY        NOT NULL,
  /* その他の列 */
)
CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemid
  ON AuctionItems(itemtype, itemid)
INSERT INTO AuctionItems VALUES(1, N'Wine',     1822,      3000)
INSERT INTO AuctionItems VALUES(2, N'Wine',     1807,       500)
INSERT INTO AuctionItems VALUES(3, N'Chair',    1753,    800000)
INSERT INTO AuctionItems VALUES(4, N'Ring',     -501,   1000000)
INSERT INTO AuctionItems VALUES(5, N'Painting', 1873,   8000000)
INSERT INTO AuctionItems VALUES(6, N'Painting', 1889,   8000000)

他の属性は品物の種類に固有です。また、別の種類の新しい品物が継続的に追加されます。このような属性は、先ほどのテーブルとは別に、品物の各属性を行ごとに格納する ItemAttributes テーブルに格納できます。各行には、次のように、品物の ID、属性名、および属性値が含まれます。

CREATE TABLE ItemAttributes
(
  itemid    INT          NOT NULL REFERENCES AuctionItems,

  attribute NVARCHAR(30) NOT NULL,
  value     SQL_VARIANT  NOT NULL, 
  PRIMARY KEY (itemid, attribute)
)
INSERT INTO ItemAttributes
  VALUES(1, N'manufacturer', CAST(N'ABC'              AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(1, N'type',         CAST(N'Pinot Noir'       AS NVARCHAR(15)))
INSERT INTO ItemAttributes
  VALUES(1, N'color',        CAST(N'Red'              AS NVARCHAR(15)))
INSERT INTO ItemAttributes
  VALUES(2, N'manufacturer', CAST(N'XYZ'              AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(2, N'type',         CAST(N'Porto'            AS NVARCHAR(15)))
INSERT INTO ItemAttributes
  VALUES(2, N'color',        CAST(N'Red'              AS NVARCHAR(15)))
INSERT INTO ItemAttributes
  VALUES(3, N'material',     CAST(N'Wood'             AS NVARCHAR(15)))
INSERT INTO ItemAttributes
  VALUES(3, N'padding',      CAST(N'Silk'             AS NVARCHAR(15)))
INSERT INTO ItemAttributes
  VALUES(4, N'material',     CAST(N'Gold'             AS NVARCHAR(15)))
INSERT INTO ItemAttributes
  VALUES(4, N'inscription',  CAST(N'One ring ...'     AS NVARCHAR(50)))
INSERT INTO ItemAttributes
  VALUES(4, N'size',         CAST(10                  AS INT))
INSERT INTO ItemAttributes
  VALUES(5, N'artist',       CAST(N'Claude Monet'     AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(5, N'name',         CAST(N'Field of Poppies' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(5, N'type',         CAST(N'Oil'              AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(5, N'height',       CAST(19.625              AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
  VALUES(5, N'width',        CAST(25.625              AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
  VALUES(6, N'artist',       CAST(N'Vincent Van Gogh' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(6, N'name',         CAST(N'The Starry Night' AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(6, N'type',         CAST(N'Oil'              AS NVARCHAR(30)))
INSERT INTO ItemAttributes
  VALUES(6, N'height',       CAST(28.75               AS NUMERIC(9,3)))
INSERT INTO ItemAttributes
  VALUES(6, N'width',        CAST(36.25               AS NUMERIC(9,3)))

value 列には sql_variant データ型を使用することに注意してください。これは、さまざまな属性値がさまざまなデータ型を持つ可能性があるためです。たとえば、size 属性の属性値は整数型、name 属性の属性値は文字列型です。

ItemAttributes テーブルからデータを抽出し、絵画の出品物 (品物 5 と 6) を各行に、各属性を列ごとに表示するとします。 PIVOT 演算子を使用しない場合、以下のようにクエリを記述する必要があります。

SELECT
  itemid,
  MAX(CASE WHEN attribute = 'artist'  THEN value END) AS [artist],
  MAX(CASE WHEN attribute = 'name'    THEN value END) AS [name],
  MAX(CASE WHEN attribute = 'type'    THEN value END) AS [type],
  MAX(CASE WHEN attribute = 'height'  THEN value END) AS [height],
  MAX(CASE WHEN attribute = 'width'   THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN(5,6)
GROUP BY itemid

結果セットは以下のようになります。

itemid artist           name             type       height width
------ ---------------- ---------------- ---------- ------ ------
5      Claude Monet     Field of Poppies Oil        19.625 25.625
6      Vincent Van Gogh The Starry Night Oil        28.750 36.250

PIVOT 演算子を使用すると、以下のように、より短くて読みやすいコードで、同じ結果を得ることができます。

SELECT *
FROM ItemAttributes AS ATR
  PIVOT

  (
    MAX(value)
    FOR attribute IN([artist], [name], [type], [height], [width])
  ) AS PVT
WHERE itemid IN(5,6)

PIVOT 演算子を理解するには、ほとんどの新しい機能と同様に、試しに使ってみることです。 PIVOT 構文のいくつかの要素は明示されています。あとは、この新しい演算子を使用しないクエリとそれらの要素との関係を理解するだけです。それ以外の要素は明示しません。

以下に、PIVOT 演算子のセマンティクスを理解するのに役立ついくつかの用語を示します。

table_expression (テーブル式)

PIVOT 演算子が動作する仮想テーブル (FROM 句と PIVOT 演算子の間の部分) です。上記の場合は、ItemAttributes AS ATR に相当します。

pivot_column (ピボット列)

結果列に変換する値を持つ、table_expression の列です。上記の場合は、attribute に相当します。

column_list (列リスト)

結果列として表示する、pivot_column の値のリスト (IN 句の後のカッコ内) です。値は有効な識別子として指定する必要があります。上記の場合は、[artist]、[name]、[type]、[height]、[width] です。

aggregate_function (集計関数)

結果のデータまたは列の値を生成するために使用する集計関数です。上記の場合は、MAX() に相当します。

value_column (値列)

aggregate_function の引数として使用する、table_expression の列です。上記の場合は、value に相当します。

group_by_list (GROUP BY リスト)

明示しない部分、つまり、結果をグループ化するために使用した pivot_column および value_column を除く、table_expression のすべての列です。上記の場合は、itemid に相当します。

select_list (選択リスト)

SELECT 句に続く列のリストで、group_by_list および column_list の任意の列を含めることができます。別名を使用して、結果列の名前を変更できます。上記の場合は、* に相当し、group_by_list と column_list に含まれるすべての列を返します。

PIVOT 演算子は、GROUP BY 句を含むクエリで列を指定した場合と同様に、group_by_list の一意の値ごとに 1 行を返します。 group_by_list は暗黙的に設定されます。クエリ内のどの場所にも明示されません。このリストには、pivot_column と value_column を除く、table_expression のすべての列が含まれます。これを理解することが、PIVOT 演算子を使用して記述したクエリがそのとおりに動作する理由、および一部のケースでエラーになる理由を理解するのに重要です。

結果列には、group_by_list と **の値が含まれると考えられます。アスタリスク (*) を指定すると、クエリにより両方のリストが返されます。 aggregate_function で value_column を引数として結果列のデータ部分または結果列の値を計算します。

以下のコードは、PIVOT 演算子を使用するクエリ内のさまざまな要素を例示しています。

SELECT * -- itemid、[artist]、[name]、[type]、[height]、[width]
FROM ItemAttributes AS ATR
  PIVOT
  (
    MAX(value)
    FOR attribute IN([artist], [name], [type], [height], [width])
  ) AS PVT
WHERE itemid IN(5,6)

以下のコードは、PIVOT 演算子を使用しないクエリにさまざまな要素を関連付けています。

SELECT
  itemid,
  MAX(CASE WHEN attribute = 'artist'  THEN value END) AS [artist],
  MAX(CASE WHEN attribute = 'name'    THEN value END) AS [name],
  MAX(CASE WHEN attribute = 'type'    THEN value END) AS [type],
  MAX(CASE WHEN attribute = 'height'  THEN value END) AS [height],
  MAX(CASE WHEN attribute = 'width'   THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN(5,6)
GROUP BY itemid

**には、値を明示的に指定する必要があることに注意してください。 PIVOT 演算子には、静的クエリの pivot_column から動的に値を派生するオプションは用意されていません。動的 SQL を使用して、この処理を行うクエリ文字列をユーザー自身で作成できます。

上記の PIVOT クエリを発展させ、絵画に関連するすべての属性を各出品物について返すことにします。 AuctionItems 内の属性および ItemAttributes 内の属性を含めます。以下のクエリを実行してみると、エラーが返されます。

SELECT *
FROM AuctionItems AS ITM
  JOIN ItemAttributes AS ATR
    ON ITM.itemid = ATR.itemid
  PIVOT

  (
    MAX(value)
    FOR attribute IN([artist], [name], [type], [height], [width])
  ) AS PVT
WHERE itemtype = 'Painting'

以下のエラーメッセージが表示されます。

.Net SqlClient Data Provider: メッセージ 8156、レベル 16、状態 1、行 1

列 'itemid' が 'PVT' に複数回指定されました。

PIVOT が動作の対象とする table_expression は、FROM 句と PIVOT 句の間の部分によって返される仮想テーブルであることを覚えておいてください。このクエリでは、仮想テーブルに itemid 列のインスタンスが 2 つ含まれています。1 つは AuctionItems で、もう 1 つは ItemAttributes で作成されたインスタンスです。以下のようにクエリを修正しがちですが、この場合もエラーが発生します。

SELECT ITM.itemid, itemtype, whenmade, initialprice, 
  [artist], [name], [type], [height], [width]
FROM AuctionItems AS ITM
  JOIN ItemAttributes AS ATR
    ON ITM.itemid = ATR.itemid
  PIVOT
  (
    MAX(value)
    FOR attribute IN([artist], [name], [type], [height], [width])
  ) AS PVT
WHERE itemtype = 'Painting'

以下のエラーメッセージが表示されます。

.Net SqlClient Data Provider: メッセージ 8156、レベル 16、状態 1、行 1
列 'itemid' が 'PVT' に複数回指定されました。
.Net SqlClient Data Provider: メッセージ 107、レベル 15、状態 1、行 1
列のプレフィックス 'ITM' は、テーブル名と一致しないか、クエリで使用される別名と一致しません。

上記のとおり、PIVOT 演算子は、select_list の列ではなく、table_expression によって返される仮想テーブル上で動作します。 select_list は PIVOT 演算子が操作を実行した後で評価されるので、group_by_list と column_list だけを参照できます。そのため、別名 ITM は select_list で認識されません。これを理解すると、動作対象の列だけを含む table_expression に PIVOT を指定する必要があることに気付きます。これには、グループ化列 (itemid 1 つと itemtype、whenmade、および initialprice のみ)、ピボット列 (attribute)、および値列 (value) が含まれます。 CTE または派生テーブルを使用して、これを実現できます。以下に、CTE を使用する例を示します。

WITH PNT
AS
(
  SELECT ITM.*, ATR.attribute, ATR.value
  FROM AuctionItems AS ITM
    JOIN ItemAttributes AS ATR
      ON ITM.itemid = ATR.itemid
  WHERE ITM.itemtype = 'Painting'
)
SELECT * FROM PNT
  PIVOT
  (
    MAX(value)
    FOR attribute IN([artist], [name], [type], [height], [width])
  ) AS PVT

結果セットは以下のようになります。

itemid itemtype whenmade initialprice artist           name             type height width
------ -------- -------- ------------ ---------------- ---------------- ---- ------ -----
5      Painting 1873     8000000.0000 Claude Monet     Field of Poppies Oil  19.62  25.62
6      Painting 1889     8000000.0000 Vincent Van Gogh The Starry Night Oil  28.75  36.25

以下に、派生テーブルを使用する例を示します。

SELECT *
FROM (SELECT ITM.*, ATR.attribute, ATR.value
      FROM AuctionItems AS ITM
         JOIN ItemAttributes AS ATR
         ON ITM.itemid = ATR.itemid
      WHERE ITM.itemtype = 'Painting') AS PNT
  PIVOT

  (
    MAX(value)
    FOR attribute IN([artist], [name], [type], [height], [width])
  ) AS PVT

また、クロス集計レポートを生成してデータを要約するときに、PIVOT を使用することもできます。たとえば、AdventureWorks データベースの Purchasing.PurchaseOrderHeader テーブルを使用して、購入方法 ID に列へのピボットを行い、従業員別かつ購入方法別の注文数を返すとします。 PIVOT 演算子を関連データだけで指定する必要があることに注意し、派生テーブルを使用して次のクエリを記述します。

SELECT EmployeeID, [1] AS SM1, [2] AS SM2,
  [3] AS SM3, [4] AS SM4, [5] AS SM5
FROM (SELECT PurchaseOrderID, EmployeeID, ShipMethodID
      FROM Purchasing.PurchaseOrderHeader) ORD
  PIVOT
  (
   COUNT(PurchaseOrderID)
   FOR ShipMethodID IN([1], [2], [3], [4], [5])
  ) AS PVT

結果セットは以下のようになります。

EmployeeID  SM1         SM2         SM3         SM4         SM5
----------- ----------- ----------- ----------- ----------- -----------
164         56          62          12          89          141
198         24          27          6           45          58
223         56          67          17          98          162
231         50          67          12          81          150
233         55          62          12          106         125
238         53          58          13          102         134
241         50          59          13          108         130
244         55          47          17          93          148
261         58          54          11          120         117
264         50          58          15          86          151
266         58          68          14          116         144
274         24          26          6           41          63

COUNT(PurchaseOrderID) 関数は、一覧の出荷方法別の行数をカウントします。 PIVOT 内では COUNT(*) を使用できないことに注意してください。列の別名を使用して、より理解しやすい名前を結果列に付けます。出荷方法が少数で出荷方法 ID が事前にわかっている場合、PIVOT を使用して出荷方法別の注文数を個別の列に表示するのが合理的です。

また、式から派生した値のピボットを行うこともできます。たとえば、年に列へのピボットを行い、注文年別かつ従業員別の合計運送料の値を返すとします。注文年は、以下のように、OrderDate 列から派生させます。

SELECT EmployeeID, [2001] AS Y2001, [2002] AS Y2002,
  [2003] AS Y2003, [2004] AS Y2004
FROM (SELECT EmployeeID, YEAR(OrderDate) AS OrderYear, Freight
      FROM Purchasing.PurchaseOrderHeader) AS ORD
  PIVOT
  (
   SUM(Freight)
   FOR OrderYear IN([2001], [2002], [2003], [2004])
  ) AS PVT

結果セットは以下のようになります。

EmployeeID  Y2001       Y2002       Y2003       Y2004
----------- ----------- ----------- ----------- ------------
164         509.9325    14032.0215  34605.3459  105087.7428
198         NULL        5344.4771   14963.0595  45020.9178
223         365.7019    12496.0776  37489.2896  117599.4156
231         6.8025      9603.0502   37604.3258  75435.8619
233         1467.1388   9590.7355   32988.0643  98603.745
238         17.3345     9745.1001   37836.583   100106.3678
241         221.1825    6865.7299   35559.3883  114430.983
244         5.026       5689.4571   35449.316   74690.3755
261         NULL        10483.27    32854.9343  73992.8431
264         NULL        10337.3207  37170.1957  82406.4474
266         4.2769      9588.8228   38533.9582  115291.2472
274         NULL        1877.2665   13708.9336  41011.3821

データウェアハウスのシナリオでは、クロス集計レポートを使用するのが一般的です。次の OrdersFact テーブルを考えてみましょう。このテーブルには、AdventureWorks から発注データと発注詳細データが挿入されます。

CREATE TABLE OrdersFact
(
  OrderID    INT      NOT NULL,
  ProductID  INT      NOT NULL,
  CustomerID NCHAR(5) NOT NULL,
  OrderYear  INT      NOT NULL,
  OrderMonth INT      NOT NULL,

  OrderDay   INT      NOT NULL,
  Quantity   INT      NOT NULL,
  PRIMARY KEY(OrderID, ProductID)
)
INSERT INTO OrdersFact
  SELECT O.SalesOrderID, OD.ProductID, O.CustomerID,
    YEAR(O.OrderDate) AS OrderYear, MONTH(O.OrderDate) AS OrderMonth,
    DAY(O.OrderDate) AS OrderDay, OD.OrderQty
  FROM Sales.SalesOrderHeader AS O
    JOIN Sales.SalesOrderDetail AS OD
      ON O.SalesOrderID = OD.SalesOrderID

年を行に、月を列に返して、年月別の合計数量を取得するには、次のクエリを使用します。

SELECT *
FROM (SELECT OrderYear, OrderMonth, Quantity
      FROM OrdersFact) AS ORD
 PIVOT
 (
  SUM(Quantity)
  FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
 ) AS PVT

結果セットは以下のようになります。

OrderYear  1     2     3     4     5     6     7     8     9     10    11    12
---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
2001       NULL  NULL  NULL  NULL  NULL  NULL  966   2209  1658  1403  3132  2480
2002       1040  2303  1841  1467  3179  2418  7755  11325 9066  5584  8268  6672
2003       3532  5431  4132  5694  8278  6444  11288 18986 18681 11607 14771 15855
2004       9227  10999 11314 12239 15656 15805 2209  NULL  NULL  NULL  NULL  NULL

PIVOT は、年と月の交差部分が存在しない場合は NULL 値を返します。年は、指定した月のいずれかとの交差部分があるかどうかに関係なく、入力テーブル式 (派生テーブル ORD) にあれば、結果に表示されます。つまり、存在するすべての月を指定から除外した場合、すべての列が NULL という行が返されることもあります。ただし、結果が NULL 値でも、必ずしも交差部分が存在しないことを表すとは限りません。列が NULL 値を許可しない場合を除いては、数量列のベースデータの NULL 値が原因で結果に NULL が表示されることがあります。 NULL を表示せず、代わりに別の値 (0 など) を表示するには、選択リストで ISNULL() 関数を使用します。

SELECT OrderYear,
  ISNULL([1],  0) AS M01,
  ISNULL([2],  0) AS M02,
  ISNULL([3],  0) AS M03,
  ISNULL([4],  0) AS M04,
  ISNULL([5],  0) AS M05,
  ISNULL([6],  0) AS M06,
  ISNULL([7],  0) AS M07,
  ISNULL([8],  0) AS M08,
  ISNULL([9],  0) AS M09,
  ISNULL([10], 0) AS M10,
  ISNULL([11], 0) AS M11,
  ISNULL([12], 0) AS M12
FROM (SELECT OrderYear, OrderMonth, Quantity
      FROM OrdersFact) AS ORD
 PIVOT
 (
  SUM(Quantity)
  FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
 ) AS PVT

結果セットは以下のようになります。

OrderYear  1     2     3     4     5     6     7     8     9     10    11    12
---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
2001       0     0     0     0     0     0     966   2209  1658  1403  3132  2480
2002       1040  2303  1841  1467  3179  2418  7755  11325 9066  5584  8268  6672
2003       3532  5431  4132  5694  8278  6444  11288 18986 18681 11607 14771 15855
2004       9227  10999 11314 12239 15656 15805 2209  0     0     0     0     0

派生テーブル内で ISNULL(Quantity, 0) を使用すると、存在しない交差部分に PIVOT が生成した NULL 値ではなく、Quantity 列のベースの NULL 値 (これが存在する場合) だけが処理されます。

2003 年、2004 年の各年の第一四半期中の各年月について、顧客 ID が 1 から 9 までのそれぞれの顧客の合計数量を返すとします。年月の値を行に、顧客 ID を列に格納するには、次のクエリを使用します。

SELECT *
FROM (SELECT CustomerID, OrderYear, OrderMonth, Quantity
      FROM OrdersFact
      WHERE CustomerID BETWEEN 1 AND 9
        AND OrderYear IN(2003, 2004)

        AND OrderMonth IN(1, 2, 3)) AS ORD
  PIVOT
  (
    SUM(Quantity)
    FOR CustomerID IN([1],[2],[3],[4],[5],[6],[7],[8],[9])
  ) AS PVT

結果セットは以下のようになります。

OrderYear   OrderMonth  1    2    3    4    5    6    7    8    9
----------- ----------- ---- ---- ---- ---- ---- ---- ---- ---- ----
2003        1           NULL NULL NULL 105  NULL NULL 8    NULL NULL
2004        1           NULL NULL NULL 80   NULL NULL NULL NULL NULL
2003        2           NULL 5    NULL NULL NULL NULL NULL NULL 15
2004        2           NULL 10   NULL NULL NULL NULL NULL 6    3
2003        3           NULL NULL 105  NULL 15   NULL NULL NULL NULL
2004        3           NULL NULL 103  NULL 25   4    NULL NULL NULL

ここでは CustomerID をピボット列、Quantity を値列として使用しているので、 OrderYear と OrderMonth が暗黙の GROUP BY リストになります。

ただし、ピボット列は 1 つしか使用できないので、年と月の値の組み合わせを列として表示する場合、PIVOT 演算子にそれらの組み合わせを渡す前に、年と月の値を手動で連結する必要があります。

SELECT *
FROM (SELECT CustomerID, OrderYear*100+OrderMonth AS YM, Quantity
      FROM OrdersFact
      WHERE CustomerID BETWEEN 1 AND 9
        AND OrderYear IN(2003, 2004)
        AND OrderMonth IN(1, 2, 3)) AS ORD
  PIVOT
  (
    SUM(Quantity)
    FOR YM IN([200301],[200302],[200303],[200401],[200402],[200403])
  ) AS PVT

結果セットは以下のようになります。

CustomerID 200301 200302 200303 200401 200402 200403
---------- ------ ------ ------ ------ ------ ------
2          NULL   5      NULL   NULL   10     NULL
3          NULL   NULL   105    NULL   NULL   103
6          NULL   NULL   NULL   NULL   NULL   4
4          105    NULL   NULL   80     NULL   NULL
8          NULL   NULL   NULL   NULL   6      NULL
5          NULL   NULL   15     NULL   NULL   25

7          8      NULL   NULL   NULL   NULL   NULL
9          NULL   15     NULL   NULL   3      NULL

UNPIVOT

UNPIVOT 演算子により、既にピボットを行ったデータを標準に戻すことができます。 UNPIVOT 演算子の構文と要素は、PIVOT 演算子の構文と要素に似ています。

たとえば、上記のセクションの AuctionItems テーブルを考えてみましょう。

itemid      itemtype                 whenmade    initialprice
----------- ------------------------ ----------- --------------
1           Wine                      1822          3000.0000
2           Wine                      1807           500.0000
3           Chair                    1753         800000.0000
4           Ring                      -501       1000000.0000
5           Painting                  1873       8000000.0000
6           Painting                  1889       8000000.0000

ItemAttributes テーブルに属性が格納されているのと同様の形で、各属性を異なる行に表示するとします。以下に例を示します。

itemid      attribute       value
----------- --------------- -------
1           itemtype        Wine
1           whenmade        1822
1           initialprice    3000.00
2           itemtype        Wine
2           whenmade        1807
2           initialprice    500.00
3           itemtype        Chair
3           whenmade        1753
3           initialprice    800000.00
4           itemtype        Ring
4           whenmade        -501
4           initialprice    1000000.00
5           itemtype        Painting
5           whenmade        1873
5           initialprice    8000000.00
6           itemtype        Painting
6           whenmade        1889
6           initialprice    8000000.00

UNPIVOT クエリで、列 itemtype、whenmade、および initialprice を行に変換します。各行には、項目 ID、属性、および値を格納します。新しく、列名 attribute と value を指定する必要があります。これらの列は、PIVOT 演算子の pivot_column と value_column に対応します。 attribute 列が値として取るのは、変換する列の実際の名前 (itemtype、whenmade、および initialprice) です。 value 列は、3 つの列から値を取得し、1 つの列に格納します。わかりやすくするために、まず、無効な UNPIVOT クエリを示し、次に一部制限を適用するクエリを示します。

SELECT itemid, attribute, value
FROM AuctionItems
  UNPIVOT
  (
    value FOR attribute IN([itemtype], [whenmade], [initialprice])
  ) AS UPV

PIVOT 演算子の引数と同様に、FOR 句の前に value_column の名前 (この場合は、value) を指定します。 FOR 句に続いて pivot_column の名前 (この場合は、attribute) を指定します。次に、IN 句を指定し、この句の中に pivot_column の値の取得元の列名を列挙します。列の一覧は、PIVOT 演算子では **と呼ばれます。このクエリを実行すると、以下のエラーが発生します。

.Net SqlClient Data Provider: メッセージ 8167、レベル 16、状態 1、行 1

列 "whenmade" の型が、UNPIVOT リストで指定されている他の列の型と競合しています。

格納先の value 列に格納される値は、いくつかの異なる取得元の列 (** で指定します) から取得します。 UNPIVOT ではすべての列値を 1 つの列に格納するので、** のすべての列を同じデータ型、長さ、および有効桁数にする必要があります。この制限を満たすため、3 つの列を同じデータ型に変換するテーブル式を UNPIVOT 演算子内に指定できます。データ型は、異なる取得元の列を同一のデータ型に変換できて、かつ元の型を保持できる sql_variant が適しています。上記のクエリにこの制限を適用するため以下のように修正し、目的の結果を得ることができます。

SELECT itemid, attribute, value
FROM (SELECT itemid,
        CAST(itemtype     AS SQL_VARIANT) AS itemtype,
        CAST(whenmade     AS SQL_VARIANT) AS whenmade,
        CAST(initialprice AS SQL_VARIANT) AS initialprice
      FROM AuctionItems) AS ITM
  UNPIVOT
  (
    value FOR attribute IN([itemtype], [whenmade], [initialprice])
  ) AS UPV

attribute 結果列のデータ型は sysname になります。これは、SQL Server でオブジェクト名を格納するのに使用するデータ型です。

UNPIVOT 演算子は、結果から value 列の NULL 値を取り除くので、PIVOT 演算子と完全に逆の演算が行われるとは考えないでください。

AuctionItems の列を行に変換したら、UNPIVOT 演算の結果と ItemAttributes テーブルからの行の和集合を作成して、ひとまとまりの結果を得ることができるようになります。

SELECT itemid, attribute, value
FROM (SELECT itemid,
        CAST(itemtype     AS SQL_VARIANT) AS itemtype,
        CAST(whenmade     AS SQL_VARIANT) AS whenmade,
        CAST(initialprice AS SQL_VARIANT) AS initialprice
      FROM AuctionItems) AS ITM
  UNPIVOT
  (
    value FOR attribute IN([itemtype], [whenmade], [initialprice])
  ) AS UPV
UNION ALL
SELECT *
FROM ItemAttributes
ORDER BY itemid, attribute

結果セットは以下のようになります。

itemid      attribute       value
----------- --------------- -------------
1           color           Red
1           initialprice    3000.00
1           itemtype        Wine
1           manufacturer    ABC
1           type            Pinot Noir
1           whenmade        1822
2           color           Red
2           initialprice    500.00
2           itemtype        Wine
2           manufacturer    XYZ
2           type            Porto
2           whenmade        1807
3           initialprice    800000.00
3           itemtype        Chair

3           material        Wood
3           padding         Silk
3           whenmade        1753
4           initialprice    1000000.00
4           inscription     One ring
4           itemtype        Ring
4           material        Gold
4           size            10
4           whenmade        -501
5           height          19.625
5           initialprice    8000000.00
5           itemtype        Painting
5           name            Field of Poppies
5           artist          Claude Monet
5           type            Oil
5           whenmade        1873
5           width           25.625
6           height          28.750
6           initialprice    8000000.00
6           itemtype        Painting
6           name            The Starry Night
6           artist          Vincent Van Gogh
6           type            Oil
6           whenmade        1889
6           width           36.250

APPLY

APPLY リレーショナル演算子を使用して、外部テーブル式の行ごとに、指定したテーブル値関数を呼び出すことができます。 APPLY は、JOIN リレーショナル演算子を使用するのと同様に、クエリの FROM 句で指定します。 APPLY には、CROSS APPLY と OUTER APPLY の 2 つの形式があります。 SQL Server 2005 Beta 2 では、APPLY 演算子により、相関サブクエリのテーブル値関数を参照できます。

CROSS APPLY

CROSS APPLY は、外部テーブル式の行ごとにテーブル値関数を呼び出します。テーブル値関数の引数として外部テーブルの列を参照できます。 CROSS APPLY は、テーブル値関数のそれぞれの呼び出しから返されたすべての結果をまとめた結果セットを返します。特定の外部行でテーブル値関数が空のセットを返した場合、結果にはその外部行が返されません。たとえば、以下のテーブル値関数は、2 つの整数値を引数として受け取り、列として最大値と最小値を持つ 1 行のテーブルを返します。

CREATE FUNCTION dbo.fn_scalar_min_max(@p1 AS INT, @p2 AS INT) RETURNS TABLE
AS
RETURN
  SELECT
    CASE
      WHEN @p1 < @p2 THEN @p1
      WHEN @p2 < @p1 THEN @p2
      ELSE COALESCE(@p1, @p2)
    END AS mn,
    CASE
      WHEN @p1 > @p2 THEN @p1
      WHEN @p2 > @p1 THEN @p2
      ELSE COALESCE(@p1, @p2)
    END AS mx
GO
SELECT * FROM fn_scalar_min_max(10, 20)

結果セットは以下のようになります。

mn          mx         
----------- -----------
10          20

以下のような T1 テーブルがあるとします。

CREATE TABLE T1
(
  col1 INT NULL,
  col2 INT NULL
)

INSERT INTO T1 VALUES(10, 20)
INSERT INTO T1 VALUES(20, 10)
INSERT INTO T1 VALUES(NULL, 30)
INSERT INTO T1 VALUES(40, NULL)
INSERT INTO T1 VALUES(50, 50)

T1 の行ごとに fn_scalar_min_max を呼び出すとします。その場合、以下のような CROSS APPLY クエリを記述できます。

SELECT *
FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M

結果セットは以下のようになります。

col1        col2        mn          mx         
----------- ----------- ----------- -----------
10          20          10          20         
20          10          10          20         
NULL        30          30          30         
40          NULL        40          40         
50          50          50          50

テーブル値関数により、特定の外部行に対して複数の行が返される場合、その外部行が複数回返されます。この資料の前半、「再帰クエリと共通テーブル式」 (従業員組織図シナリオ) で使用した Employees テーブルを考えてみます。同じデータベースに以下の Departments テーブルも作成します。

CREATE TABLE Departments
(
  deptid    INT NOT NULL PRIMARY KEY,
  deptname  VARCHAR(25) NOT NULL,
  deptmgrid INT NULL REFERENCES Employees
)
SET NOCOUNT ON
INSERT INTO Departments VALUES(1, 'HR',           2)
INSERT INTO Departments VALUES(2, 'Marketing',    7)
INSERT INTO Departments VALUES(3, 'Finance',      8)
INSERT INTO Departments VALUES(4, 'R&D',          9)
INSERT INTO Departments VALUES(5, 'Training',     4)
INSERT INTO Departments VALUES(6, 'Gardening', NULL)

多くの部門は、Employees テーブルの従業員に対応するマネージャ ID があります。ただし、Gardening 部門のように、マネージャを持たない部門もあります。 Employees テーブルのマネージャが必然的に部門の管理も行います。以下のテーブル値関数は、従業員 ID を引数として受け取り、その従業員と、その従業員のあらゆるレベルのすべての部下を返します。

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE
(
  empid   INT NOT NULL,
  empname VARCHAR(25) NOT NULL,
  mgrid   INT NULL,
  lvl     INT NOT NULL
)
AS
BEGIN
  WITH Employees_Subtree(empid, empname, mgrid, lvl)
  AS
  ( 
    -- アンカー メンバ (AM)
    SELECT empid, empname, mgrid, 0
    FROM employees
    WHERE empid = @empid
    UNION all
    -- 再帰メンバ (RM)
    SELECT e.empid, e.empname, e.mgrid, es.lvl+1
    FROM employees AS e
      JOIN employees_subtree AS es
        ON e.mgrid = es.empid
  )
  INSERT INTO @TREE
    SELECT * FROM Employees_Subtree
  RETURN
END
GO

各部門のマネージャのあらゆるレベルの部下をすべて返すには、以下のクエリを使用します。

SELECT *
FROM Departments AS D
  CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

結果セットは以下のようになります。

deptid      deptname   deptmgrid   empid       empname    mgrid       lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1           HR         2           2           Andrew     1           0
1           HR         2           5           Steven     2           1
1           HR         2           6           Michael    2           1
2           Marketing  7           7           Robert     3           0
2           Marketing  7           11          David      7           1
2           Marketing  7           12          Ron        7           1
2           Marketing  7           13          Dan        7           1
2           Marketing  7           14          James      11          2
3           Finance    8           8           Laura      3           0
4           R&D        9           9           Ann        3           0
5           Training   4           4           Margaret   1           0
5           Training   4           10          Ina        4           1

ここでは、注意する点が 2 つあります。まず、Departments の各行が、部門のマネージャを引数とする fn_getsubtree から返された行の数だけ重複しています。次に、Gardening 部門は fn_getsubtree が空のセットを返すので、結果に表示されません。

実用的な使用法として、グループごとに n 行を返したい場合がよくありますが、そのような場合にも CROSS APPLY 演算子を使用できます。たとえば、次の関数は特定の顧客の最新の注文から指定した件数の注文を返します。

USE AdventureWorks
GO
CREATE FUNCTION fn_getnorders(@custid AS INT, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP(@n) *
  FROM Sales.SalesOrderHeader
  WHERE CustomerID = @custid
  ORDER BY OrderDate DESC
GO

CROSS APPLY 演算子を使用した以下の簡単なクエリで、顧客ごとに最新 2 件の注文を取得できます。

SELECT O.*
FROM Sales.Customer AS C
  CROSS APPLY fn_getnorders(C.CustomerID, 2) AS O

TOP の機能強化の詳細については、この資料の後半の「TOP の機能強化」を参照してください。

OUTER APPLY

OUTER APPLY は CROSS APPLY によく似ていますが、テーブル値関数により空のセットが返された外部テーブルの行も返します。テーブル値関数の列に対応する列値として NULL 値が返されます。たとえば、上記のセクションの Departments テーブルに対するクエリを変更し、CROSS APPLY の代わりに OUTER APPLY を使用します。出力の最終行に注目してください。

SELECT *
FROM Departments AS D
  OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST

結果セットは以下のようになります。

deptid      deptname   deptmgrid   empid       empname    mgrid       lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1           HR         2           2           Andrew     1           0
1           HR         2           5           Steven     2           1
1           HR         2           6           Michael    2           1
2           Marketing  7           7           Robert     3           0
2           Marketing  7           11          David      7           1
2           Marketing  7           12          Ron        7           1
2           Marketing  7           13          Dan        7           1
2           Marketing  7           14          James      11          2
3           Finance    8           8           Laura      3           0
4           R&D        9           9           Ann        3           0
5           Training   4           4           Margaret   1           0
5           Training   4           10          Ina        4           1
6           Gardening  NULL        NULL        NULL       NULL        NULL

相関サブクエリのテーブル値関数

SQL Server 2000 では、相関サブクエリ内でテーブル値関数を参照できません。 SQL Server 2005 Beta 2 では、APPLY リレーショナル演算子を採用したことでこの制限が取り払われます。サブクエリ内からでも、外部クエリの列をテーブル値関数に引数として渡すことができます。たとえば、3 人以上の部下が所属するマネージャが担当する部門だけを返す場合、以下のクエリを記述できます。

SELECT *
FROM Departments AS D
WHERE (SELECT COUNT(*)
       FROM fn_getsubtree(D.deptmgrid)) >= 3
deptid      deptname                  deptmgrid  
----------- ------------------------- -----------
1           HR                        2          
2           Marketing                 7

新しい DRI 操作のサポート :

SET DEFAULT と SET NULL

ANSI SQL では、使用が想定される 4 つの参照操作が定義され、FOREIGN KEY 制約をサポートしています。外部キーによって参照しているテーブルに DELETE 操作または UPDATE 操作を行ったときのシステムの反応を示す参照操作を指定してください。 SQL Server 2000 は、この 4 つの操作のうち NO ACTION と CASCADE の 2 つの操作をサポートします。 SQL Server 2005 Beta 2 では、SET DEFAULT 参照操作と SET NULL 参照操作に対するサポートが追加されます。

SET DEFAULT 参照操作および SET NULL 参照操作により、宣言参照整合性 (DRI) 機能が拡張されます。 2 つの参照操作は、外部キー宣言の ON UPDATE 句や ON DELETE 句と関連して使用します。 SET DEFAULT を指定した場合、参照元のテーブルで行を削除 (ON DELETE) または参照元のキーを更新 (ON UPDATE) すると、参照先テーブルの関連付けを持つ行の参照先の列値がその列の既定値に設定されます。同様に、SET NULL オプションを指定した場合、参照先の列で NULL 値を許可していれば値が NULL に設定されます。

たとえば、以下の Customers テーブルには、3 人の実在する顧客と 1 人のダミーの顧客が含まれています。

CREATE TABLE Customers
(
  customerid CHAR(5) NOT NULL,
  /* その他の列 */
  CONSTRAINT PK_Customers PRIMARY KEY(customerid)
)

INSERT INTO Customers VALUES('DUMMY')
INSERT INTO Customers VALUES('FRIDA')
INSERT INTO Customers VALUES('GNDLF')
INSERT INTO Customers VALUES('BILLY')

Orders テーブルで注文を追跡します。注文は、必ずしも実在の顧客に割り当てる必要はありません。顧客 ID を指定せずに注文を入力すると、その注文は既定で DUMMY 顧客 ID に割り当てられます。 Customers テーブルからレコードが削除されるときに、Orders テーブルの関連付けを持つ行の customerid 列に NULL を設定するとします。 customerid 列が NULL の注文は、"孤立する" ことになります。つまり、その注文はどの顧客にも所属しません。 Customers テーブルの customerid 列への更新も許可するとします。 Orders テーブルの関連付けを持つ行を連鎖的に更新したいのですが、会社のビジネスルールではそのような規定ではなく、ID が変更された顧客に属していた注文は既定の顧客 (DUMMY) に関連付けることになっているとします。 Customers テーブルの customerid 列が更新されたときに、Orders テーブルの関連する顧客 ID (customerid) に既定値 'DUMMY' を設定します。この場合、外部キーを持つ Orders テーブルを以下のように作成し、いくつか注文を入力します。

CREATE TABLE Orders
(
  orderid    INT      NOT NULL,
  customerid CHAR(5)  NULL DEFAULT('DUMMY'),
  orderdate  DATETIME NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid),
  CONSTRAINT FK_Orders_Customers
    FOREIGN KEY(customerid)
    REFERENCES Customers(customerid)
      ON DELETE SET NULL
      ON UPDATE SET DEFAULT
)
INSERT INTO Orders VALUES(10001, 'FRIDA', '20040101')
INSERT INTO Orders VALUES(10002, 'FRIDA', '20040102')
INSERT INTO Orders VALUES(10003, 'BILLY', '20040101')
INSERT INTO Orders VALUES(10004, 'BILLY', '20040103')
INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104')
INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')

SET NULL オプションと SET DEFAULT オプションをテストするため、以下の DELETE ステートメントと UPDATE ステートメントを実行します。

DELETE FROM Customers
WHERE customerid = 'FRIDA'
UPDATE Customers
  SET customerid = 'DOLLY'
WHERE customerid = 'BILLY'

その結果、以下のように FRIDA の注文の customerid 列には NULL 値が割り当てられ、BILLY の注文には DUMMY が割り当てられます。

orderid     customerid orderdate             
----------- ---------- ----------------------
10001       NULL       1/1/2004 12:00:00 AM  
10002       NULL       1/2/2004 12:00:00 AM  
10003       DUMMY      1/1/2004 12:00:00 AM  
10004       DUMMY      1/3/2004 12:00:00 AM  
10005       GNDLF      1/4/2004 12:00:00 AM  
10006       GNDLF      1/5/2004 12:00:00 AM

SET DEFAULT オプションを使用する場合、参照先列の既定値が NULL 以外で参照元のテーブルに対応する値を持たないときは、トリガとなる操作を実行するとエラーが発生します。たとえば、Customers から DUMMY 顧客を削除し、次に、GNDLF の customerid を GLDRL に更新すると、エラーが発生します。 UPDATE が SET DEFAULT 操作のトリガとなり、GNDLF の元の注文を DUMMY 顧客 ID に割り当てようとしますが、Customers テーブルには対応する行がありません。

DELETE FROM Customers
WHERE customerid = 'DUMMY'
UPDATE Customers
  SET customerid = 'GLDRL'
WHERE customerid = 'GNDLF'
.Net SqlClient Data Provider: メッセージ 547、レベル 16、状態 0、行 1
UPDATE ステートメントは COLUMN FOREIGN KEY 制約 "FK_Orders_Customers" と競合しています。
競合が発生したのは、データベース "tempdb"、テーブル "Customers"、列 "customerid" です。
ステートメントは終了されました。

定義済みの参照操作を含む、外部キーの詳細については、sys.foreign_keys を参照してください。

パフォーマンスとエラー処理の機能強化

ここでは、以前のバージョンの SQL Server のパフォーマンスの問題点の対処、データ読み込み機能の追加、およびエラー管理機能の大幅な改善について説明します。このような機能強化には、BULK 行セットプロバイダ、TRY...CATCH エラー処理構造などがあります。

BULK 行セットプロバイダ

BULK は、リレーショナル形式のファイルデータにアクセスするための OPENROWSET 関数で指定する新しい行セットプロバイダです。ファイルからデータを取得するには、BULK オプション、ファイル名、および bcp.exe か手動で作成したフォーマットファイルを指定します。 OPENROWSET が返すテーブルの別名に続けて、結果列の名前をかっこで囲んで指定できます。

以下に、OPENROWSET で指定できるすべてのオプションを使用した新しい構文を示します。

OPENROWSET
( { 'provider_name' 
, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } 
   , { [ catalog. ] [ schema. ] object | 'query' }   
| BULK 'data_filename', 
{FORMATFILE = 'format_file_path' [, <bulk_options>] |
    SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB} 
}
) 

 ::=
[ , CODEPAGE  = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]  
[ , FIRSTROW = first_row ] 
[ , LASTROW = last_row ]
[ , ROWS_PER_BATCH = 'rows_per_batch']
[ , MAXERRORS = 'max_errors']
   [ , ERRORFILE ='file_name']
}
)

たとえば次のクエリは、テキストファイル 'c:\temp\textfile1.txt' から列を 3 つ返し、結果列に col1、col2、col3 という別名を付けます。

SELECT col1, col2, col3
  FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',
         FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)

BULK オプションを使用するときは、SINGLE_BLOB、SINGLE_CLOB、SINGLE_NCLOB のいずれかのオプション (後で説明します) を使用する場合を除き、フォーマットファイルを指定する必要があります。したがって、データファイルの種類、フィールドターミネータ、および行ターミネータは指定する必要がありません。この他、FORMATFILE の省略可能なオプションとして、CODEPAGE、FIRSTROW、LASTROW、ROW_PER_BATCH、MAXERRORS、および ERRORFILE を指定できます。ほとんどのオプションは SQL Server 2000 の BULK INSERT コマンドで使用できますが、ERRORFILE は概念上新しいオプションです。このファイルには、入力データファイルから形式エラーがある (つまり、OLEDB 行セットに変換できない) 行が 0 行以上格納されます。エラーがある行は、データファイルから "そのまま" このエラーファイルにコピーされます。エラーを修正してデータを適切な形式にしたら、同じコマンドを使用して簡単に再読み込みを行うことができます。エラーファイルはコマンドの実行を開始する時点で作成されます。ファイルが既に存在している場合、エラーが発生します。このファイルを確認すると、エラーが発生した行を簡単に特定できますが、その原因まではわかりません。原因を明らかにするため、.ERROR.txt という拡張子の制御ファイルが自動的に作成され、ERRORFILE の各行を参照して、エラーを診断します。

BULK 行セットプロバイダを使用して OPENROWSET が返した結果をテーブルに格納し、一括読み込み操作のテーブルオプションを指定できます。たとえば、次のコードは、読み込み先テーブルでの制約チェックを無効にして、上記のクエリの結果をテーブル MyTable に読み込みます。

INSERT INTO MyTable WITH (IGNORE_CONSTRAINTS)
  SELECT col1, col2, col3
  FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',
         FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)

IGNORE_CONSTRAINTS オプション以外に、読み込み操作に対して指定できるテーブルヒントは BULK_KEEPIDENTITY、BULK_KEEPNULLS、および IGNORE_TRIGGERS です。

BULK プロバイダを使用して、ファイルデータを大きなオブジェクト型の列値 1 つとして返すことができます。その場合は、SINGLE_CLOB (文字データの場合)、SINGLE_NCLOB (Unicode データの場合)、SINGLE_BLOB (バイナリデータの場合) のうち、いずれかのオプションを指定します。このオプションのいずれかを使用するときは、フォーマットファイルを指定しません。 (INSERT ステートメントまたは UPDATE ステートメントを使用して) ファイルを読み込むことができる大きなオブジェクト列のデータ型は、VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX)、XML のいずれかです。可変長の列に使用する MAX 指定子、および XML データ型の詳細については、この資料の後半を参照してください。

大きな列にファイルを読み込む例として、次の UPDATE ステートメントは、テーブル CustomerData の列 txt_data に顧客 101 のテキストファイル 'c:\temp\textfile101.txt' を読み込みます。

UPDATE CustomerData
  SET txt_data  = (SELECT txt_data FROM OPENROWSET(
    BULK 'c:\temp\textfile101.txt', SINGLE_CLOB) AS F(txt_data))
WHERE custid = 101

大きな列は、1 度に 1 つしか更新できません。

次の例で、INSERT ステートメントを使用して顧客 102 のバイナリファイルを大きな列に読み込む方法を示します。

INSERT INTO CustomerData(custid, binary_data)
  SELECT 102 AS custid, binary_data
  FROM OPENROWSET(
    BULK 'c:\temp\binfile102.dat', SINGLE_BLOB) AS F(binary_data)

例外処理

SQL Server 2005 Beta 2 では、TRY...CATCH Transact-SQL 構造の形式の、単純でありながら非常に優れた例外処理メカニズムを導入しています。

以前のバージョンの SQL Server では、エラーが起こりそうなすべてのステートメントの後にエラー処理コードを含める必要がありました。エラーチェックコードを集中管理する場合は、ラベルと GOTO ステートメントを使用する必要があります。さらに、データ型の変換エラーなどのエラーは、バッチが終了する原因となるので Transact-SQL ではトラップできません。 SQL Server 2005 Beta 2 では、このような問題の多くに対応しています。

バッチが終了する原因となっていたエラーをキャッチおよび処理できるようになりました。ただし切断を引き起こさないエラーに限ります (テーブルやデータベースの整合性エラー、ハードウェアエラーなど、重要度 21 以上のエラーが一般的です)。

BEGIN TRY/END TRY ブロック内に実行するコードを記述し、続けて BEGIN CATCH/END CATCH ブロックにエラー処理コードを記述します。 TRY ブロックには対応する CATCH ブロックが必要であることに注意してください。CATCH ブロックがない場合、構文エラーになります。簡単な例として、以下のような Employees テーブルがあるとします。

CREATE TABLE Employees
(
  empid   INT         NOT NULL,
  empname VARCHAR(25) NOT NULL,
  mgrid   INT         NULL,
  /* その他の列 */
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT CHK_Employees_empid CHECK(empid > 0),
  CONSTRAINT FK_Employees_Employees
    FOREIGN KEY(mgrid) REFERENCES Employees(empid)
)

新しい従業員の行をテーブルに挿入するコードを記述します。また、エラーの状況に応じて、何らかの修正動作も実行します。新しい TRY...CATCH 構造を以下のように使用します。

BEGIN TRY
  INSERT INTO Employees(empid, empname, mgrid)
     VALUES(1, 'Emp1', NULL)

  PRINT 'After INSERT.'
END TRY
BEGIN CATCH
  PRINT 'INSERT failed.'
  /* 修正動作の実行 */
END CATCH

このコードを初めて実行するときは、"After INSERT" と出力されます。 2 回目にこのコードを実行するときは、"INSERT Failed" と出力されます。

TRY ブロック内のコードがエラーなしで実行されると、対応する CATCH ブロックの後の最初のステートメントに制御が渡されます。 TRY ブロック内のステートメントでエラーが発生すると、対応する CATCH ブロック内の最初のステートメントに制御が渡されます。 CATCH ブロックでトラップされたエラーは、呼び出し元のアプリケーションには返されません。アプリケーションにエラー情報を渡す場合、(RAISERROR やクエリの結果セットを使用するなどして) ユーザーがロジックを用意する必要があります。 CATCH ブロックでは、ERROR_NUMBER()、ERROR_MESSAGE()、ERROR_SEVERITY()、および ERROR_STATE() の 4 つの関数を使用してすべてのエラー情報を入手できます。 4 つの関数は CATCH ブロックの任意の場所から複数回クエリできます。関数の値は保たれます。これとは対照的に、@@error 関数は DECLARE を除く任意のステートメントから影響を受けるため CATCH ブロックの最初のステートメントでクエリする必要があります。 ERROR_NUMBER() は @@error の代わりに使用できます。他の 3 つは、残りの情報をエラーで生成されたとおりに返します。 SQL Server 2005 より前の SQL Server リリースでは、そのような情報を入手できませんでした。

バッチまたはルーチン (ストアドプロシージャ、トリガ、ユーザー定義関数、動的なコード) で処理されない例外が発生した場合、そのバッチまたはルーチンが上位レベルのコードの TRY ブロック内で呼び出されていたのであれば、その上位のコードの、対応する CATCH ブロックに制御が渡されます。例外が発生したバッチまたはルーチンが、上位のコードの TRY ブロック内で呼び出されていない場合、呼び出し履歴のさらに上位レベルの TRY ブロックが検索され、最初に見つかった TRY...CATCH 構造の CATCH ブロックに制御が渡されます。 TRY ブロックが見つからなかった場合、呼び出し元のアプリケーションにエラーを返します。

より詳しい例を示します。以下のコードは、障害の原因となったエラーの種類に応じて対応を変え、コードのどの部分がアクティブになっているかを示すメッセージを出力します。

PRINT 'Before TRY...CATCH block.'
BEGIN TRY
  PRINT '  Entering TRY block.'
  INSERT INTO Employees(empid, empname, mgrid) VALUES(2, 'Emp2', 1)
  PRINT '    After INSERT.'
  PRINT '  Exiting TRY block.'
END TRY

BEGIN CATCH
  PRINT '  Entering CATCH block.'
  IF ERROR_NUMBER() = 2627
  BEGIN
    PRINT '    Handling PK violation...'
  END
  ELSE IF ERROR_NUMBER() = 547
  BEGIN
    PRINT '    Handling CHECK/FK constraint violation...'
  END
  ELSE IF ERROR_NUMBER() = 515
  BEGIN
    PRINT '    Handling NULL violation...'
  END
  ELSE IF ERROR_NUMBER() = 245
  BEGIN
    PRINT '    Handling conversion error...'
  END
  ELSE
  BEGIN
    PRINT '    Handling unknown error...'
  END
  PRINT '    Error Number:   ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
  PRINT '    Error Message:  ' + ERROR_MESSAGE()
  PRINT '    Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))
  PRINT '    Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10))
  PRINT '  Exiting CATCH block.'
END CATCH
PRINT 'After TRY...CATCH block.'

CATCH ブロック内で複数回呼び出されている ERROR_NUMBER() 関数は、CATCH ブロックに制御を渡したエラーの番号を常に返します。このコードは、既に挿入されている従業員 1 の部下として従業員 2 を挿入します。最初にこのコードを実行するときは、エラーが発生することなく完了し、次の出力が生成されます。

Before TRY...CATCH block.
  Entering TRY block.

    After INSERT.
  Exiting TRY block.
After TRY...CATCH block.

CATCH ブロックがスキップされることに注意してください。このコードを 2 回目に実行すると、以下の出力が生成されます。

Before TRY...CATCH block.
  Entering TRY block.
  Entering CATCH block.
    Handling PK violation...
    Error Number:   2627
    Error Message:  制約 'PK_Employees' の PRIMARY KEY 違反。オブジェクト 'Employees' には
    重複したキーを挿入できません。
    Error Severity: 14
    Error State   : 1
  Exiting CATCH block.
After TRY...CATCH block.

TRY ブロックには入りましたが、完了しなかったことに注意してください。主キー違反の結果として制御が CATCH ブロックに渡され、CATCH ブロックでエラーを識別し、処理しています。同様に、従業員 ID として無効な値 (CHECK 制約に違反する 0、employeeid で許可されていない NULL、INT に変換できない 'a' など) を代入すると、値に応じたエラーが発生して、対応する処理コードが作動します。

TRY ブロックで明示的なトランザクション処理を行っている場合、CATCH ブロックのエラー処理コードでトランザクションの状態を調査して、対策を決定できます。 SQL Server 2005 には、トランザクションの状態を返す新しい関数 XACT_STATE() が用意されます。この関数が取りうる戻り値は 0、-1、および 1 です。戻り値 0 は開かれているトランザクションがないことを意味します。トランザクションをコミットまたはロールバックしようとすると、エラーが発生します。戻り値 1 はトランザクションが開かれていて、コミットまたはロールバックのいずれも可能であることを意味します。実行する処理とエラー処理ロジックに従って、トランザクションをコミットするかロールバックするかを決定する必要があります。戻り値 -1 はトランザクションは開かれていますが、"コミット不能な状態" であることを意味します。これは SQL Server 2005 で導入された新しいトランザクションの状態です。旧バージョンあれば TRY ブロック内のトランザクションが中止するようなエラーが発生したときに、そのトランザクションがコミット不能な状態になります (重要度 17 以上が一般的です)。コミット不能なトランザクションは開いているすべてのロックを保持するので、データは読み取り専用になります。トランザクションログへの書き込みが必要な操作は実行できないので、トランザクションがコミット不能な間はデータを変更できません。トランザクションを終了するには、ロールバックする必要があります。トランザクションをコミットすることはできません。何らかの変更を行うにはロールバックする必要があります。次の例で、XACT_STATE() 関数の使用方法を示します。

BEGIN TRY
  BEGIN TRAN
    INSERT INTO Employees(empid, empname, mgrid) VALUES(3, 'Emp3', 1)

    /* その他の操作 */
  COMMIT TRAN
  PRINT 'Code completed successfully.'
END TRY
BEGIN CATCH
  PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' found.'
  IF (XACT_STATE()) = -1
  BEGIN
     PRINT 'Transaction is open but uncommittable.'
     /* ...データの調査... */
     ROLLBACK TRANSACTION -- ROLLBACK のみ可能
     /* ...エラーの処理... */
  END
  ELSE IF (XACT_STATE()) = 1
  BEGIN
     PRINT 'Transaction is open and committable.'
     /* ...エラーの処理... */
     COMMIT TRANSACTION -- または ROLLBACK
  END
  ELSE
  BEGIN
     PRINT 'No open transaction.'
     /* ...エラーの処理... */
  END
END CATCH

TRY ブロックで、明示的なトランザクション内のコードを実行します。新しい従業員の行を挿入し、そのトランザクション内でその他の操作も行います。 CATCH ブロックで、エラー番号を出力してトランザクションの状態を調査し、対策を決定します。トランザクションが開いていてコミット不能な場合、CATCH ブロックでデータを調査してトランザクションをロールバックしてから、データを書き換える必要がある修正策を講じます。トランザクションが開いていてコミット可能な場合、CATCH ブロックでエラーを処理してコミットします (ロールバックすることもできます)。トランザクションが開かれていない場合、エラーが処理されます。コミットもロールバックも行われません。このコードを初めて実行すると、従業員 3 を格納する新しい従業員の行が挿入され、次の出力が生成されて正常に終了します。

Code completed successfully.

このコードを 2 回目に実行したときは、主キー違反が発生して次の出力が返されます。

Error: 2627 found.
Transaction is open and committable.

Transact-SQL に影響を与える SQL Server 2005 Beta 2 の他の機能

ここでは、Transact-SQL に影響を与える SQL Server 2005 Beta 2 でのその他の機能強化について簡単に説明します。扱う内容は、TOP の機能強化、結果を伴うデータ操作言語 (DML)、動的な列に使用する MAX 指定子、XML/XQuery、データ定義言語 (DDL) トリガ、キュー処理と SQL Server Service Broker、および DML のイベントと通知です。

TOP の機能強化

SQL Server Version 7.0 と SQL Server 2000 では、TOP オプションにより、SELECT クエリが返す行数または行のパーセンテージを制限できます。ただし、引数として定数を渡す必要があります。 SQL Server 2005 Beta 2 では、TOP が主に以下の点で強化されています。

クエリで処理する行数や行のパーセンテージを返す数値式を指定できるようになりました。オプションで、変数やサブクエリを使用することもできます。
DELETE クエリ、UPDATE クエリ、INSERT クエリで TOP オプションを使用できるようになりました。

TOP オプションを使用するクエリの新しい構文を以下に示します。

SELECT [TOP (<expression>) [PERCENT] [WITH TIES]]
FROM <table_name>...[ORDER BY...]
DELETE [TOP (<expression>) [PERCENT]] FROM <table_name> ...
UPDATE [TOP (<expression>>) [PERCENT]] <table_name> SET ...
INSERT [TOP (<expression>) [PERCENT]] INTO <table_name> ...

数値式は、かっこで囲んで指定する必要があります。 SELECT クエリでは、旧バージョンとの互換性のためだけに、かっこを付けない定数の指定がサポートされます。式は、完全に独立している必要があります。つまり、サブクエリを使用する場合は、外部クエリ内のテーブルの列を参照することはできません。 PERCENT オプションを指定しない場合は、式は暗黙に bigint データ型に変換可能である必要があります。 PERCENT オプションを指定する場合、式は暗黙に float に変換可能で、0 から 100 までの範囲に収まる必要があります。WITH TIES オプションと ORDER BY 句は SELECT クエリだけでサポートされます。

たとえば、以下のコードは、TOP オプションの引数として変数を使用し、最新の注文から指定した数の注文を返します。

USE AdventureWorks
DECLARE @n AS BIGINT
SET @n = 2
SELECT TOP(@n) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

この機能強化は、必要な行数をストアドプロシージャやユーザー定義関数の引数として渡すときに特に役に立ちます。完全に独立したサブクエリを使用することによって、「月間の注文数の平均値を計算し、計算結果と同じ件数の最新の注文を返す」というような動的な要求に答えることができます。

USE AdventureWorks
SELECT TOP(SELECT
             COUNT(*)/DATEDIFF(month, MIN(OrderDate), MAX(OrderDate))
           FROM Purchasing.PurchaseOrderHeader) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

以前のバージョンの SQL Server は SET ROWCOUNT オプションで、クエリで処理される行数を制限できました。たとえば、SET ROWCOUNT は、1 つの大きなトランザクションではなく、いくつかの小さなトランザクションで大量のデータを定期的に消去する場合に使用されるのが一般的です。

SET ROWCOUNT 1000
DELETE FROM BigTable WHERE datetimecol < '20000101'
WHILE @@rowcount > 0
  DELETE FROM BigTable WHERE datetimecol < '20000101'
SET ROWCOUNT 0

この方法で SET ROWCOUNT を使用することにより、削除プロセス中にトランザクションログをバックアップして再利用できるので、ロックのエスカレートを防ぐことができます。 SET ROWCOUNT を使用する代わりに、TOP を次のように使用できるようになりました。

DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101'
WHILE @@rowcount > 0
  DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101'

TOP オプションを使用するとき、"終端の行" と TOP が使用されているかどうかがオプティマイザにより認識されるので、より効果的なプランを生成できます。

SELECT クエリでいつでも TOP を指定できるので、INSERT ステートメントで TOP を使用する必要はないと考えるかもしれませんが、たとえば、次のように、EXEC コマンドの結果または UNION 演算の結果を挿入するときに、役に立つことがわかります。

INSERT TOP ... INTO ...
  EXEC ...
INSERT TOP ... INTO ...
  SELECT ... FROM T1
  UNION ALL
  SELECT ... FROM T2
  ORDER BY ...

結果を伴う DML

SQL Server 2005 には新しい OUTPUT 句が導入され、変更系のステートメント (INSERT、UPDATE、DELETE) のデータをテーブル変数に返すことができるようになりました。結果を伴う DML が有用なシナリオは、削除とアーカイブ、メッセージ処理アプリケーションなど多数です。新しい OUTPUT 句の構文を以下に示します。

OUTPUT <dml_select_list> INTO @table_variable

トリガを使用する場合と同じく、挿入および削除するテーブルを参照することで、変更前や変更後の行のイメージにアクセスできます。 INSERT ステートメントでは、挿入するテーブルだけにアクセスできます。 DELETE ステートメントでは、削除するテーブルだけにアクセスできます。 UPDATE ステートメントでは、挿入するテーブルと削除したテーブルの両方にアクセスできます。

結果を伴う DML が有効な、削除とアーカイブのシナリオの例として、大きな Orders テーブルがあり、定期的に履歴データを削除するとします。削除したデータは、OrdersArchive というアーカイブテーブルにコピーします。 @DeletedOrders というテーブル変数を宣言し、この資料の「TOP の機能強化」で説明した削除方法で、ブロック単位で履歴データ (2003 年より前の注文など) を削除するループに入ります。さらに OUTPUT 句を追加し、句の中で、削除するすべての行のすべての属性を @DeletedOrders テーブル変数にコピーしてから、そのテーブル変数のすべての行を INSERT INTO ステートメントを使用して OrdersArchive テーブルにコピーします。

DECLARE @DeletedOrders TABLE
(
  orderid   INT,
  orderdate DATETIME,
  empid     INT,
  custid    VARCHAR(5),
  qty       INT
)
WHILE 1=1

BEGIN
  BEGIN TRAN
    DELETE TOP(5000) FROM Orders
      OUTPUT deleted.* INTO @DeletedOrders
    WHERE orderdate < '20030101'
    INSERT INTO OrdersArchive
      SELECT * FROM @DeletedOrders
  COMMIT TRAN
  DELETE FROM @DeletedOrders
  IF @@rowcount < 5000
    BREAK
END

メッセージ処理シナリオの例として、次の Messages テーブルを考えます。

USE tempdb
CREATE TABLE Messages
(
  msgid   INT          NOT NULL IDENTITY ,
  msgdate DATETIME     NOT NULL DEFAULT(GETDATE()),
  msg     VARCHAR(MAX) NOT NULL,
  status  VARCHAR(20)  NOT NULL DEFAULT('new'),
  CONSTRAINT PK_Messages 
    PRIMARY KEY NONCLUSTERED(msgid),
  CONSTRAINT UNQ_Messages_status_msgid 
    UNIQUE CLUSTERED(status, msgid),
  CONSTRAINT CHK_Messages_status
    CHECK (status IN('new', 'open', 'done'))
)

メッセージ ID、入力日、メッセージテキスト、およびメッセージの状態 (処理前 "new"、処理中 "open"、処理後 "done" のいずれか) をメッセージごとに保存します。

次のコードで、1 秒ごとにランダムなテキストをメッセージに挿入するループを使用してメッセージを生成するセッションをシミュレーションします。状態列に既定値 "new" を割り当てたので、新しく挿入したメッセージは "new" 状態になります。このコードを同時に複数のセッションから実行します。

USE tempdb
SET NOCOUNT ON
DECLARE @msg AS VARCHAR(MAX)
WHILE 1=1
BEGIN
  SET @msg = 'msg' + RIGHT('000000000'
    + CAST(CAST(RAND()*2000000000 AS INT)+1 AS VARCHAR(10)), 10)
  INSERT INTO dbo.Messages(msg) VALUES(@msg)
  WAITFOR DELAY '00:00:01';
END

次のコードで、メッセージを以下の手順で処理するセッションをシミュレーションします。

メッセージを処理し続ける無限ループを形成します。
UPDATE TOP(1) ステートメントを使用して、アクセス可能な新しいメッセージを 1 つロックします。このときロックした行をスキップするため READPAST ヒントを指定し、メッセージの状態を "open" に変更します。
OUTPUT 句を使用して、メッセージの属性を @Msgs テーブル変数に格納します。
メッセージを処理します。
Messages テーブルと @Msgs テーブル変数を結合して、メッセージの状態を "done" に設定します。
Messages テーブルに新しいメッセージが見つからなかった場合、1 秒待機します。

このコードを複数のセッションから実行します。

USE tempdb
SET NOCOUNT ON
DECLARE @Msgs TABLE(msgid INT, msgdate DATETIME, msg VARCHAR(MAX))
WHILE 1 = 1
BEGIN
  UPDATE TOP(1) Messages WITH(READPAST) SET status = 'open'
    OUTPUT inserted.msgid, inserted.msgdate, inserted.msg
    INTO @Msgs
  WHERE status = 'new'
  IF @@rowcount > 0
  BEGIN
    PRINT 'Processing message...'

    -- ここでメッセージを処理します
    SELECT * FROM @msgs
    UPDATE M
      SET status = 'done'
    FROM Messages AS M
      JOIN @Msgs AS N
        ON M.msgid = N.msgid;
    DELETE FROM @Msgs
  END
  ELSE
  BEGIN
    PRINT 'No messages to process.'
    WAITFOR DELAY '00:00:01'
  END
END

シミュレーションを完了したら、メッセージの挿入と処理を行っているすべてのセッションを終了し、Messages を削除します。

USE tempdb
DROP TABLE Messages

動的な列に使用する MAX 指定子

SQL Server 2005 では、(MAX) という構文を使用する MAX 指定子を導入し、可変長のデータ型 VARCHAR、NVARCHAR、および VARBINARY の機能を強化しました。 MAX 指定子を伴った可変長のデータ型は、機能を強化した TEXT、NTEXT、および IMAGE データ型に代えて使用します。大きなオブジェクトデータ型 TEXT、NTEXT、および IMAGE の代わりに MAX 指定子を伴った可変長のデータ型を使用すると、いくつかの利点があります。値をインラインで保存するときおよびポインタを使用するときを SQL Server で内部的に判断するので、明示的なポイント操作が不要です。データの大小にかかわらず、統一的なプログラミングモデルを使用できます。 MAX 指定子を伴った可変長のデータ型は、列、変数、パラメータ、比較、トリガ、すべての文字列関数などでサポートされます。

MAX 指定子の使用例として、次のコードで CustomerData というテーブルを作成します。

CREATE TABLE CustomerData
(
  custid INT  NOT NULL PRIMARY KEY,
  txt_data    VARCHAR(MAX)   NULL,
  ntxt_data   NVARCHAR(MAX)  NULL,

  binary_data VARBINARY(MAX) NULL
)

テーブルに含まれる列は、主キーとして使用する custid、および NULL 値を許可する txt_data、ntxt_data、および binary_data で、主キー列以外には大きなデータを格納できるように、それぞれデータ型 VARCHAR(MAX)、NVARCHAR(MAX)、および VARBINARY(MAX) を定義しています。

MAX 指定子を伴った動的な列からチャンクを読み取るには、通常の動的な列の場合と同様に SUBSTRING 関数を使用します。チャンクを更新するには、強化されて WRITE メソッドを使用できるようになった UPDATE ステートメントの構文を使用します。以下に、強化後の UPDATE ステートメントの構文を示します。

UPDATE table_name
SET column_name.WRITE(@chunk, @offset, @len)
WHERE ...

WRITE メソッドは @offset 位置から @len 個の文字を切り取り、その部分に @chunk を挿入します。 @offset は 0 から始まるので、オフセット 0 が @chunk の最初の文字の位置を表します。 WRITE メソッドの使用例を示すため、まずは顧客 ID が 102、txt_data 列の値が "Customer 102 text data" という行を CustomerData テーブルに挿入します。

INSERT INTO CustomerData(custid,txt_data)
  VALUES(102, 'Customer 102 text data')

次の UPDATE ステートメントは "102" を "one hundred and two" に置換します。

UPDATE CustomerData
  SET txt_data.WRITE('one hundred and two', 9, 3)
WHERE custid = 102

@chunk が NULL の場合、@len を無視して @offset の位置で値を削除します。次のステートメントはオフセット 28 以降のすべてのデータを削除します。

UPDATE CustomerData
  SET txt_data.WRITE(NULL, 28, 0)
WHERE custid = 102

@len が NULL の場合、@offset 以降のすべての文字を削除して @chunk を追加します。次のステートメントはオフセット 9 以降のすべてのデータを削除し "102" を追加します。

UPDATE CustomerData
  SET txt_data.WRITE('102', 9, NULL)
WHERE custid = 102

@offset が NULL の場合、@len を無視して末尾に @chunk を追加します。次のステートメントは末尾に文字列 " is discontinued" を追加します。

UPDATE CustomerData
  SET txt_data.WRITE(' is discontinued', NULL, 0)
WHERE custid = 102

XML と XQuery

SQL Server 2005 Beta 2 では XML 関連の機能強化がいくつか行われ、XML 構造のデータをネイティブに格納、クエリ、および更新できるようになりました。同じデータベースに XML とリレーショナルデータの両方を格納できるので、ストレージやクエリ処理に既存のデータベースエンジンを活用できます。

新しく xml データ型が導入されました。 xml データ型は、テーブルの列に使用でき、インデックスを付けることもできます。変数、ビュー、関数、およびストアドプロシージャに使用することもできます。 xml データ型は、リレーショナル FOR XML クエリで生成したり、OPENXML を使用してリレーショナル行セットとしてアクセスしたりできます。データベースにはスキーマをインポートしたり、データベースからスキーマをエクスポートしたりできます。スキーマを使用して、XML データの検証と制約を行うことができます。 XQuery を使用することによって、XML 型が指定されたデータをクエリおよび変更できます。 xml データ型は、トリガ、レプリケーション、一括コピー、DBCC、およびフルテキスト検索でサポートされます。ただし、xml は比較できません。つまり、xml 列に主キー制約、一意制約、外部キー制約を定義することはできません。

以下に、xml データ型を使用する例を示します。以下のコードでは、@x という XML 変数を定義し、その変数に顧客注文データを読み込みます。

USE AdventureWorks
DECLARE @x AS XML
SET @x = (SELECT C.CustomerID, O.SalesOrderID 
          FROM Sales.Customer C 
            JOIN Sales.SalesOrderHeader O 
              ON C.CustomerID=O.CustomerID 
          ORDER BY C.CustomerID
          FOR XML AUTO, TYPE)
SELECT @x

以下のコードでは、xml 列を持つテーブルを作成し、OPENROWSET 関数を使用して、その列に XML ファイルの一括読み込みを行います。

CREATE TABLE T1
(
  keycol INT NOT NULL PRIMARY KEY,
  xmldoc XML NULL
)

INSERT INTO T1(keycol, xmldoc)
  SELECT 1 AS keycol, xmldoc
  FROM OPENROWSET(BULK 'C:\documents\mydoc.xml', SINGLE_NCLOB)
       AS X(xmldoc)

また、SQL Server 2005 Beta 2 では XQuery をサポートします。XQuery は W3C 標準の XML クエリ言語です。マイクロソフトは SQL Server で標準言語を拡張し、挿入、更新、および削除に XQuery を使用できるようにしています。 XQuery は、ユーザー定義型 (UDT) スタイルのメソッドを使用して、Transact-SQL に埋め込みます。

以下に XQuery に用意されているクエリメソッドを示します。

XML データの操作 : @x.query (xquery string) は XML を返します。
存在確認 : @x.exist (xquery string) はビットを返します。
スカラ値を返す : @x.value (xquery string, sql_type string) は sql_type を返します。

XQuery には変更メソッド @x.modify (xDML string) が用意されています。

一例として、Jobs というテーブルがあり、列 jobinfo に XML 形式のジョブ情報を含んでいるとします。以下のクエリは、ある条件を満たす行ごとにいくつか操作を行った後、ID と XML データを返します。 WHERE 句でメソッド jobinfo.exist() を呼び出し、目的の行をフィルタ選択します。このメソッドは、edited 要素の date 属性が Transact-SQL 変数 @date よりも大きな jobinfo 列を持つ行に対してのみ 1 を返します。 jobinfo.query() メソッドを呼び出すことにより、返される行ごとに XML の結果が生成されます。 query() メソッドは、jobinfo で見つかった job 要素ごとに、そのジョブの id 属性に基づく id 属性を備えた jobschedule 要素、および jobinfo の start 属性と end 属性に基づくデータを備えた begin サブ要素と end サブ要素を生成します。

SELECT id, jobinfo.query(
  'for $j in //job 
    return
      <jobschedule id="{$j/@id}">      
        <begin>{data($j/@start)}</begin>
        <end>{data($j/@end)}</end>
      </jobschedule>')
FROM Jobs
WHERE 1 = jobinfo.exist(
  '//edited[@date > sql:variable("@date")]')

以下の value() メソッド呼び出しにより、jobinfo 内の最初のジョブの start 属性が Transact-SQL の日付時刻形式で返されます。

SELECT id, jobinfo.value('(//job)[1]/@start', 'DATETIME') AS startdt
FROM Jobs
WHERE id = 1

また、XQuery を使用してデータを変更することもできます。たとえば、次のコードを使用して Employees テーブルの従業員 1 の empinfo XML 列を更新します。resume 要素の edited サブ要素の date 属性を新しい値に更新します。

UPDATE Employees SET empinfo.modify(
   'update /resume/edited/@date 
    to xs:date("2000-6-20")')
WHERE empid = 1

DDL トリガ

以前のバージョンの SQL Server では、AFTER トリガを定義できるのは、テーブルに対して実行された DML ステートメント (INSERT、UPDATE、および DELETE) だけでした。 SQL Server 2005 Beta 2 では、サーバーまたはデータベース全体をスコープとする DDL イベントにトリガを定義できます。 CREATE_TABLE のような個別の DDL ステートメント、または DDL_DATABASE_LEVEL_EVENTS のようなステートメントのグループに、DDL トリガを定義できます。トリガ内で、eventdata() 関数にアクセスすることによって、トリガを起動したイベントに関するデータを取得できます。この関数はイベントに関する XML データを返します。各イベントのスキーマは SQL Server Events 基本スキーマを継承します。

イベント情報には、以下の情報が含まれます。

イベントが発生した時点
イベントを実行した SPID
イベントの種類
影響を受けたオブジェクト
SET オプション
トリガを起動した Transact-SQL ステートメント

以前のバージョンの SQL Server で使用するトリガと同様に、DDL トリガはトリガを起動したトランザクションのコンテキストで実行されます。トリガを起動したイベントを元に戻す場合、ROLLBACK ステートメントを実行できます。たとえば、以下のトリガは、現在のデータベースに新しいテーブルを作成するのを防ぎます。

CREATE TRIGGER trg_capture_create_table ON DATABASE FOR CREATE_TABLE
AS
-- デバッグ用の PRINT イベント情報
PRINT 'CREATE TABLE Issued'
PRINT EventData()
-- EventData() が返したデータを調査し、結果に応じて対応できます。
RAISERROR('New tables cannot be created in this database.', 16, 1)
ROLLBACK
GO

トリガが作成されたデータベース内で CREATE TABLE ステートメントを実行すると、以下の出力が得られます。

CREATE TABLE T1(col1 INT)
CREATE TABLE Issued
<EVENT_INSTANCE>
  <PostTime>2003-04-17T13:55:47.093</PostTime>
  <SPID>53</SPID>
  <EventType>CREATE_TABLE</EventType>
  <Database>testdb</Database>
  <Schema>dbo</Schema>
  <Object>T1</Object>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
                QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE TABLE T1(col1 INT)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

.Net SqlClient Data Provider: メッセージ 50000、レベル 16、状態 1、プロシージャ 
trg_capture_create_table、行 10
New tables cannot be created in this database.
.Net SqlClient Data Provider: メッセージ 3609、レベル 16、状態 1、行 1
トランザクションはトリガで終了しました。バッチは中止されました。

この資料の XML 出力は、読みやすくするために手を加えてあります。このコードを実行すると、手を加えていない XML が出力されます。

トリガを削除するには、以下のステートメントを実行します。

DROP TRIGGER trg_capture_create_table ON DATABASE

DDL が特に役立つシナリオには、DDL 変更の整合性チェック、監査などのシナリオがあります。 DDL 整合の実施例として、次のデータベースレベルのトリガを使用すると、主キーがないテーブルの作成が拒否されます。

CREATE TRIGGER trg_create_table_with_pk ON DATABASE FOR CREATE_TABLE
AS
DECLARE @eventdata AS XML, @objectname AS NVARCHAR(257),
  @msg AS NVARCHAR(500)

SET @eventdata = eventdata()
SET @objectname = 
  N'[' + CAST(@eventdata.query('data(//SchemaName)') AS SYSNAME)
  + N'].[' + 
  CAST(@eventdata.query('data(//ObjectName)') AS SYSNAME) + N']'
IF OBJECTPROPERTY(OBJECT_ID(@objectname), 'TableHasPrimaryKey') = 0
BEGIN
  SET @msg = N'Table ' + @objectname + ' does not contain a primary key.'
    + CHAR(10) + N'Table creation rolled back.'
  RAISERROR(@msg, 16, 1)
  ROLLBACK
  RETURN
END

CREATE TABLE ステートメントを実行するとトリガが起動されます。 XQuery を使用してスキーマ名とオブジェクト名を抽出し、OBJECTPROPERTY 関数を使用してテーブルに主キーがあるかどうかをチェックします。主キーがない場合、エラーを発生させてトランザクションをロールバックします。トリガを作成した後は、次のように主キーを使用しないテーブルを作成するとエラーになります。

CREATE TABLE T1(col1 INT NOT NULL)
メッセージ 50000、レベル 16、状態 1、プロシージャ trg_create_table_with_pk、行 19
Table [dbo].[T1] does not contain a primary key.
Table creation rolled back.
メッセージ 3609、レベル 16、状態 2、行 1
トランザクションはトリガで終了しました。バッチは中止されました。

次のステートメントは成功します。

CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)

トリガとテーブル T1 を削除するため、次のコードを実行します。

DROP TRIGGER trg_create_table_with_pk ON DATABASE
DROP TABLE T1

監査トリガの例として、次のデータベースレベルのトリガを使用すると、AuditDDLEvents テーブルへのすべての DDL ステートメントが監査されます。

CREATE TABLE AuditDDLEvents
(
  LSN              INT      NOT NULL IDENTITY,
  posttime         DATETIME NOT NULL,
  eventtype        SYSNAME  NOT NULL,

  loginname        SYSNAME  NOT NULL,
  schemaname       SYSNAME  NOT NULL,
  objectname       SYSNAME  NOT NULL,
  targetobjectname SYSNAME  NOT NULL,
  eventdata        XML      NOT NULL,
  CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @eventdata AS XML
SET @eventdata = eventdata()
INSERT INTO dbo.AuditDDLEvents(
  posttime, eventtype, loginname, schemaname, 
  objectname, targetobjectname, eventdata)
  VALUES(
    CAST(@eventdata.query('data(//PostTime)')         AS VARCHAR(23)),
    CAST(@eventdata.query('data(//EventType)')        AS SYSNAME),
    CAST(@eventdata.query('data(//LoginName)')        AS SYSNAME),
    CAST(@eventdata.query('data(//SchemaName)')       AS SYSNAME),
    CAST(@eventdata.query('data(//ObjectName)')       AS SYSNAME),
    CAST(@eventdata.query('data(//TargetObjectName)') AS SYSNAME),
    @eventdata)
GO

このトリガは単に、eventdata() 関数のイベント属性のうち注目するものを XQuery を使用してすべて抽出し、AuditDDLEvents テーブルに挿入します。トリガをテストするため、DDL ステートメントを実行して監査テーブルにクエリします。

CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
ALTER TABLE T1 ADD col2 INT NULL
ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL
CREATE NONCLUSTERED INDEX idx1 ON T1(col2)
SELECT * FROM AuditDDLEvents

テーブル T1 のスキーマを過去 24 時間以内に変更した担当者を調べ、どのように変更したかを確認するため、次のクエリを実行します。

SELECT posttime, eventtype, loginname,
  CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
  AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
ORDER BY posttime

トリガと、先ほど作成したテーブルを削除するため、次のコードを実行します。

DROP TRIGGER trg_audit_ddl_events ON DATABASE
DROP TABLE dbo.T1
DROP TABLE dbo.AuditDDLEvents

サーバーレベルの監査トリガの例として、次のトリガを使用すると、監査テーブル AuditDDLLogins への DDL ログインに関連するすべてのイベントが監査されます。

USE master
CREATE TABLE dbo.AuditDDLLogins
(
  LSN              INT      NOT NULL IDENTITY,
  posttime         DATETIME NOT NULL,
  eventtype        SYSNAME  NOT NULL,
  loginname        SYSNAME  NOT NULL,
  objectname       SYSNAME  NOT NULL,
  logintype        SYSNAME  NOT NULL,
  eventdata        XML      NOT NULL,
  CONSTRAINT PK_AuditDDLLogins PRIMARY KEY(LSN)
)
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
  FOR DDL_LOGIN_EVENTS
AS
DECLARE @eventdata AS XML
SET @eventdata = eventdata()
INSERT INTO master.dbo.AuditDDLLogins(
  posttime, eventtype, loginname,
  objectname, logintype, eventdata)

  VALUES(
    CAST(@eventdata.query('data(//PostTime)')         AS VARCHAR(23)),
    CAST(@eventdata.query('data(//EventType)')        AS SYSNAME),
    CAST(@eventdata.query('data(//LoginName)')        AS SYSNAME),
    CAST(@eventdata.query('data(//ObjectName)')       AS SYSNAME),
    CAST(@eventdata.query('data(//LoginType)')        AS SYSNAME),
    @eventdata)
GO

トリガをテストするため、次の DDL ログインステートメントを実行し、ログインの作成、変更、および削除を行って、監査テーブルにクエリします。

CREATE LOGIN login1 WITH PASSWORD = '123'
ALTER LOGIN login1 WITH PASSWORD = 'xyz'
DROP LOGIN login1
SELECT * FROM AuditDDLLogins

トリガと監査テーブルを削除するため、次のコードを実行します。

DROP TRIGGER audit_ddl_logins ON ALL SERVER
DROP TABLE dbo.AuditDDLLogins
DROP DATABASE testdb

DDL とシステムイベントの通知

SQL Server 2005 Beta 2 では、DDL およびシステムイベントをキャプチャしたり、イベント通知を Service Broker 配置に送信したりすることができます。トリガは同期して処理されますが、イベント通知は非同期に使用できるイベント配信メカニズムです。イベント通知により、指定した Service Broker サービスに XML データを送信します。イベントコンシューマはこの XML データを非同期に利用できます。イベントコンシューマは、WAITFOR 句の拡張部分を使用して、新しいデータが到着するのを待機できます。

イベント通知は、以下の項目によって定義されます。

スコープ (SERVER、DATABASE、ASSEMBLY、個別のオブジェクト)
イベントまたはイベントグループのリスト (例、CREATE_TABLE、DDL_EVENTS など)
SQL Server Events メッセージ型とコントラクトを実装する展開の名前

イベントデータは、SQL Server Events スキーマを使用して、XML 形式で送信されます。イベント通知を作成するための一般的な構文を以下に示します。

CREATE EVENT NOTIFICATION <name>
  ON <scope>
  FOR <list_of_event_or_event_groups>
  TO SERVICE <deployment_name>

イベント通知を作成すると、システム展開とユーザーが指定した展開の間の Service Broker メッセージ交換が確立されます。 **には、メッセージ交換を確立してイベントに関するデータを配信する相手となる Service Broker を指定します。指定する配置は、SQL Server Events メッセージ型とコントラクトを実装する必要があります。イベント通知が設定されているイベントが発生すると、関連するイベントデータから XML メッセージが構築され、イベント通知のメッセージ交換により、指定した展開にメッセージが送信されます。

たとえば次のコードは、T1 というテーブルを作成し、T1 テーブルのスキーマを変更するたびに特定の展開に通知を送信するイベント通知を定義します。

CREATE TABLE dbo.T1(col1 INT);
GO
-- キューを作成します。
CREATE QUEUE SchemaChangeQueue;
GO
--イベント通知コントラクトを参照するキューに
--サービスを作成します。
CREATE SERVICE SchemaChangeService
  ON QUEUE SchemaChangeQueue
(
[//s.ms.net/SQL/Notifications/PostEventNotification/v1.0]
);
GO
--Service Broker によりメッセージを送信する先のアドレスを定義するため
--サービスにルートを作成します。
CREATE ROUTE SchemaChangeRoute
  WITH SERVICE_NAME = 'SchemaChangeService',
  ADDRESS = 'LOCAL';
GO
--イベント通知を作成します。
CREATE EVENT NOTIFICATION NotifySchemaChangeT1
  ON TABLE dbo.T1
  FOR ALTER_TABLE TO SERVICE [SchemaChangeService];

次の ALTER を実行すると、SchemaChangeQueue に構築した SchemaChangeService に XML メッセージが送信されます。

ALTER TABLE dbo.T1 ADD col2 INT;

その後、以下のステートメントにより、キューから XML メッセージを取得できます。

RECEIVE TOP (1) CAST(message_body AS nvarchar(MAX)) 
FROM SchemaChangeQueue

結果の出力は以下のようになります (実際には、改行されません)。

<EVENT_INSTANCE>
<PostTime>2004-06-15T11:16:32.963</PostTime>
<SPID>55</SPID>
<EventType>ALTER_TABLE</EventType>
<ServerName>MATRIX\S1</ServerName>
<LoginName>MATRIX\Gandalf</LoginName>
<UserName>MATRIX\Gandalf</UserName>
<DatabaseName>testdb</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>T1</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" 
ENCRYPTED="FALSE" />
<CommandText>ALTER TABLE dbo.T1 ADD col2 INT;</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

WAITFOR ステートメントを使用して、以下のようにブロッキングモードで通知を受け取ることができます。

WAITFOR (RECEIVE * FROM myQueue)

まとめ

SQL Server 2005 Beta 2 での Transact-SQL の機能強化により、クエリ記述の表現力が増し、コードのパフォーマンスが向上し、エラー管理の機能が拡張されます。 Transact-SQL の機能強化に継続的な作業が行われているのは、Transact-SQL の SQL Server で果たしている重要な役割、能力、将来性を確信しているためです。

次の方法で共有

SQL Server 2005 Beta 2 Transact-SQL の機能強化

目次

紹介と対象範囲

クエリの表現力と DRI サポートの向上

順位付け関数

ROW_NUMBER

パーティション分割

RANK、DENSE_RANK

NTILE

再帰クエリと共通テーブル式

共通テーブル式

再帰クエリ

1 つの親を持つ環境 : 従業員組織図

複数の親を持つ環境 : 部品表

PIVOT と UNPIVOT

PIVOT

UNPIVOT

APPLY

CROSS APPLY

OUTER APPLY

相関サブクエリのテーブル値関数

新しい DRI 操作のサポート :

パフォーマンスとエラー処理の機能強化

BULK 行セットプロバイダ

例外処理

Transact-SQL に影響を与える SQL Server 2005 Beta 2 の他の機能

TOP の機能強化

結果を伴う DML

動的な列に使用する MAX 指定子

XML と XQuery

DDL トリガ

DDL とシステムイベントの通知

まとめ

その他のリソース

次の方法で共有

SQL Server 2005 Beta 2 Transact-SQL の機能強化

目次

紹介と対象範囲

クエリの表現力と DRI サポートの向上

順位付け関数

ROW_NUMBER

パーティション分割

RANK、DENSE_RANK

NTILE

再帰クエリと共通テーブル式

共通テーブル式

再帰クエリ

1 つの親を持つ環境 : 従業員組織図

複数の親を持つ環境 : 部品表

PIVOT と UNPIVOT

PIVOT

UNPIVOT

APPLY

CROSS APPLY

OUTER APPLY

相関サブクエリのテーブル値関数

新しい DRI 操作のサポート :

パフォーマンスとエラー処理の機能強化

BULK 行セット プロバイダ

例外処理

Transact-SQL に影響を与える SQL Server 2005 Beta 2 の他の機能

TOP の機能強化

結果を伴う DML

動的な列に使用する MAX 指定子

XML と XQuery

DDL トリガ

DDL とシステム イベントの通知

まとめ

その他のリソース

BULK 行セットプロバイダ

DDL とシステムイベントの通知