SQL Server の計算列 (Computed Column) の使い方と注意点

SQL Server の計算列 (Computed Column)

データベースのテーブルを設計する時に、他のカラムの値を使って計算した結果をカラムとして持たせたいような時ありませんか？

例えば、注文の情報を持つ Orders テーブルに OrderQty（注文個数）とUnitPrice（単価）のカラムがあって、OrderQty * UnitPrice で計算された OrderAmount（注文金額）というようなカラムを持たせたいような時です。

そんなときに便利なのが、計算列 (Computed Column) です。

この記事では、計算列の基本的な使い方と注意点などをご説明します。

計算列 (Computed Column) の基本

SQL Server の計算列 (Computed Column) は、同じテーブルの他のカラムや定数などから自動計算されるカラムです。

デフォルトではデータは保持せず、クエリーが実行される際に、定義した計算式を使って計算されます。

PERSISTED と指定しておけば、計算結果が物理的に保存されます。

計算式で使われている他のカラムの値が更新された時に、計算列の値に自動的に反映されるので、便利ですし、自分で計算して値を保存するのと比べると間違いがないです。

CREATE TABLE 文で計算列を指定するには、次のようにできます。

CREATE TABLE Orders (
  OrderID INT NOT NULL IDENTITY(1,1),
  OrderQty INT NULL,
  UnitPrice DECIMAL(17,2) NULL,
  OrderAmount AS ISNULL(OrderQty, 0) * ISNULL(UnitPrice, 0)
  CONSTRAINT PK_Order PRIMARY KEY (OrderID)
);

カラム名の後に AS [計算式] のように定義します。

この CREATE TABLE 文を実行すると次のようなテーブルができました。

計算列のデータ型は、計算式に出てくるデータ型の中で優先順位が高いものになり、優先順位が低いデータ型は高いデータ型に暗黙的に変換されて計算されます。

今回は、OrderQty が INT で、UnitPrice は DECIMAL(17,2) なので、OrderQty が DECIMAL 型に変換されて計算されます。

INT 型は演算時に精度計算ルールにより、DECIMAL(10,0) として扱われます。

「乗算」の有効桁数、小数点以下桁数、長さのルールでは、DECIMAL(p1, s1) * DECIMAL(p2, s2) の場合、DECIMAL(p1 + p2 + 1, s1 + s2) になります。

ですので、今回 OrderAmount のデータ型は DECIMAL(17 + 10 + 1, 2 + 0) -> DECIMAL(28, 2) になっています。

PERSISTED にしたい時は、計算式の後に次のように指定します。

CREATE TABLE Orders (
  OrderID INT NOT NULL IDENTITY(1,1),
  OrderQty INT NULL,
  UnitPrice DECIMAL(17,2) NULL,
  OrderAmount AS ISNULL(OrderQty, 0) * ISNULL(UnitPrice, 0) PERSISTED,
  CONSTRAINT PK_Order PRIMARY KEY (OrderID)
);

OrderAmount カラムのデータ型は同じですが、デザイン画面で見ると Is Persisted が Yes になっています。

実際に Orders テーブルに値を挿入して、計算列の値を確認してみます。

INSERT INTO Orders
  (OrderQty, UnitPrice)
VALUES
  (2, 500.5),
  (4, 100),
  (10, 11.99);

上記の INSERT 文を実行した後、SELECT 文で Orders テーブルのデータを見てみると OrderAmount カラムに ISNULL(OrderQty, 0) * ISNULL(UnitPrice, 0) の計算結果の値が入っていることがわかります。

次の UPDATE 文で、OrderID = 1 のレコードの OrderQty を 2 から 3 に変更してみます。

UPDATE Orders
SET    OrderQty = 3
WHERE  OrderID = 1;

上記の UPDATE 文を実行した後、再度SELECT 文で Orders テーブルのデータを確認します。

OrderID = 1 の OrderAmount の値も自動的に再計算されていますね。

計算列 (Computed Column) の注意点

先ほどは、CREATE TABLE 文で計算列を定義しましたが、ALTER TABLE - ADD 文を使って、後から計算列を追加することも可能です。

ですが、ALTER TABLE - ALTER COLUMN 文を使って、計算列に変更したり、計算列を普通のカラムに変更したりすることはできません。

既存の普通のカラムを計算列にしたい場合は一度 ALTER TABLE - DROP COLUMN でカラムを削除してから、ALTER TABLE - ADD を使って、計算列を追加する必要があります。

計算列を PERSISTED にするかどうかですが、計算式が複雑だったり、よく使うカラムの場合は、計算結果を保存しておくことで、パフォーマンスを向上させることができます。

計算式に関連するカラムのデータ挿入・更新時に、計算してデータを保存する時間がかかり、またデータを物理的に保存することになるので、その分の容量は使います。

多少データ容量を使うことには問題がなく、毎回計算列を使うたびに計算されるよりも、一度計算しておいて、計算列を使う際のパフォーマンスが良いほうがいいので、私は PERSISTED にすることが多いです。

また、計算列 の計算式に非決定的関数（GETDATE 関数, NEWID 関数など）が使われている場合は PERSISTED にできないのでご注意ください。

以上、計算列の基本的な使い方と注意点などをご説明しました。