野良エンジニアの足跡 -20ページ目

ブログ画像一覧を見る

このブログをフォローする

<< 前のページへ最新 | 18 | 19 | 20 | 21 | 22 | 最初次のページへ >>

LIKE演算子の謎

こんにちは、naginoです。

とりあえず SQL Server 2005 SP2（9.0.3068）で確認しました。

原因が理解できていないので、とりあえずです。

SP3 が出ているので、SP3 でどうなるかは近いうちに確認する予定です。

元ネタはこちら。

http://forums.microsoft.com/msdn-ja/ShowPost.aspx?PostID=3837833&SiteID=7

色々な情報が錯綜していますが、インデックスが張られた nchar 型の列で、LIKE 演算が怪しい動きをするということです。

下準備のクエリは以下です。

CREATE TABLE T(P CHAR(3) COLLATE Japanese_CI_AS)
CREATE INDEX I ON T(P)
INSERT T VALUES(N'2/7')
INSERT T VALUES(N'207')
INSERT T VALUES(N'217')

テーブル T が作成されます。

そのテーブルに対して以下のクエリを実行すると、妙なことが起こります。

SELECT * FROM T WHERE convert(NCHAR(3), P) like N'20_' COLLATE Japanese_CI_AS
⇒結果が0件！

一方インデックスを削除すると、ちゃんと 1件帰ってきます。

DROP INDEX I on T
SELECT * FROM T WHERE convert(NCHAR(3), P) like N'20_' COLLATE Japanese_CI_AS

さて、どういうことが起きているか、内部に少し立ち入ってみます。

実行プランを見比べてみるとわかるのですが、インデックスが張られている場合はなにやら妙な処理が多数入っています。

どうもこれが原因のようです。

おそらくですが、インデックスを使おうとして、検索範囲の上限値と下限値をまず計算しているようなのですが、これが妙なのです。

SET SHOWPLAN_TEXT ON

を実行して、さらにもぐってみると、以下のようになります。

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011], [Expr1012], [Expr1013]))
|--Merge Interval
| |--Concatenation
| |--Compute Scalar(DEFINE:(([Expr1006],[Expr1007],[Expr1005])=GetRangeThroughConvert(N'20',NULL,(22))))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:(([Expr1009],[Expr1010],[Expr1008])=GetRangeThroughConvert(NULL,N'2¼',(10))))
| |--Constant Scan
|--Index Seek(OBJECT:([test].[dbo].[T].[I]), SEEK:([test].[dbo].[T].[P] > [Expr1011] AND [test].[dbo].[T].[P] < [Expr1012]), WHERE:(CONVERT(nchar(3),[test].[dbo].[T].[P],0) like N'20_') ORDERED FORWARD)

「¼」が見えます。

ちなみに以下のクエリだと、次のような結果になります。

SELECT * FROM T WHERE convert(NCHAR(3), P) like N'21_' COLLATE Japanese_CI_AS

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1011], [Expr1012], [Expr1013]))
|--Merge Interval
| |--Concatenation
| |--Compute Scalar(DEFINE:(([Expr1006],[Expr1007],[Expr1005])=GetRangeThroughConvert(N'21',NULL,(22))))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:(([Expr1009],[Expr1010],[Expr1008])=GetRangeThroughConvert(NULL,N'22',(10))))
| |--Constant Scan
|--Index Seek(OBJECT:([test].[dbo].[T].[I]), SEEK:([test].[dbo].[T].[P] > [Expr1011] AND [test].[dbo].[T].[P] < [Expr1012]), WHERE:(CONVERT(nchar(3),[test].[dbo].[T].[P],0) like N'21_') ORDERED FORWARD)

どうやら、検索範囲の下限値を求めているようです。

でも「¼」とは違和感があります。

どういうことかというと、以下のクエリの実行結果が false になるのです。

select case when N'¼' < N'0' COLLATE Japanese_CI_AS then 'true' else 'false' end

ということは、実行プランで下限値を求めているはずが、'0' より大きいので、結果 0件になるようです。

・・・謎です。

ちなみに、以下のクエリは true になります。

select case when '¼' < '0' COLLATE Japanese_CI_AS then 'true' else 'false' end

・・・Unicode の処理が怪しい気がするのですが・・・。

ちなみに以下のクエリの実行結果は、以下のとおり。

select
convert(varbinary, N'1'),
convert(varbinary, '1'),
convert(varbinary, N'0'),
convert(varbinary, '0'),
convert(varbinary, N'¼'),
convert(varbinary, '¼'),
convert(varbinary, N'/'),
convert(varbinary, '/')

⇒0x3100 0x31 0x3000 0x30 0xBC00 0x3F 0x2F00 0x2F

うーん、「¼」が「/」になるのが正しそうなのですが・・・これ以上は 1ユーザに過ぎない私には手が出ないところです。

いずれにせよ、LIKE演算で末尾にワイルドカードを使った範囲検索で、且つ最後の文字が半角数字の「0」の場合、照合順序によっては謎な動きをすることがあるようです。

ただ、以下のクエリの場合は期待通りに動作します。

SELECT * FROM T WHERE convert(NCHAR(3), P) like N'%20_' COLLATE Japanese_CI_AS

実行プランを見るとわかりますし、クエリを見てもわかるように、上限下限が決められない検索ですので、今回のような問題は起きないわけです。

部分一致でシステムを作っている限り目にしないかもしれませんが、なんとなく気持ち悪いですね。

識者にご教授いただきたいところです。

で、部分一致だと上記のような、内部的に範囲検索に置き換える最適化が行えないため、インデックスが使えず、速度の問題が出てくるわけです。

その際に、さて何か対策が無いかという話がありまして、それはまた別の機会に調査する予定です。

まあ、SP3 での上記現象の検証が先になるとは思います。

SQL Server 2005 SP3

こんにちは、naginoです。

ついに待望の SQL Server 2005 の Service Pack 3 がリリースされました。

バグ修正で幾つか大きな修正が行われていますので、該当するケースでは適用を検討する必要があるといえるでしょう。

http://support.microsoft.com/?kbid=955706

DLは以下から。

http://www.microsoft.com/downloads/details.aspx?displaylang=ja&FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4

新機能については以下に記載あります。

今回はそれほど影響の大きい機能追加は無いという印象です。

http://msdn.microsoft.com/ja-jp/library/dd353312(SQL.90).aspx

バグ修正については以下に記載があります。

幾つか致命的な不具合の修正が行われています。

http://support.microsoft.com/?kbid=955706

実は不具合修正の中で 1つ注目しているものがあります。

http://support.microsoft.com/kb/959019/

というのも、MSDNフォーラムで見かけた、とある現象がどのように変化したのかが気になるからです。

これについては、後日検証の予定です。

12/22 追記

CU1 がリリースされました。

http://ameblo.jp/sql/entry-10181516797.html

SP2 CU10 ないし 11 を適用していた場合、SP3 を適用するとダウングレードになりますので、注意が必要です。

SQL Server 2008 版の MCTS、MCITP

こんにちは、naginoです。

随分と間を空けてしまいました。

開発の仕事から縁遠くなって、あまり書けるような内容と出会いませんでした。

MCTS（マイクロソフト認定テクノロジースペシャリスト）と、 MCITP（マイクロソフト認定 IT プロフェッショナル）の SQL Server 2008 対応資格試験が一部始まっています。

http://www.microsoft.com/japan/learning/mcp/mcts/sql/2008/default.mspx

http://www.microsoft.com/japan/learning/mcp/mcitp/sql/2008/default.mspx

例によって、資格体系がまた変わりました。

2000、2005 の時とは異なります。

ちなみに 2005 のときは下図の通りでした。

MCTS が 3種類に分かれ、それぞれの上位に 1つずつ MCITP がある形になっています。

試験番号と組み合わせると以下のとおり。

●DBアドミニストレータ系

MCTS: SQL Server 2008 - インプリメンテーションアンドメンテナンス ⇒ 70-432

MCITP: データベースアドミニストレータ 2008 ⇒ 70-450

SQL Server 2005 系の資格である MCITP データベースアドミニストレータ取得者は、70-453 のみの合格で直接 MCITP 認定されます。

●開発系

MCTS: SQL Server 2008 - データベースデベロップメント ⇒ 70-433
MCITP: データベースデベロッパー 2008 ⇒ 70-451

SQL Server 2005 系の資格である MCITP データベースデベロッパー取得者は、70-454 のみの合格で直接 MCITP 認定されます。

●BI系
MCTS: SQL Server 2008 - ビジネスインテリジェンスデベロップメントアンドメンテナンス ⇒ 70-448
MCITP: ビジネスインテリジェンスデベロッパー 2008 ⇒ 70-452
SQL Server 2005 系の資格である MCITP ビジネスインテリジェンスデベロッパー取得者は、70-455 のみの合格で直接 MCITP 認定されます。

※青字は投稿時点で日本語試験実施中。

https://www1.prometric-jp.com/exam_list/exam_list.asp?client_top_no=MS&client_no=MS