You are the administrator of your company network. And you’re in charge of a SQL Server 2008 infrastructure. The instance contains a database names PPR. Database PPR has a large table named PBT. The application queries only execute DML statements on the last two months data.
Administrative audits are conducted monthly on data older than two months. In the database, you find some performance issues, such as the performance of the application queries against the PBT table is poor, the maintenance tasks against the database, including index defragmentation, run quite slowly. So what should you do to solve these problems while not having effect on sever performance.
A.
You should develop a database snapshot for the PBT table every three months. Modify the queries to use the current snapshot.
B.
You should develop an additional table named PBTHistory for data older than three months.
Create a SQL Server Agent job that runs the following Transact-SQL statement every month.
INSERT INTO PBTHistory SELECT * FROM PBT WHERE DATEDIFF(m, OrderDate,GETDATE())>3
C.
You should develop Create an additional table named PBTHistory for data older than three months.
Use the following Transact-SQL statement.
CREATE TRIGGER trgMoveDataON PBT AFTER INSERT ASINSERT INTO PBTHistory SELECT * FROM PBT WHERE DATEDIFF(m,OrderDate,GETDATE())>3
D.
You should develop an additional table named PBTHistory for data older than three months.Partition the PBT and PBTHistory tables in two parts by using the OrderDate colum.
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 PBTHistorie table.