You are a database administrator on an instance of SQL Server 2008. Your Prod database contains data relating to your company’s manufacturing processes. You are creating an application that will track how products are assembled on the manufacturing floor and the time taken during each assembly task. A portion of your data model is as follows:
(Ckick on Exhibits to view the table structures)
The AssemblyTask table must track the time it takes to complete each assembly task. Users will frequently run queries specifying time durations to identify assembly tasks that take minimal time or excessive time to complete. You want to minimize database storage where possible. However, query performance is your primary concern.
Which action should you take?
Exhibit:
A. Create a persisted computed column to store each task’s duration, and create an index on the computed column.
B. Create a dedicated column to store each task’s duration and use a DML trigger to populate the column.
C. Create a stored procedure that calculates each task’s duration.
D. 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.
Correct Answer: A
Explanation/Reference:
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. You 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, you could use either of the following ALTER TABLE statements to create the Duration column:
ALTER TABLE dbo.AssemblyTaskADD Duration AS EndTime - StartTime PERSISTED;
ALTER TABLE dbo.AssemblyTaskADD Duration AS EndTime - StartTime;
Then, you could create an index on the Duration column to improve query performance.
You 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.
You 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, you could include Transact-SQL code to prevent the original operation or perform additional or alternative actions. For the database in this scenario, you 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 you 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 you use triggers to additional actions, you 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, you should avoid using triggers because they require more overhead.
You 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.
You 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.
