You are a professional level SQL Sever 2008 Database Administrator. A database is
included by an instance. And a large table named OrderDetails is included by the database.
Only DML statements on the last three months data are executed by the application
queries. Administrative audits are managed monthly on data which is longer than four
months. The performance problems listed below are found by you in the database. The
performance of the application queries against the OrderDetail table is poor. It takes a long
time to perform the maintenance tasks against the database, index defragmentation is
contained. The performance problems should be solved with on impact on the server
performance. Which action will you perform?
A.
A database snapshot should be created for the OrderDetails table every four months.
And then, the queries should be changed to utilize the present snapshot.
B.
An additional table named OrderDetailsHistory should be created for data older than four
months. And then, the OrderDetails and OrderDetailsHistory tables should be partitioned in
two parts by utilizing the OrderDate column. At last, a SQL Server Agent job that runs every
month should be created and the ALTER TABLE…SWITCH Transact-SQL statement should
be utilized to remove data that is longer than four months to the OrderDetailsHistory table.
C.
An additional table named OrderDetailsHistory should be created for data longer than
four months. And then, a SQL Server Agent job that runs the following Transact-SQL
statement every month should be created. INSERT INTO OrderDetailsHistory SELECT *
FROM OrderDetailsWHERE DATEDIFF(m,OrderDate,GETDATE())>4
D.
An additional table named OrderDetailsHistory should be created for data older than four
months. And then, the following Transact-SQL statement should be utilized. CREATE
TRIGGER trgMoveDataON OrderDetailsAFTER INSERT ASINSERT INTO
OrderDetailsHistory SELECT * FROM OrderDetailsWHERE
DATEDIFF(m,OrderDate,GETDATE())>4
Explanation: