By: Atif Shehzad｜更新しました。 2011-08-29 ｜ コメント (8) ｜ 関連記事 その他の > T-SQL

Problem

私は、単純なシナリオで計算列を使用し、これらはうまく動作しています。 しかし、いくつかのケースでは、計算された列を使用してビジネス ロジックを実装する際に制限に直面します。 たとえば、計算された値を決定するために、別の式に基づいて異なる値を持つことが必要です。 また、ゼロ除算エラーが発生する可能性があり、これを防止する必要があります。 また、計算列式で使用するために、計算列テーブルの外側の列にアクセスする必要があります。

Solution

計算列は、データベース設計に大きな柔軟性を与えることができます。 計算式を条件付きで適用し、ゼロ除算エラーを処理したり、計算列のテーブル外の任意の列にアクセスしたりすることが可能です。 これは計算カラムをより柔軟で便利なデータベース設計にする。

• Conditional computation of values
• Divided by zero error
• Accessing a column outside the computed column table

T-SQL techniques to solve the above mentioned issues.This will use the simple T-SQL techniques.

値の条件付き計算

従業員に関連するいくつかの列がある単純なテーブルがあります。 また、60歳での退職を計算する計算列があります。 管理職の年齢制限を65歳に設定し、その他の従業員には60歳に維持するという新しい要件があります。

これを達成するには、次のコードに示すように、CASE文を使用した条件式を使用します。 DORetirement 列で、指定が「マネージャー」である場合、退職は 65 になり、それ以外の場合は 60 になります。 CASE文を使用することで、与えられた条件によって別々の式を定義することができます。

Divided by Zero Error

計算式では、割るときに分母にゼロが含まれる可能性があります。

計算式 AS (分子/分母) で、分子と分母の列が計算列に使用されるシナリオを考えてみます。 この場合、計算列式が分母列にゼロを使用して計算されるたびに、ゼロによる除算エラーが発生します。

このエラーを回避するには、以下のようにNULLIF関数を使用して分母の0をNULL値で置き換えます。 テーブルに挿入する4番目のレコードはゼロによる除算の問題が発生しますが、NULLIF関数はこれをNULLに変換します。

-- Script# 2: Avoiding divided by zero error-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( int NOT NULL, int NOT NULL, AS (Numerator/NULLIF(Denominator,0)) )GO--Insert sample dataINSERT INTO CCTest (Numerator, Denominator) SELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 3 UNION ALLSELECT 88, 0 UNION ALLSELECT 90, 15GO-- Check the resultSELECT * from CCTestGO

NULL で割り切れないので、返される値は、このレコード 1 件の列の NULL です。 ただし、永続化された計算列でのみ、NOT NULL を明示的に指定することができます。

計算列テーブルの外の列にアクセスする

計算列は、そのテーブルの外の列に直接アクセスすることができません。 この制限は、ユーザ定義関数を使用することで克服できる場合があります。 UDFは、計算列テーブルの外側の任意の列にアクセスするために式で使用することができます。

以下のスクリプトでは、従業員の休暇残高を計算し、従業員が最大20日を超える休暇を何日持っているかを示すUDFが作成されています。 このデータは、LeaveBalanceという名前のセカンダリテーブルから取得されます。

--Script # 3: Use UDF to access column in other table-- Use sample databaseUSE GO -- Create Table to reference in UDFIF OBJECT_ID('LeaveBalance', 'U') IS NOT NULL DROP TABLE LeaveBalanceGOCREATE TABLE . ( INT NOT NULL, TINYINT NOT NULL, )GO--Insert sample dataINSERT INTO LeaveBalanceSELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 13 UNION ALLSELECT 88, 7 UNION ALLSELECT 90, 15GO-- Create UDF to get leave balanceIF OBJECT_ID('UDF_GetLeaveBalance', 'FN') IS NOT NULL DROP FUNCTION UDF_GetLeaveBalanceGO-- Create UDF to use in computed columnCREATE FUNCTION UDF_GetLeaveBalance (@EmpNumb int)RETURNS TINYINTASBEGIN DECLARE @LeaveBalance TINYINT SELECT @LeaveBalance = (20 - LeavesAvailed) FROM LeaveBalance WHERE EmpNumb = @empnumb RETURN @LeaveBalanceENDGO-- Create Table to use computed columnIF OBJECT_ID('CCTest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, AS (.UDF_GetLeaveBalance(EmpNumb)) )GO--Insert sample dataINSERT INTO CCTest (EmpNumb, Designation) SELECT 840, 'DBA' UNION ALLSELECT 805, 'DBA' UNION ALLSELECT 846, 'Manager' UNION ALLSELECT 88, 'Manager' UNION ALLSELECT 90, 'Developer' GO-- Check the resultSELECT * from CCTestGO

そこで、以下では、各従業員が利用できる残りの日数を表示します。 これについては、こちらのヒント：SQL Server で計算された列にインデックスを作成する方法 で詳しく説明されています。

次のステップ

上記の問題とその解決策は、永続化された計算列でも永続化されない計算列でも同じように機能します。 計算されたカラムの利用可能な柔軟性を活用することで、データベース設計を改善することができるかもしれません。

• Click here to read tip about basics of working with computed columns
• Click here to read tip about creating indexes on computed columns

Last Updated: 2011-08-29

著者について

Atif Shehzadは熱心なSQL Server DBA、技術評論家、記事作成者として知られています。
私のヒントをすべて見る

ヒントをすべて見る