SQL Server: Computed Columns

Computed columns are virtual columns that are not physically stored in the table by default. Each computed column uses the AS keyword followed by an expression that evaluates to a value. The expression can contain constants, functions, operators, and references to other columns within the table. The value of the computed column is calculated each time a query referencing it executes. DBA/Developer can also include the optional PERSISTED keyword when creating a computed column. When a persisted computed column is created, it is physically stored in the table, and is recalculated each time a column value referenced in the calculation expression is changed. For an index to be created on a computed column, the column must be deterministic and precise. A computed column is considered deterministic if it produces the same value each time it is passed the same values. For example, the GETDATE() function returns a different value each time it is called. Therefore, a computed column that referenced the GETDATE() function would be non-deterministic. A computed column is considered precise if it does not perform floating-point calculations using a float or real data type. For example, a computed column that returns an int data type but uses a float or real data type in its definition is imprecise, and a computed column that returns an int data type and does not use a float or real data type is precise. In this scenario, the calculation for the Duration is deterministic and precise. Therefore, DBA/Developer could use either of the following

ALTER TABLE statements to create the Duration column:

ALTER TABLE dba.AssemblyTaskADD Duration AS EndTime - StartTime PERSISTED;
ALTER TABLE dba.AssemblyTaskADD Duration AS EndTime - StartTime;

Then, DBA/Developer could create an index on the Duration column to improve query performance. DBA/Developer can use the IsDeterministic and IsPrecise properties with the COLUMNPROPERTY function to determine if an expression used in a computed column is deterministic and precise, respectively. DBA/Developer should not create a dedicated column to store each task's duration and use a DML trigger to populate the column. A DML trigger defines Transact-SQL code that executes when DML statements, such as INSERT, UPDATE, and DELETE statements, are issued on tables or views. Within the body of the trigger, DBA/Developer could include Transact-SQL code to prevent the original operation or perform additional or alternative actions. For the database in this scenario, DBA/Developer could use an INSTEAD OF trigger to fire for each insert or update. The trigger could calculate the duration value. A DML trigger might be used if DBA/Developer wanted to log DML errors but continue processing or perform complex validation that roll backs the triggering operation or returns an error message to the caller. If DBA/Developer use triggers to additional actions, DBA/Developer should note that AFTER triggers do not fire if the triggering SQL statement fails, for example if constraint checks are not successful. However, in this scenario, performance is a primary concern. Therefore, DBA/Developer should avoid using triggers because they require more overhead. DBA/Developer should not create a stored procedure that calculates each task's duration. This would not improve query performance because the stored procedure call would have to be included for each query. DBA/Developer should not create a non persisted computed column that includes a CLR user-defined function to store each task's duration, and create an index on the computed column. To create an index on a computed column that references a CLR user-defined function, the computed column must be persisted.

                               Fig. 1 Table Structure of Production Database