Transact-SQLを使用したデータベース開発に役立つ13のヒント

T-SQLによる業務用アプリケーション開発のケーススタディ

ヒント6：サブクエリと派生テーブル

　要件：ある建築会社のデータベースには、建築ジョブのテーブル、ジョブあたりの作業時間のテーブル、ジョブで購入した資材のテーブルがそれぞれ1つずつあります。これらから、各ジョブ、そのジョブの総作業時間、そのジョブで購入した資材の合計コストを取りまとめた簡単なリストを生成する必要があります。なお、ジョブによっては、作業時間はあるが資材コストはない場合や、資材コストはあるが作業時間はない場合があります。

　これは一見、かなり簡単な要件のように思われるかもしれません。しかし実際には、なかなか難しい問題であり、クエリの手法についても意見が分かれる可能性があります。

　開発者によっては、この問題に対処するときに、次のようにOUTER JOINステートメントのペアを使用するかもしれません。

SELECT J.JobID, SUM(H.Hours) AS TotHours,
   SUM(C.Costs) AS TotCosts
FROM JobMast J
   LEFT JOIN JobHours H ON H.JobID = J.JobID
   LEFT JOIN JobCosts C ON C.JobID = J.JobID
GROUP BY J.JobID

　あいにく、このクエリでは、正しい結果は生成されません。このデータベースでは、単一のジョブに対応する作業時間の行が2つあったり、資材コストの行が3つあったりする可能性があります。そのため、上記の集計方法では、作業時間やコストが二重三重にカウントされて、非常に高い値になってしまう可能性があるのです。

　幸い、SQL-92の標準では、スカラー値を返すサブクエリをSELECTステートメントの中に実装する方法で、この問題に対処できます。次のクエリなら、信頼の置ける結果が生成されます。

SELECT J.JobID,
   (SELECT SUM(H.Hours) FROM JobHours H
      WHERE H.JobID = H.JobID) AS TotHours,
   (SELECT SUM(C.Costs) FROM JobCosts C
      WHERE C.JobID = H.JobID) AS TotCosts
FROM JobMast J
GROUP BY J.JobID

　上記のコードでは、相関サブクエリを使用しています。相関サブクエリは、外部クエリの結果に依存します。したがって、単独で実行することはできません。

　この方法では、外部クエリの各レコードに対してサブクエリが実行されます。人によっては、これでは効率が悪いと考え、代わりに派生テーブルを使うことがあります。

SELECT J.JobID, H.TotHours, C.TotCost
   FROM JobMast J
   LEFT JOIN (SELECT JobID, SUM(Hours) AS TotHours
              FROM JobHours
              GROUP BY JobID) H
   ON H.JobID = J.JobID
   LEFT JOIN (SELECT JobID, SUM(Costs) AS TotCosts
              FROM JobCosts
              GROUP BY JobID) C
   ON C.JobID = J.JobID

　上記のクエリでは、かっこで囲まれた2つのサブクエリで派生テーブルが生成されます。派生テーブルとは要するに、そのクエリの間だけ存在する、一時的な臨時のビューです。派生テーブルの内容は、他のテーブルと同様に、外部クエリで参照できます（なお、派生テーブルを参照するときには、必ず別名を使用する必要があります）。また、派生テーブルは、FROM句の中のサブクエリでも作成できます。派生テーブルは主に、当該のクエリのみで一時的な結果セットが必要な場合に使用します。

　「エコノミストを10人集めれば、経済について10通りの見解が得られる」という古い戯言があります。これはいくぶん誇張した言い方ですが、相関サブクエリと派生テーブルについても、どちらの方法が好ましいかを10人の開発者に聞いてみたら、意見はきっと分かれるはずです。つまり、読みやすいという理由で相関サブクエリを選ぶ人もいれば、パフォーマンス面で優位かもしれないという理由で派生テーブルを選ぶ人もおり、さらにはもっと別の方法を選ぶ人も必ずいるはずです。

　技能や経験が同程度の開発者であっても、パフォーマンス（の有望性）に重きを置くか、それともメンテナンスや読みやすさに重きを置くかで、意見はきっと分かれます。時には、正解は1つでないこともあります。鍵となるのは、この種の状況に対処するときに一貫性を保つということです。

　話を先へ進める前に、要件をもう1つ追加してみましょう。私が作成した結果セットには、各ジョブの作業時間の集計が含まれていました。ここで、時間を集計するのではなく、作業時間に従業員の時給を掛けたものを合計して、労働賃金の総額を求めるには、どうすればいいでしょうか。

　さらに複雑なことに、1つのジョブの中で時給が変わることもあるものとします（たとえば、あるジョブの半分の作業までの時給より、残りの半分の時給の方が高い場合があります）。リスト7は、上記のクエリを拡張して、作業日に対応する作業時間とその日の有効な時給とをマッチさせる方法を示したものです。追加したロジックでは、当該の作業日以前の有効な日付の最大値（MAX）における時給を検索しています。追加したこのサブクエリは、前述のクエリのどちらでも使用できます。

リスト7：作業日と適切な時給を対応付けるためのサブクエリ

-- JobMast  (JobID)
-- JobHours (JobID, EmpKey, Hours, DateWorked)
-- JobCosts (JobID, Costs)
-- EmpRate  (EmpKey, EffDate, WorkRate)
-- Use subquery to create derived tables, to sum/group hours 
-- and costs
-- Inside the subquery for hours, perform another subquery
-- to match up an employee's work date with the rate for that day
-- Uses EmpRate twice (ER1 and ER2, once to apply the correct rate
-- and a second time in a subquery to get the rate based on date
SELECT J.JobID, H.TotLabor, H.TotHours, C.TotCost 
   FROM JobMast J
   LEFT JOIN 
       (SELECT JobID, SUM(JH.Hours*ER1.WorkRate) AS TotLabor,
                SUM(JH.Hours) AS TotHours
              FROM JobHours JH
         JOIN EmpRate ER1 ON ER1.EmpKey = JH.EmpKey
         WHERE ER1.EffDate = (SELECT MAX(EffDate) FROM
            EmpRate ER2 WHERE EffDate <= DateWorked
            AND ER2.EmpKey = JH.EmpKey)
         AND DateWorked >= EffDate
              GROUP BY JobID) H 
   ON H.JobID = J.JobID
   LEFT JOIN (SELECT JobID, SUM(Costs) AS TotCosts
              FROM JobCosts
              GROUP BY JobID) C 
   ON C.JobID = J.JobID

ヒント7：テーブル変数と一時テーブル

　ヒント1および3のリストでは、一時的な結果セットを保持してストアドプロシージャ内で繰り返し利用するために、テーブル変数を導入しました。これらの例では、テーブル値を返すユーザー定義関数（アカウントキーのリストに対して処理を行うもの）の結果を、注文ヘッダーテーブルに対して直接結合しました。アカウントキーが格納されているテーブルを別の結合で使用したい場合なら、次のような方法が可能です。

DECLARE @tAcctKeys TABLE (IntKey int)
INSERT INTO @tAcctKeys
   SELECT IntKey = IntKey FROM
      CsvToTable(@cAcctKeyList)
-- now you can use @tAcctKeys in any subsequent
-- joins in the stored procedure

　Microsoftのドキュメントによると、テーブル変数を使用すると、一時テーブルと同様のメリットがある程度得られるのに加え、パフォーマンスも若干向上します。テーブル変数の一般的なスコープは、他の変数と同じです。つまり、プロシージャまたは関数の最後で自動的にクリアされます。SQL Serverでは、ストアドプロシージャの再コンパイルの回数は、テーブル変数を使用している場合の方が、一時テーブルを使用している場合より少なくなります。加えて、テーブル変数が絡んでいるトランザクションの存続期間は、テーブル変数の更新の間だけです。したがって、テーブル変数の方が、リソースのロックやログ記録の必要性が少なくなります。テーブル変数のスコープは限定されており、永続的なデータベースの一部ではないので、トランザクションのロールバックによる影響を受けません。

　確かに、テーブル変数のパフォーマンスは多少優れているかもしれませんが、その内容がシステムデータベースではなくメモリに格納されるおかげで一時テーブルよりパフォーマンスが優れているというのは、若干の誤解があります。Microsoftによると、テーブル変数はメモリのみの構造ではありません。テーブル変数には、メモリに収まりきらないデータを保持することも不可能ではありません。したがって、データを格納するための場所がディスク上に必要です。テーブル変数は、一時テーブルと同様に「tempdb」データベースに作成されます。メモリでまかなえる場合は、テーブル変数と一時テーブルのいずれも、メモリ（データキャッシュ）内で作成および処理されます。

　この事実をふまえても、前述のような理由から、開発者は一時テーブルよりテーブル変数の方を好むのが一般的です。しかし、テーブル変数には、見過ごせない制約がいくつかあります。テーブル変数のTRUNCATEは不可能ですし、テーブル変数の作成後にその構造を変更することもできません。さらには、テーブル変数に対しては、次のいずれのステートメントも使用できません。

-- neither line will work with table variables
INSERT @tTable EXEC <sp_mystoredproc>
SELECT * INTO @tTable FROM <mytable>

ヒント8：日付関数

　要件：日別の注文情報を取得して、週ごとに取りまとめ、週報や週単位のグラフを作成する必要があります。

　次に示す2つの短いコードは、SQL ServerのDATEPART関数を使用してこの要件を満たす方法を示したものです。1つ目のコードは、週の中の任意の日付を、その週の土曜日の日付に変換するユーザー定義関数です。2つ目のコードでは、注文情報に対するクエリの中でそのユーザー定義関数を使用し、週末の土曜日の日付ごとにデータを取りまとめています。この結果セットを、週報やグラフに使用できます。この例も、ビジネス層でデータをほとんど（あるいはまったく）いじらずに結果セットを作成できるようにTransact SQLを使用した例と言えます。

　UPDATEトリガを作成するときには、UPDATEステートメントで更新された行の数にかかわらず、トリガは1回のみ起動されるということを頭に入れておく必要があります。

CREATE FUNCTION dbo.GetEndOfWeek
(@dDate DateTime)
-- Converts date to the Saturday date for the week
RETURNS DateTime AS 
BEGIN
   DECLARE @dRetDate DateTime
   SET @dRetDate =
      @dDate + ( 7-DATEPART(weekday,@dDate))
   RETURN @dRetDate
END
SELECT dbo.GetEndOfWeek(OrderDate) AS WeekEnding,
   SUM(Amount) AS WeekAmount
   FROM OrderHdr
   GROUP BY dbo.GetEndOfWeek(OrderDate)

　DATEPARTは、分析対象の日付要素に基づく整数を返します。SQL Server Books Onlineには、DATEPART関数で分析できる日付要素（datepart）の一覧が掲載されています。たとえば、日付要素にquarterを指定すると、データを四半期ごとに取りまとめることができます。

　weekdayという日付要素では、SQL Serverでの週の開始日の設定に応じて結果が左右されます。既定の設定では、週の開始日は日曜日です。週の開始日を別の曜日に設定するには、SET DATEFIRSTコマンドを使用します。たとえば、売上の集計を、日曜～土曜という区切りではなく月曜～日曜という区切りで行う場合には、次のように設定します。

-- Change first day of week from default of
-- Sunday (7) to Monday (1)
SET DATEFIRST 1

　日付について1つ申し沿えておきます。日付を比較するクエリを作成するときに、日付列の値には午前0時以外の時刻が設定されているのに対し、比較対象の日付変数の日付には時刻が定義されていない、というケースがよくあります。

　たとえば、Date <= CAST('05-26-2003') AS DATETIMEという条件を満たす行を取得するクエリの場合、「05-26-2003 22:10:00」という日付の行は取得されません。なぜなら、この場合には、「2003年5月26日の22:10:00」と「2003年5月26日の00:00:00（午前0時）」とが比較され、「前者<=後者」は成り立たないと判断されるからです。5月26日までの全データを、その日の午後11時59分ぎりぎりのものまで含めて取得したい場合には、比較日に1を加えたうえで、それ未満の日付のものを取得する（Date < CAST('05-26-2003') + 1）というロジックに変える手があります。

ヒント9：CASTとCONVERTによるデータ変換

　要件：経理処理の結果を伝えるメッセージを、通常の英文に近い形で生成する必要があります。たとえば、"100 Employee Checks were generated on 11/15/2004 at 5:07:02 PM, for a total dollar value of $134,123.11"といったメッセージです。

　SQL Serverに備わっているCAST関数とCONVERT関数を使用すると、異なるデータ型を組み合わせて利用できます。リスト8は、異なるデータ型（DATETIMEとdecimal）の変数を使用して、ログや表示に利用できるメッセージ文字列を組み立てる方法の例です。

リスト8：CAST関数とCONVERT関数を使用して異なるデータ型を併用する

DECLARE @nRowCount integer
   DECLARE @dReportDate DATETIME
   DECLARE @nDollars decimal(13,2)
   SET @nRowCount = 156
   SET @dReportDate = GETDATE()
   SET @nDollars = 156134.11
   DECLARE @cDateString char(20)
   DECLARE @cRowCount char(20)
   DECLARE @cMessage varchar(8000)
   SET @cRowCount = LTRIM(RTRIM(CONVERT(int,@nRowCount,20)))
   SET @cDateString = LTRIM(RTRIM(CONVERT(datetime,@dReportDate,20)))
   SET @cMessage = 'Generated ' + @cRowCount +
                ' Employee Checks on '  + @cDateString +
          ' for a total amount of $' + 
                CAST(@nDollars AS Varchar(20))
   SELECT @cMessage

ヒント10：更新トリガ

　要件：テーブルに対してUPDATEが実行されたときに、タイムスタンプ列を自動的に更新する必要があります。

　UPDATEトリガは、テーブルに対してUPDATEステートメントが実行されるたびに毎回起動される、特別な種類のストアドプロシージャです。トリガを使用すると、特定の規則を適用したり、特定の列を更新したりできます。

　UPDATEトリガでは、更新前（DELETED）と更新後（INSERTED）の行の状態を保持する2つの重要なシステムテーブルにアクセスします。これらのテーブルの内容を使用すると、基本的な監査証跡機能を実装できます（ヒント11で取り上げます）。とりあえずは、LASTUPDATED列の自動更新の実現に話を絞ることにします。

　UPDATEトリガを作成するときには、UPDATEステートメントで更新された行の数にかかわらず、トリガは1回のみ起動されるということを頭に入れておく必要があります。SQL Serverの経験が浅い人がトリガのコードを作成すると、1つの行のみが更新されたという想定でコードを作成してしまうというミスを犯すことがあります。

CREATE TRIGGER Upd_Client ON dbo.Client
FOR UPDATE
AS
   UPDATE Client
      SET LastUpdate = GETDATE()
   FROM Client C
      JOIN Inserted I
       ON I.PrimaryKey = C.PrimaryKey

ヒント11：UPDATEトリガを使用して監査証跡の更新を実装する

　要件：特定の列に対する変更を、監査証跡ログに記録する必要があります。ログに含める必要があるのは、変更されたテーブル、変更された行の主キー、変更された列の名前、変更前および変更後の値（古い値/新しい値）、更新の日付/時刻です。

　特定のデータベース項目に対する変更を追跡したいと考えている顧客は多くいます。UPDATEトリガでは、前のヒントで使用した「INSERTED」テーブルと「DELETED」テーブルを使用して監査証跡を実装できます。

　リスト9では、製品マスターテーブルに対してUPDATEトリガを使用して、項目に対する変更を追跡しています。ここでは、「INSERTED」テーブルと「DELETED」テーブルをクエリして、特定の列に変更があるかどうか調べています。この例では、PRICE列とDESCRIPTION列の変更をチェックした後で、「INSERTED」および「DELETED」からアプリケーションログテーブルに対して値を書き込んでいます。

リスト9：基本的な監査証跡機能を実装する

CREATE TRIGGER Upd_Price ON dbo.Price
   FOR  UPDATE  
   AS
      DECLARE @dLastUpdate DATETIME
      SET @dLastUpdate = GETDATE()
         -- Set the last Update for rows updated
      UPDATE Price 
         SET LastUpdate = @dLastUpdate
      FROM Price P
         JOIN Inserted I
          ON I.PrimaryKey = P.PrimaryKey 
         -- Write out to the Audit Log, for rows where Price changed 
      INSERT INTO AuditLog (TableName, PrimaryKey, LastUpdate, 
                             ColName, OldValue, NewValue, UserKey)
             SELECT 'PRICE' AS TableName, I.PrimaryKey, 
                    @dLastUpdate AS LastUpdate, 'Price' AS ColName,
                    CONVERT(CHAR(20),D.Price) AS OldValue, 
                    CONVERT(CHAR(20),I.Price) AS NewValue, 
                    I.UserKey
             FROM Inserted I
             JOIN Deleted D ON D.PrimaryKey = I.PrimaryKey
             WHERE I.Price <> D.Price
      -- Now check for any changes to the description
      INSERT INTO AuditLog (TableName, PrimaryKey, LastUpdate, 
                             ColName, OldValue, NewValue, UserKey)
             SELECT 'PRICE' AS TableName, I.PrimaryKey, 
                    @dLastUpdate AS LastUpdate, 'Descr' AS ColName,
                    D.Descr AS OldValue, 
                    I.Descr AS NewValue, 
                    I.UserKey
             FROM Inserted I
             JOIN Deleted D ON D.PrimaryKey = I.PrimaryKey
             WHERE I.Descr <> D.Descr

　メモ：このヒントでは、監査証跡ログの基本機能を示しています。完全に自動化された監査証跡ソリューションを構築するには、ログへの記録が必要なテーブル/列に対してUPDATEトリガを生成するようなデータドリブンスクリプトの作成を検討するとよいでしょう。

　自動化された監査証跡ソリューションの構築に役立つ製品は、いくつかの会社から出ています。私の個人的なおすすめは、Red Matrix TechnologiesのSQLAuditという製品です。上記のような機能やその他の機能が備わっています。包括的な監査証跡ソリューションの開発に要する苦労を考えると、SQLAuditなどのサードパーティ製ツールを購入するのは、経済面から見て賢い選択かもしれません。

ヒント12：動的SQL

　データドリブンアプリケーションにおいて、重要なクエリ構文を実行時まで確定できない場合に、動的SQLステートメントを使用して値を渡せることがあります。ただしこれは賛否両論がある方法です。リスト10は、動的SQLの使用例を2つ示したものです。1つ目の例は、テーブル名が変数となっている簡単なクエリです。2つ目の例は、1つの行を返すクエリの結果を出力変数に設定しています。

リスト10：動的SQLの基本的な使用例

-- Must use Unicode data
DECLARE @cSQLSyntax nvarchar(2000)
DECLARE @cTableName varchar(20)
SET @cTableName = 'EMPLOYEE'
SET @cSQLSyntax = N'SELECT * FROM ' + @cTableName  + ' WHERE EmployeeID = 1' 
EXEC sp_executesql @cSQLSyntax
-- Return a column into an output variable
-- Query must only contain one row, else an error will occur
-- (query could also be a SUM that returns a scalar value)
DECLARE @cValue nVarChar(30)
DECLARE @cColumnName varchar(20)
SET @cColumnName = 'firstname'
SET @cSQLSyntax = N' SELECT @cValue = ' +  @cColumnName + ' FROM Employee where employeeid = 1'
EXECUTE SP_EXECUTESQL @cSQLSyntax, N'@cValue nVarChar(30) OUTPUT', @cValue OUTPUT
SELECT @cValue

　メモ：リスト10の動的SQLではUnicodeデータ（nCharおよびnVarChar）を使用する必要があります。

　Googleで「dynamic SQL」（動的SQL）と検索してみると、動的SQLについての優れた説明がいくつも見つかります。ネット上には、動的SQLのさまざまな手法を説明した文章がたくさんあり、動的SQLとの関連でSQLインジェクションについて説明したものまであります。

ヒント13：データベースのテーブルと列の一覧を取得する

　初心者がよく抱く疑問の1つに、データベースのテーブルと列の一覧を取得するにはどうすればいいかというものがあります。次のように、情報スキーマをクエリすれば、一覧を簡単に取得できます。

-- Return a list of table names
SELECT DISTINCT Table_Name FROM
   Northwind.INFORMATION_SCHEMA.COLUMNS
   ORDER BY Table_Name
-- Return a list of columns
-- (perform a SELECT * to see a full list)
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,
      CHARACTER_MAXIMUM_LENGTH FROM
   Northwind.INFORMATION_SCHEMA.COLUMNS
   ORDER BY Table_Name
-- You could combine the capability of LIKE
-- to find columns with a particular search
-- pattern

まとめ

　私のWebサイトでは、SQL Server 2000とTransact-SQLについての良書をいくつか紹介しています。また、SQL Server、Transact-SQL、SQL-92などについてのさまざまなオンラインリソースも紹介しています。

　また、これは現在も進行中のプロジェクトですので、折に触れて加筆を行う場合があります。