Introduction
計算列は、内容が式の結果である仮想列だけです。 通常、テーブルの残りのカラムに基づいたデータを保持するために使用されます。 式には、テーブルの他の非計算列、定数、演算子、および関数を含めることができますが、計算列の式としてクエリを指定することはできません。
それらは「仮想」列なので、テーブルの残りの部分のようにディスク上に保存されません。 実際、これらは保存されず、クエリでカラムにアクセスされるたびに計算されます。
計算列がどのように機能するかを理解する最も良い方法は、サンプルを使用することです。 記事の最後に、この記事で使用したすべてのスクリプトを含むファイルが用意されています。 まず最初に、請求書情報を格納するテーブルと、その請求書の明細行を格納するテーブルの2つを作成します。 また、サンプルデータを作成するために、スクリプトファイルにいくつかの挿入があります。
CREATE TABLE invoices( id_invoice INT PRIMARY KEY IDENTITY , customer_name VARCHAR(25));CREATE TABLE detail_lines( id_detail INT PRIMARY KEY IDENTITY , id_invoice_detail INT , product VARCHAR(30) , unit_price MONEY , quantity INT , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));
計算列を作成する方法は、テーブルの他の列を作成するのと同じ方法で、CREATE TABLEまたはALTER TABLEステートメントを使用して作成することができます。 計算されたカラムでは、カラムのデータ型をカラムの内容を取得するために使用される式に置き換えます。 構文は、カラム名、”as “というキーワード、そして式の順になります。 detail_lineテーブルに計算列を作成して、unit_priceとquantityを掛け合わせた行の合計金額を格納してみましょう。
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
計算列のチェック
列が本当に計算列かどうかを確認するには、いくつかの方法があります。 その1つは、関数columnproperty()でプロパティ “IsComputed “を指定する方法です。
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
計算列の情報を取得する別の方法は、システムビュー、sys.computed_columnsを通して行う方法です。 このビューは sys.columns ビューの拡張です。 つまり、sys.computed_columns は sys.columns ビューからすべてのカラムを継承し、さらにこのタイプのカラムに固有のカラムを追加します。 この記事を通して、計算されたカラムの異なる特性を見るために、このビューのカラムのいくつかを見ていきます。 今のところ、このビューは計算されたカラムのみを表示し、カラムが計算されているかどうかを示すis_computedというカラムを持っていることを知っていれば十分です。 明らかに、このビューのすべてのレコードはこの列に1を持ちます。
SELECT name, is_computed FROM sys.computed_columns;
列の内容は、列がクエリで参照されるたびに計算されるので、その内容は常に更新されます。 式に含まれるカラムに変更があると、自動的にカラムの値にも反映されます。 サンプルのdetail_linesテーブルのレジストリで数量を変更し、結果を確認することで確認できます。
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
A Step Forward
これまで見てきた計算列は、他の列との計算を実行するだけなので、非常に基本的なものでした。 しかし、計算された列の式には、T-SQL 標準関数とユーザー定義関数（UDF）の両方の関数を含めることができます。 このようにして、これらの列の機能をはるかに拡張することが可能です。
そのサンプルを見てみましょう。 請求書テーブルに、請求書の合計金額を計算する計算列を追加することにします。 これを行うには、請求書番号を取得し、detail_lines テーブルに問い合わせて、その請求書 ID を持つすべてのレコードから total_amount を合計する必要があります。 これを行うには、請求書IDをパラメータとして受け取り、合計を返す関数を使用するのが最もよい方法です。
CREATE FUNCTION fn_total_invoice (@invoice_number INT)RETURNS MONEYASBEGIN DECLARE @total MONEY SELECT @total=SUM(total_amount) FROM detail_lines WHERE id_invoice_detail = @invoice_number RETURN @totalENDALTER TABLE invoices ADD total_invoice AS dbo.fn_total_invoice(id_invoice)
この列が正しく動作しているかどうかは、detail_lines テーブルに新しいレコードを追加して、total_bill が変化するかどうかで確認できます。 残念ながら、これは可能ではありません。 その変更を行うには、列を削除し、新しい式で再作成する必要があります。
計算列が外部関数を使用している場合、この関数を変更することは許可されません。 変更しようとすると、この関数がテーブルにリンクされていることを示すエラーが表示されます。 関数を変更するには、列を削除し、関数の変更を行い、最後に関数の新しいバージョンで列を再作成する必要があります。
sys.computed_columns ビューの “definition” 列で列の定義を取得できます。
SEECT name, definition FROM sys.computed_columns
Computed Column
前述のとおり、これらの列は “virtual” なので物理的にテーブルに格納されることはありません。 しかし、計算されたカラムを強制的にテーブルに物理的に格納することが可能であり、これはカラムの “永続化 “と呼ばれています。 これは、カラムが参照されるたびに計算を実行する必要がないため、SELECT 文のパフォーマンスを向上させることができます。
さらに、カラムを持続させるには、カラムを作成するために使用する式が「決定論的」でなければなりません。 Microsoft の Web サイトで見ることができるように、「決定論的関数は、特定の入力値のセットで呼び出され、データベースの状態が同じであれば、いつでも同じ結果を返します」。 (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). SQL Server が計算列の式を決定論的とみなすかどうかを知りたい場合は、columnproperty() 関数に “IsDeterministic” プロパティを指定して使用できます。
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
列の定義がユーザー定義関数である場合、その関数自体が決定論的かどうかを確認することも可能です。 これを行うには、IsDeterministic プロパティを持つ objectproperty() 関数を使用する必要があります。
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
クエリからわかるように、最初の例の列では、詳細の合計価格を計算していますが、これは決定論的と見なされます。 しかし、請求書の合計金額を計算する関数は非決定論的とみなされます。 このように、detail_table テーブルの total_price 列だけがテーブルに格納できます。
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
再び sys.computed_columns ビューで is_persisted フィールドを見ることができますが、これは列がテーブルで持続されるかどうかを示すことになります。
Computed Columns を使用したインデックス
インデックスで Computed Columns を使用することは可能ですが、いくつかの要件を満たす必要があります：
- Ownership: 所有権：計算列の定義で使用されるすべての関数は、テーブルと同じユーザーによって所有されている必要があります。 計算されたカラムは決定論的でなければなりません。 また、列がCLR式を含む場合、決定論的であることに加えて、列は永続化されなければなりません。 計算されたカラムの式は正確でなければならない。 これは、”float” または “real” データ型であってはならないことを意味する。 また、定義でこのタイプのデータを使用することもできません。 この機能は、columnproperty()関数でIsPreciseプロパティを指定することで確認することができます。 計算された列は、text、ntext、または image のタイプにすることはできません。 また、式にimage、ntext、text、varchar（max）、nvarchar（max）、varbinary（max）、またはxmlデータ型が含まれている場合、式の結果のデータ型がインデックスで許可されている場合のみ使用できます。
これらの考慮事項に加え、列の作成に使用する接続とインデックスの作成に使用する接続は、これらのアクションを実行するために特定の設定を行う必要があります。 これは、columnproperty() 関数で IsAnsiNullsOn プロパティを指定して確認できます。
インデックスを作成する接続、およびインデックスに影響を与えるレコードの挿入、更新、削除を実行する接続は、ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER オプションをアクティブにしておく必要があります。
最後の考察
最後に、計算された列を正しく使用するために知っておく必要がある、いくつかの追加の側面を確認するつもりです。
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
上記にもかかわらず、計算された列はDEFAULTまたはFOREIGN KEY制約定義で使用することができません。
一方、計算された列はPRIMARY KEYまたはUNIQUE制約の一部として使用することができます。
結論
計算された列の使用は、いくつかの状況で非常に有用である可能性があります。 しかし、インデックスを作成したり、永続化したりといった制約があるため、使用する場所については慎重に検討する必要があります。 これはほんの始まりに過ぎません。 新しいことを自由に試し、計算されたカラムを実験して、新しい可能性を見つけてください。