SQL Serverには”計算列”という便利な列を定義することができます。
計算列は同じテーブルの他の列や関数、定数などを組み合わせた計算式によって計算される列です。
テーブルに物理的に保存されない仮想列で、「列名 AS 計算式」の形式で定義します。
例えば、以下のテーブルでは[金額]という列を[単価]*[数量]で求められる計算列として定義しています。
CREATE TABLE [注文明細] ( [注文ID] int, [注文明細NO] int, [商品コード] int, [単価] int, [数量] int, [金額] AS [単価]*[数量] ) コマンドは正常に終了しました。
このテーブルに対して、（計算列の値を指定せずに）レコードを挿入すると、
INSERT INTO [注文明細] VALUES ( 1, 1, 1, 100, 6 ) (1 件処理されました)
レコードを取得した際に、計算列の値が計算されて返ってきます。
SELECT * FROM [注文明細] 注文ID 注文明細NO 商品コード 単価 数量 金額 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 100 6 600 (1 件処理されました)
ただし、以下のように計算列の値まで指定してレコードを挿入しようとすると、エラーになります。
INSERT INTO [注文明細] VALUES ( 1, 2, 2, 200, 8, 1600 ) サーバー : メッセージ 213、レベル 16、状態 5、行 1 挿入エラー : 列名または列数の値がテーブルの定義と不一致です。
また、計算列を定義する際にはデータ型を指定しないため、計算に使われている列や関数のデータ型のうち最大精度のデータ型が計算列のデータ型となります。
したがって、以下のように計算結果がオーバーフローを起こすようなレコードを挿入すると、挿入は正常に行われますが、
INSERT INTO [注文明細] VALUES ( 1, 3, 3, 1000000, 1000000 ) (1 件処理されました)
レコードを取得する際に、オーバーフローが発生します。
SELECT * FROM [注文明細] 注文ID 注文明細NO 商品コード 単価 数量 金額 ----------- ----------- ----------- ----------- ----------- ----------- 1 1 1 100 6 600 (2 件処理されました) サーバー : メッセージ 8115、レベル 16、状態 2、行 1 expression から型 int への変換で、算術オーバーフロー エラー。
故に、計算列を定義する場合には、計算の結果、オーバーフローが発生しないかどうかをよく確認しておく必要があります。
ちなみに上記のエラーは、例えば以下のような定義に変えてやればOKです。
ALTER TABLE [注文明細] DROP COLUMN [金額] コマンドは正常に終了しました。 ALTER TABLE [注文明細] ADD [金額] AS CAST( [単価] AS bigint ) * [数量] コマンドは正常に終了しました。 SELECT * FROM [注文明細] 注文ID 注文明細NO 商品コード 単価 数量 金額 ----------- ----------- ----------- ----------- ----------- -------------------- 1 1 1 100 6 600 1 3 3 1000000 1000000 1000000000000 (2 件処理されました)
なお、計算列はレコードの取得だけでなく、並べ替えや検索条件にも利用することができます。
SELECT * FROM [注文明細] ORDER BY [金額] DESC 注文ID 注文明細NO 商品コード 単価 数量 金額 ----------- ----------- ----------- ----------- ----------- -------------------- 1 3 3 1000000 1000000 1000000000000 1 1 1 100 6 600 (2 件処理されました) SELECT * FROM [注文明細] WHERE [金額] > 10000 注文ID 注文明細NO 商品コード 単価 数量 金額 ----------- ----------- ----------- ----------- ----------- -------------------- 1 3 3 1000000 1000000 1000000000000 (1 件処理されました)
さらにインデックス(※)を作成することもできます。
※計算式に不定要素が含まれる場合は不可
CREATE INDEX [IX_注文明細] ON [注文明細] ([金額]) コマンドは正常に終了しました。
という具合に、計算列には制約がらみで若干制限事項があるものの、従来、データモデルの正規化によってテーブルから削除されていた導出項目を計算列として定義するなど、アイデア次第でいろいろと応用できるのではないでしょうか。