ヒント9：SQL ServerのCOALESCE関数の使用
いくつかの検索条件（姓、名、住所、都市名、郵便番号など）をユーザーが入力すると、それに合致する顧客を検索できるというフォーム（またはWebページ）があるとしましょう。ユーザーが入力するのは、1つのフィールドでも複数のフィールドでもよいものとします。この場合、取り得るパラメータすべてをチェックしたうえで、ユーザーが入力したパラメータのみを使用してクエリするようなストアドプロシージャを作成する必要があります。
この場合、各パラメータをチェックし、ユーザーが入力したパラメータに基づいてSQLのSELECT文字列を組み立て、動的SQLを使用してその文字列を実行する、という形でストアドプロシージャを作成する方法もあります。多くのSQL開発者が選択するのはそちらの方法です。
ここでは別の方法を紹介します。次のコードのように、SQL ServerのCOALESCE関数を使用する方法です。
-- This will work in both SQL2000 and SQL2005 -- You can use COALESCE to query on only those search values -- that are not NULL DECLARE @city varchar(50), @state varchar(50), @zip varchar(50), @FirstName varchar(50), @LastName varchar(50), @Address varchar(50) SET @FirstName = 'Kevin' SET @State = 'NY' SELECT * FROM CUSTOMERS WHERE FirstName = COALESCE(@FirstName,FirstName) AND LastName = COALESCE(@LastName,LastName) AND Address = COALESCE(@Address,Address) AND City = COALESCE(@City,City) AND State = COALESCE(@State,State) AND Zip = COALESCE(@Zip,Zip)
COALESCEは、SQL Server 2000とSQL Server 2005の両方で使用でき、その方がT-SQLコードがすっきりすると思います。それぞれの検索条件について、COALESCEには2つの値を渡しています。1つは検索変数、もう1つは検索変数がNULLの場合に使用する値です。したがって、ユーザーが指定しなかった検索条件については、その列自身が既定値として使用されます。この方法は、クエリの対象行が数百万に及ぶ場合でも、きわめて高速に動作します。
ヒント10：SQL 2005でのグループ内のランク付け
SQL Server 2005には、ROW_NUMBERという関数が追加されており、結果セットにランクを付けることができます。また、グループ分けした結果ごとにランクを付けることも可能です。たとえば、500ドルを超える上位の注文を対象として、顧客別に注文の多い順にランキングを付けたいとします。
リスト6は、NorthwindのOrdersデータベースに対して、500ドルを超える注文をクエリする方法を示します。ランキングの番号は、顧客別の注文ごとに割り当てます。ROW_NUMBER OVERステートメントでは、PARTITION（ここでは、グループレベルの定義のようなものを表す）と、結果セットにランクを付けるときに使用するOrderを指定できます。
ROW_NUMBER() OVER (PARTITION BY CUSTOMERID ORDER BY (UnitPrice * Quantity) DESC) AS OrderRank
SELECT CustomerID, OH.OrderID, OrderDate, (UnitPrice * Quantity) as Orderamount, ROW_NUMBER() OVER (PARTITION BY CUSTOMERID ORDER BY (UnitPrice * Quantity) desc ) AS OrderRank FROM Orders OH JOIN [dbo].[Order Details] OD ON OH.OrderID = OD.OrderID WHERE (UnitPrice * Quantity) > 500 ORDER BY CUSTOMERID, OrderAmount DESC -- Results, OrderRank partitioned (reset) on each Customer ALFKI 10692 1997-10-03 00:00:00.000 878.00 1 ALFKI 10835 1998-01-15 00:00:00.000 825.00 2 ANTON 10535 1997-05-13 00:00:00.000 1050.00 1 ANTON 10677 1997-09-22 00:00:00.000 936.90 2 ANTON 10535 1997-05-13 00:00:00.000 825.00 3 ANTON 10573 1997-06-19 00:00:00.000 820.00 4 ANTON 10573 1997-06-19 00:00:00.000 702.00 5 AROUT 10953 1998-03-16 00:00:00.000 4050.00 1 AROUT 10558 1997-06-04 00:00:00.000 1060.00 2 AROUT 10707 1997-10-16 00:00:00.000 780.00 3
ヒント11：T-SQL 2005のその他の話題（APPLY、ユーザー定義関数、相関サブクエリでのテーブル値ユーザー定義関数）
T-SQL 2000では、テーブル値ユーザー定義関数をクエリに組み込む機能について、やや欠けている面がありました。開発者は、ユーザー定義関数の実行結果を使用するときに、まず一時テーブルに取り込んだうえで、その一時テーブルを使用するという方法を使わざるを得ないことがよくありました。
T-SQL 2005では、テーブル値ユーザー定義関数とクエリの連係がしやすくなっています。この記事の前編では、新しいAPPLY演算子を使用して、テーブル値ユーザー定義関数の結果を派生テーブルと同じような形で直接適用するという例を紹介しました。その例では、指定した顧客のTOP Nの注文をNorthwindデータベースから取得するユーザー定義関数を使用し、クエリ内ですべての顧客に対してその関数を直接適用するという処理を行いました。
今回は、テーブル値ユーザー定義関数をもっと直接的に使う別の方法を紹介します。T-SQL 2005では、テーブル値ユーザー定義関数をサブクエリで使用でき、外部クエリの列をユーザー定義関数の引数として渡せるのです。
Northwindデータベースで、5,000ドルを超える注文が2件以上ある顧客や、1,000ドルを超える注文が5件以上ある顧客などを知りたいとします。最初の手順は、GetCustOrders_GT_Xというテーブル値ユーザー定義関数を作成することです（リスト7の前半部分を参照）。このユーザー定義関数は、2つのパラメータ（顧客IDと基準額）を受け取り、その顧客の注文で基準額を超えるものをテーブル変数で返します。
次の手順では、データベースの各顧客に対してそのユーザー定義関数を実行し、注文が2件以上ある顧客を判断します。できれば、サブクエリを作成して、各顧客をパラメータとしてユーザー定義関数に渡せれば理想的です。ここで、T-SQL 2005の機能を活用できます。
SQL Server 2000の場合、相関サブクエリ内のテーブル値関数では、外部クエリの列を参照できませんでした。うれしいことに、SQL Server 2005ではその制約がなくなりました。ユーザー定義関数を使用するサブクエリを作成して、外部クエリの列をユーザー定義関数に引数として渡すことができます（リスト7の後半部分を参照）。
CREATE FUNCTION [dbo].[GetCustOrders_GT_X] (@CustomerID AS varchar(10), @nThreshold AS decimal(14,2)) RETURNS @tOrders TABLE (OrderID int, CustomerID varchar(10), OrderDate datetime, OrderAmount decimal(14,2)) AS BEGIN INSERT INTO @tOrders SELECT OH.OrderID, CustomerID, OrderDate, (UnitPrice * Quantity) as Orderamount FROM Orders OH JOIN [dbo].[Order Details] OD ON OH.OrderID = OD.OrderID WHERE CustomerID = @CustomerID AND (UnitPrice * Quantity) > @nThreshold ORDER BY OrderAmount DESC RETURN END GO DECLARE @nNumOrders int, @nMinAmt decimal(14,2) SET @nNumOrders = 2 SET @nMinAmt = 5000.00 SELECT CustomerID FROM Customers WHERE (SELECT COUNT(*) FROM DBO.GetCustOrders_GT_X(CustomerID,@nMinAmt)) >=@nNumOrders -- ResultsHUNGO QUICK ERNSH SAVEA RATTC -- To get the actual orders > 5000 for these 5 customers, -- we can turn the query above into a derived table, and then -- use a CROSS APPLY SELECT MaxOrders.* FROM (SELECT CustomerID FROM Customers WHERE (SELECT COUNT(*) FROM dbo.GetCustOrders_GT_X(CustomerID,@nThresholdAmt)) >= @nNumOrders) as CustList CROSS APPLY GetCustOrders_GT_X(CustomerID,@nThresholdAmt) AS MaxOrders
ヒント12：SQL 2005のXML機能の拡張
SQL Server 2000には、XMLを処理するための多種多様な機能があります。SQL Server 2005では、新しいXMLデータ型によって、XML機能が拡張されています。
リスト8とリスト9は、XMLデータを扱ういくつかの機能の例です。リスト8では、複数の異なる方法で、標準の列にXMLデータを挿入しています。リスト9では、XML列の中で文字列を検索しています（この構文については、Microsoft SQL Serverニュースグループの常連さんたちにご協力いただきました。ありがとうございました）。
-- First Example of inserting XML into a table of columns DECLARE @cXMLDoc XML declare @hdoc int SET @cXMLDoc = ' <AddressType > <AddressRecord AccountID = "1" Street="31 Main Dr" City="Philly" State="PA" Zip="12345"/> <AddressRecord AccountID = "2" Street="1 Wilson Dr" City="Newark" State="NJ" Zip="22222"/> </AddressType>' EXEC sp_xml_preparedocument @hdoc OUTPUT, @cXMLDoc SELECT * FROM OPENXML (@hdoc, '/AddressType/AddressRecord',1) WITH (AccountID int,Street varchar(100), City varchar(100), State varchar(10), ZipCode varchar(13)) -- Second example of inserting XML into a table of columns -- uses the Address tag to specify nested columns DECLARE @cXMLDoc XML declare @hdoc int SET @doc = ' <customer> <Customernum>48456</Customernum> <Firstname>Kevin</Firstname> <Lastname>Goff</Lastname> <Address> <city>Allentown</city> <state>PA</state> </Address> </customer>' EXEC sp_xml_preparedocument @idoc OUTPUT, @doc DECLARE @tTemp TABLE (Customernum int, Firstname char(50), Lastname char(50), City char(50), State Char(10)) INSERT INTO @ttemp SELECT Customernum,firstname,lastname,city,state FROM OPENXML (@idoc, '/customer',2) WITH (Customernum int,Firstname varchar(50),Lastname varchar(50), city varchar(50) './Address/city', state varchar(50) './Address/state')
-- Performing a partial text search inside an XML column declare @tTest table (address xml) insert into @ttest values (' <Address > <AddrRecord AccountID = "1" Street="31 Main Dr" City="Newark" State="NJ" Zip="11111" /> </Address>' ) insert into @ttest values (' <Address > <AddrRecord AccountID = "2" Street="1 Wilson Rd" City="Philly" State="PA" Zip="22222"/> </Address>' ) SELECT * FROM @ttest WHERE Address.exist('/Address/AddrRecord [contains(@City,"hil")]') = 1
ヒント13：SQL 2005でのテーブル変数に関する制限の緩和
テーブル変数は、SQL Server 2000で導入され、多くのデータベース開発者から歓迎されました。しかし、テーブル変数にはいくつかの制限がありました。その1つが、ストアドプロシージャの結果をテーブル変数に直接挿入できないという点でした。したがって、次のコードはT-SQL 2000では使えません。
INSERT @tTable EXEC <sp_mystoredproc>
うれしいことに、T-SQL 2005ではその制約がなくなりました。ストアドプロシージャを実行して、テーブル変数に直接挿入できます（リスト10を参照）。
DECLARE @tUser TABLE (UserName varchar(100),Status char(10),UserPK int) INSERT INTO @tUser exec [dbo].[cgsValidateUserID] 'KGOFF','KGOFF' SELECT * FROM @tUser
データ処理についての雑感
これまで何度も述べたことですが、結局のところ、開発者の仕事の主軸となるのはデータ処理です。この記事で2回にわたって見てきたように、Visual Studio 2005とSQL Server 2005では、データ処理が扱いやすくなっています。その中には、ジェネリックやSQLのPIVOTコマンドのように、大幅な機能拡張もあれば、ADO.NETでの個別の行ごとのフィルタのように、細かいながら重要な変更もあります。
Visual Studio 2003のコードをVisual Studio 2005の環境でメンテナンスしている方なら、こうした新しいデータ処理機能をすぐには活用できないかもしれません。しかし、時間の許す限り、新機能について学び、その機能を使ったプロトタイプを作成して、いざというときのために備えておきましょう。
参考文献
ばっちり空調の効いた部屋に1週間ほど閉じこもって、こうした新機能についてひたすら掘り下げてみたいと思ったことはありませんか。あいにく、現実世界にはさまざまな要件や要求があるため、その実現は通常は困難です。しかし、数時間の余裕があるのなら、次に挙げるような優れた参考文献で学ぶことが可能です。
1つ目は、『CoDe Magazine 』誌の記事です。同誌には、SQL Server 2005でのT-SQLとXMLについて、すばらしい記事が掲載されています。2005年1月/2月号には、Jim Duffyの「SQL Server 2005 T-SQL Enhancements」という記事があります。Jimは話が上手で、テクノロジ全般に精通しています。
『CoDe Magazine』誌の2006年5月/6月号には、Shawn Wildermuthによる「Making Sense of the XML DataType in SQL Server 2005」というすばらしい記事が掲載されています。もし「年間最優秀記事賞」のようなものがあったとしたら、私はこの記事に1票を投じたいと思います。Shawnの名前をGoogleで検索してみると、ADO.NETに関する有益な記事がネット上で多数見つかります。
2つ目は、Manuel Abadiaがネット上で公開している、ObjectDataSourceに関する文章です。ExtendedObjectDataSourceという独自クラスも公開されています。Manuelのコンテンツはここから参照できます。
最後に紹介するのは書籍です。Tod Goldingが書いた『Professional .NET 2.0 Generics』（Tod Golding 著、Wiley Publishing、2005年10月）はたいへん優れた本で、ジェネリックについて詳しく解説しています。コードはC#とVB.NETの両方で示されています。
最後に
文章、書類、コードなどを提出したときに、その後になって、もっと良いアイデアが浮かんだという経験はありませんか。実のところ、私は後知恵の帝王です。幸い、そうした場合の対応にはブログがうってつけです。「The Baker's Dozen」に関連する補足情報や追加のヒントについては、私のブログ（www.TheBakersDozen.net）をぜひチェックしてください。他にも、お得な情報が載っているかもしれませんよ。
