Home » Microsoft » MB6-886 » What should you do to minimize the effect of this query on the server?
You intend to use SQL Server 2008 to create a database solution.
There’s a large table in the database.
The table is updated not very often. A query is executed against the table by users.
The execution of a complex calculation is required by the query.
The calculation involves multiple columns for a given row.
You notice that because the query is CPU intensive, the query has a bad performance.
So what should you do to minimize the effect of this query on the server?
A. A view should be created on the table.
B. An index should be created on each field.
C. On the table, a computed column should be created.
D. On the table, a persisted computed column should be created
Correct Answer: D
Explanation/Reference:
Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. That’s why using a computed column will not improve performance.
The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table.
Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED you can speed up your
SELECT query execution.