You are a professional level SQL Sever 2008 Database Administrator in an international corporation
named Wiikigo. You are experienced in managing databases in an enterprise-level organization, optimizing
and sustaining the database life cycle.
In the company, your job is to implement solutions on security, troubleshooting, deployment and optimization.
A SQL Server 2008 infrastructure is managed by you. 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