You are a database developer on an instance of SQL Server 2008. Your Employee table contains a HireDate column that contains the date each employee was employed with the company.
You have created the user-defined function shown in the exhibit to calculate the number days that an employee has been employed with your company:
You have some complex queries that use the dbo.udf_get_days_emp function to return a value. You want to optimize performance for these queries with the least amount of effort.
Which action should you take?
A. Remove the reference to the GETDATE() built-in function in the function’s definition.
B. Replace the function with a stored procedure that accepts an input variable and returns an output variable.
C. Create a view that includes only the function’s result and create an index on the view.
D. Create a computed column in the Employee table that uses the function in its expression definition, and create an index on the computed column.
Correct Answer: B
Explanation/Reference:
In this scenario, the dbo.udf_get_days_emp function is a nondeterministic function. A function is deterministic if it returns the same value each time it is passed the same values. In this scenario, the function could return different values even if it was passed the same input, such as when GETDATE() returns a different value. Nondeterministic user-defined functions have certain restrictions on how they can be used. In this scenario, the best approach would be to rewrite the function as a stored procedure because stored procedures do not have this restriction. If you are not sure whether a function is deterministic, you can return the IsDeterministic property using the OBJECTPROPERTY function. The IsDeterministic property is 1 if the function is deterministic, and 0 if it is not. For example, you could use the following query to determine whether the dbo.udf_get_days_emp function is deterministic or nondeterministic:
SELECT OBJECTPROPERTY(OBJECT_ID(‘dbo.udf_get_days_emp’), ‘IsDeterministic’);
You should not remove the reference to the GETDATE() built-in function in the function’s definition. Although this would make the function deterministic, you would not be able to calculate the number of days the employee has been employed.
You should not create a view that includes only the function’s result and create an index on the view because you cannot create a view that references a nondeterministic function. In this scenario, you would have to create a view based on each value returned from the function. Unlike a UDF or stored procedure, you cannot pass a parameter to a view.
You should not create a computed column in the Employee table that uses the function in its expression definition and create an index on the computed column.
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 that references 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. To be able to create an index 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. A computed column is considered precise if it does not perform floating-point calculations using either a float or real data type. In this scenario, the function is nondeterministic, so using it in the computed column’s expression would prevent you from creating an index on the computed column. You can query the IsDeterministic and IsPrecise properties using the COLUMNPROPERTY function to determine if a computed column is deterministic and precise, respectively.
