You administer a SQL Server 2008 infrastructure. An instance contains a database that
includes a large table named OrderDetails. The application queries only execute DML
statements on the last three months data. Administrative audits are conducted monthly on
data older than three months. You discover the following performance problems in the
database. The performance of the application queries against the OrderDetail table is poor.
The maintenance tasks against the database, including index defragmentation, take a long
time. You need to resolve the performance problems without affecting the server
performance. What should you do?
A.
Create a database snapshot for the OrderDetails table every three months. Modify the
queries to use the current snapshot.
B.
Create an additional table named OrderDetailsHistory for data older than three months.
Partition the OrderDetails and OrderDetailsHistory tables in two parts by using the
OrderDate column. Create a SQL Server Agent job that runs every month and uses the
ALTER TABLE…SWITCH Transact- SQL statement to move data that is older than three
months to the OrderDetailsHistory table.
C.
Create an additional table named OrderDetailsHistory for data older than three months.
Create a SQL Server Agent job that runs the following Transact-SQL statement every
month. INSERT INTO OrderDetailsHistory SELECT * FROM OrderDetails WHERE
DATEDIFF(m,OrderDate,GETDATE())>3
D.
Create an additional table named OrderDetailsHistory for data older than three months.
use the following Transact-SQL statement. CREATE TRIGGER trgMoveData ON
OrderDetails AFTER INSERT AS INSERT INTO OrderDetailsHistory SELECT * FROM
OrderDetails WHERE DATEDIFF(m,OrderDate,GETDATE())>3