SQL Sever 2005に移行したときに私が最もダメージを受けたのが、「TOP 100 PERCENT」だった。
Viewやテーブル関数にTOP 100 PERCENTを指定すると、ORDER BYが切れるようになるというのは、SQL Server 2000時代でもある意味Tipsとして通用していたと思うが、SQL Server 2005ではこれが使えなくなったのだった。
最初にこれに気づいたときは、なぜこうなるのかわからず、正直かなり動揺した。このことについては、ヘルプなどのあまりフォーマルな形ではアナウンスされなかったと思う。ただ、そのときいろいろ調べて「TOP 100 PERCENTであれば、全件を返すわけだから、オプティマイザは並べ替えを行わない」という変更が行われたのを知ったのだが、そのときあるサイトに書かれていたコメントを見て、めげたのを覚えている。曰く、「マイクロソフトがサポートしていると言っていないものを勝手に使う方が悪い」
それは正論ですが。。
ただ、この使い方は、あの名著「SQL Server 2005 ストアドプロシージャプログラミング」でもTipsとして紹介されているものだった。実際、Viewが照会しやすいように並び替えられていれば、利用価値は高いし、プログラムに組み込まれている場合はやはりニーズはあるものなのです。
1)私の職場ではまだExcelレポートの呪縛から逃れられていないので、Excelテンプレートに値をセットしてレポートに仕上げる仕組みがいまだ健在だったりする(そういうところは結構多いのでは?)。
あくまでもDBよりの私は、そこに流し込むデータの一切をDB側でくみ上げようとします。もちろん、見出し・小計・合計に至るまで。アプリケーションにはデータは加工させない、が基本スタンスなので。
そうやって作成したテーブル関数は実にレポートそのものです。特定のレポートのための関数だから、単純な「SELECT * FROM 。。」で照会できるに越したことはないし、もちろんORDERも同じところ(関数内で)コントロールするに越したことはないわけです。
2)Viewをユーザ照会に使っているときには、表示順序の変更を求められることもままあります。最初は顧客番号順でいいと言っていたのに、件数が増えてくると「残高のない顧客は下の方に出したい」なんて言われたりして。そういうときにVIEWの ORDER BY CUSTNOをORDER BY (CASE WHEN BALANCE=0 THEN 2 ELSE 1 END),CUSTNOに書き換えるだけですむなら、メンテナンスも楽だとおもっています。
とはいいつつ、並び替えてくれないものは仕方がないので、そのときは必死になってViewとTableFunctionを書き替えました。でも、そのうちニーズに抗えず、結局裏ワザに走ることに。。。
「TOP 99.999999 PERCENT」(もちろんテーブル件数と相談ですが)
こんなに苦労していたのに、去年の年末、「SP2の累積プログラムその2」に本件のパッチが含まれていることに気付きました。
「FIX: SQL Server 2005 で ORDER BY 句を使用するビューで照会するとき、結果がランダムな順序にまだ返されます。
SQL Server 2005 でのデータベースにビューを持っています。 ビューの定義で、 SELECT ステートメントは、次の要件を満たします。
• | SELECT ステートメントは、 TOP (100)%式を使用します。 |
• | SELECT ステートメントは、 ORDER BY 句を使用します。 |
ビューで照会するとき、結果がランダムな順序に返されます。
ただし、 Microsoft SQL Server 2000 でこの動作が異なります。 SQL Server 2000 に結果は、 ORDER BY 句で指定される順序に返されます。」
なんだよ。。。仕様じゃないなら、早く直してくれればいいのに。。。
結局累積パッチはあててません(今更あててもね)。累積プログラムも7まででたら、そろそろSP3でしょう。
SP3出るまで待ちます。