Study with 070-447 most valid questions & verified answers

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005. You work as a database administrator for your company. You utilize the SP:Recompile event class to run SQL Server Profiler. You find that a great amount of recompiles occurring. The possible causes of the large number of recompiles should be identified. From the flowing four options, which one is the most possible cause of the problem?

• A. Ad hoc batches are not being recovered from the procedure cache.
• B. The sp_executesql batches are not being recovered from the procedure cache.
• C. A database administrator executed UPDATE STATISTICS statements on all tables, and the most common stored procedures reference all the tables.
• D. One new stored procedure is created by a database administrator.

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005. You work as a database administrator for your company. There is a user in the Windows SalesManagers group, and he is called Simon. A Reporting Services report is required by Simon to have annual sales information displayed by territory. There is another user in the Windows Sales group, and he is called Jack. He is responsible to report to Simon. A report is required by Jack, and the report is limited to his sales territory, but the same detailed sales data is still displayed, and the detailed sales data is on Simon's report. A secure solution should be designed to meet the requirements of the users listed above, and the least amount of server resources should be utilized. Which action should be performed?

• A. First, two data regions should be contained by a report which is designed for Simon. Second, one data region should be utilized for summary sales data. Third, the other data region should be utilized for the detailed sales data for each sales representative. Four, a report that displays the detailed sales data should be designed for Jack.
• B. First, two reports should be designed for Simon. Second, the territory information should be contained by one, and the detailed sales data should be contained by another. At last, a report that displays the detailed sales data for Jack's territories should be designed for Jack.
• C. First, all territory information should be contained by a parameterized report which is designed for Simon. Second, a linked report that links to Simons report should be designed for Jack.
• D. First, the territory information should be contained by a report which is designed for Simon. Second, a report which detailed sales data for Jack's territories is contained should be designed for Jack. Third, Simon??s report should be set to have the data in Jacks report contained.

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005.
You work a database administrator for your company. There is a Data Transformation Services (DTS) package, and the DTS package is stored in SQL Server 2000. And you plans to have the SQL Server 2000 migrated into SQL Server 2005. The DTS package dynamically configures precedence constraints by utilizing complex Microsoft Visual Basic Scripting Edition (VBScript). The SQL Server 2005 Package Migration Wizard is run by you, but the wizard cannot convert the package from SQL Server 2000 to SQL Server 2005.
You are required to make sure that the DTS package runs in SQL Server 2005. And the package should be run in the shortest time. Which action should be utilized?

• A. The SQL Server 2000 Data Transformation Services (DTS) task should be configured to not utilize precedence constraints. Then, the SQL Server 2005 Package Migration Wizard should be run again.
• B. The SQL Server 2000 Data Transformation Services (DTS) package should be wrapped in an Execute DTS 2000 Package task.
• C. The SQL Server 2005 Package Migration Wizard should be run. And when the upgrade fails, a Transfer SQL Server Objects task should be run.
• D. The SQL Server 2000 Data Transformation Services (DTS) package should be manually recreated as a SQL Server Integration Services (SSIS) package.

You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions, monitoring the database server, and design deployment. In the company, you major job is the deployment, maintenance and support of Microsoft SQL Server 2005.You work as a database administrator for your company. A SQL Server 2005 database is utilized in your company. And a table named Inventory is contained by the database. A column named Price is included by the table. According to the company policy, you cannot reduce more than 10 percent the value in the Price column in any single database operation. Through many kinds of method, the company makes updates to the Price column. For instance, the utilization of ad hoc queries is one of the methods. Since you are the database administrator, you should confirm that this company policy takes its effect.which action should be performed?

• A. On the Price column, a check constraint should be created, and a specified minimum value is required by the check constraint.
• B. A stored procedure should be created, and changes are disallowed by the procedure to the Price column that violates company policy.
• C. A trigger should be created, and changes are rolled back by the trigger to the Price column that violates company policy.
• D. On the Price column, a foreign key constraint is created to a table that valid prices are included.

A most proper archiving strategy for the SalesData.Orders table in the Sales database should be identified. The archiving strategy should be implemented with the least amount of effect on performance. Which action should be performed?

• A. First, a table named ArchivedData.Orders should be created. Second, an INSERT statement will be utilized to copy data from the SalesData.Orders table into ArchivedData.Orders every three months. At last a DELETE statement should be utilized to remove archived data from SalesData.Orders.
• B. First, a table named ArchivedData.Orders should be created. Second, the SalesData.Orders table and its indexes should be partitioned. At last, MERGE, SPLIT, and SWITCH functions should be utilized every three months to remove old order data into ArchivedData.Orders.
• C. First, a database snapshot of the Sales database should be created every three months, and then a DELETE statement should be utilized to remove archived data from the SalesData.Orders table.
• D. First, the Sales database should be backed up to tape every three months, and then a DELETE statement should be utilized to migrate archived data from the SalesData.Orders table.