後で、SQL Server 2008 とかの最新バージョンを調べますが、落とし穴になりかねないのでメモ。
create table test1 ( id int, name varchar(10), num int );
として test1 テーブルを作成しておきます。
ここに次のようなデータを入れます。
delete from test1 ; insert into test1 values(1,'masuda',10); insert into test1 values(2,'tomoaki',20); insert into test1 values(3,'100',30);
このデータを検索します。
select * from test1 where num = 10 ;
この結果は予想通り、1行目が取得できます。
1 masuda 10
さて、num に文字列の’10’を指定したときはどうなるでしょうか?
select * from test1 where num = '10' ;
マッチングしないと思いきや、自動的に文字列型から数値型に変換されて、1行取得されます。
# Oracle の場合は変換エラーになったと思うのだけど、うろ覚えです。
1 masuda 10
逆に文字列型(varchar)に対して数値で比較してみると、
select * from test1 where name = 100 ;
これはエラーになります。
サーバー : メッセージ 245、レベル 16、状態 1、行 1 構文エラー。varchar 値 'masuda ' から int データ型に変換できませんでした。
1行目の name 列の値「masuda」が int 型に変換できないためにエラーになっていますね。
となると、name 列の値が全て数値型になるようにしたら、どうなるのでしょうか?
delete from test1 ; insert into test1 values(1,'100',10); insert into test1 values(2,'200',20); insert into test1 values(3,'300',30);
というデータを入れた場合に、次の SQL を動かすと、
select * from test1 where name = 100 ;
実は、結果が取れるのですッ!!!
1 100 10
そんな訳で、SQL Server 2005 の SQL を書くときに、自動変換をあてにしたり、自動変換がされるような書き方をすると、はまる可能性があるよという話でした。
これは、.NET から扱うときに、SqlParamter オブジェクトの使い方が問題になってくるのですよね。これは別の記事に。
自動変換だと、1番でなく2番のように動くのでパフォーマンスに影響して困ります。
(1) select * from test1 where name=cast(100 as varchar);
(2) select * from test1 where cast(name as integer)=100;
SQL Server だけでなく、Oracle でも同様な処理なのは、ショックでした。
ああ、SQL Server 2008 R2 で試してみたのですが、取れますねぇ。
ああ、MySQL で試してみたのですが、取れますねぇ。
ひょっとして、これが RDB の基本の動作なのかも、思ったりします。
ただ、MySQL のほうが賢くて、name に ‘masuda’ を入れても、
select * from test1 where name = 100 ;
で、こけません。。。それならば、対称性を考えるとそっちのほうがいいかという感じもするけど。