夏椰の東屋

今日はテーブルに計算列を持たせ、そこにCHECK制約をつけて遊んでみました。

#ターゲットはSQLServer20005Expressです。

まずは、計算列だけを持つCREATE TABLE文です。

CREATE TABLE testProcCol 
(
 id INT, 
 padid AS REPLICATE('0',5-LEN(CAST(id AS VARCHAR))) 
    + CAST(id AS VARCHAR)
) ;

ここへINSERTを発行します。

?

insert into testProcCol values(0);

で、SELECTして結果を取得してみますと・・・

となります。

?

CREATE　TABLEの時にPADIDとして指定したのは

　　・REPEAT('0', 回数) →'0'の文字を回数分つなげた文字列を返す。

　　・CAST(列名 as varchar)→列名で指定されたデータをVARCHARへ変換する。

　　・LEN(文字列)→文字列の長さを取得する。

の3つです。

PADIDの長さを5として、'0'をつなげる回数は5からIDを文字列にした時の長さを引いたものになります。

それに文字列化したIDをつなげたら前0パディングされた文字列のIDが出てきます。

#後でCHECK制約が出てくるので、現状として回数がマイナス値になったらNULLになるのは省きます(--;

?

で、PADIDに対して01000～09999間での値ぢゃなきゃ嫌だ！って制約があったとします。

なので、計算列のPADIDに対してCHECK制約をかけてみます。

#IDに対して1000～9999であるCHECK制約をつけるのは簡単なので、あえて計算列のPADIDにつけます。

CREATE TABLE testProcCol 
(
 id INT, 
 padid AS REPLICATE('0',5-LEN(CAST(id AS VARCHAR))) 
    + CAST(id AS VARCHAR) PERSISTED 
 CHECK( padid IS NOT NULL AND padid LIKE '0[0-9][0-9][0-9][0-9]' )
) ;

このテーブルにデータを追加してみましょう。

?

insert into testProcCol values(0);　　  →OK
insert into testProcCol values(1000);　　→OK
insert into testProcCol values(9999);　　→OK
insert into testProcCol values(10000);　　→NG(1桁目が0にならない)
insert into testProcCol values(99999);　　→NG(1桁目が0にならない)
insert into testProcCol values(100000);　　→NG(REPLICATEの第2引数がマイナスになる→PADIDがNULLになる)

よってSELECTで取得できるデータは以下の通りになります。

チェック制約に引っかかるということはデータの更新時に

計算列の計算が行われているということに気づくと思います。

#ただし、これはチェック制約をチェックするために計算しているだけです。

?

このデータを取得する際の実行計画ですが、

以下のようになります。

?

これを見ると、取得時に計算列が計算されていることがわかりますね。

?

ということで、遊んでみた結果を載せてみた。

うまく使うと楽になる要素が多々ありそうな気がします。

#気がするだけ？(ｗｗ