Instantly download 070-432 training test engine

Microsoft 070-432 Exam Overview:

Microsoft 070-432 Exam Syllabus Topics:

Microsoft TS:MS SQL Server 2008,Implementation and Maintenance Sample Questions:

Question 1

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems table. The table has the following definition:

Currently, the table is partitioned by year with each year in its own filegroup.
You need to move the data from the oldest year into a new table in a different Schema to archive the data.
What should you do?

A. Create a new Filegroup.
Create a new database File.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use the ALTER PARTITION FUNCTION statement along with the SPLIT RANGE clause.
B. Use the ALTER PARTITION FUNCTION ... SPLIT RANGE statement.
C. Create a new table.
Use the ALTER TABLE statement along with the SWITCH PARTITION clause.
Use the ALTER PARTITION FUNCTION statement along with the MERGE RANGE
clause.
D. Run the following statement: EXECUTE sp_tableoption @TableNamePattern ='OrderItem3', @OptionName= 'PartltionByYear'; @OptionValue= 'true';
E. Create a new partition function.
Create a new partition scheme.
Add a clustered index to place the data onto the partition scheme.
F. Execute the DBCC CLEANTABLEcommand on the OrderItems table.
G. Run the following statement:
CREATE PARTITION SCHEME SEC_FG
AS PARTITION FUNC_FG
ALL TO (SECONDARY);
H. Remove the clustered index from the table.
I. Use the ALTER TABLEstatement to remove the COLLATEoption.
J. Create a new filegroup.
Create a new database file.
Use the ALTER PARTITION SCHEME statement along with the NEXT USED clause.
Use ALTER INDEX REORGANIZE statement.

Question 2

---
You administer a SQL Server 2008 instance that contains a very large database named FinanceDB.
You plan to create a maintenance plan that meets the following objectives for the FinanceDB database:
It executes the DBCC CHECKDB statement.
It rebuilds all the indexes.
It updates all index statistics.
You need to ensure that the maintenance plan is executed in the minimum amount of time.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A. Use the Update Statistics task.
B. Use the Check Database Integrity task.
C. Use the Reorganize Index task.
D. Use the Rebuild index task.
E. Use the Shrink Database task.

Question 3

You are mastering the company database. During the development, you find that Transact-SQL query below is running slowly:SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510' A clustered index exists on the VideoTitle column.
There is a nonclustered index on the ReleaseDate column which contains the UpcNum and RetailPrice columns. The result of the avg_fragmentation_in_percent is 30 percent, when you search the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table,In order to find this method to solve this problem, which is the correct answer?

A. You should Re-create the index on only the ReleaseDate column.
B. You should alter the query hint to force the optimizer to force a unclustered index seek.
C. You should delete the query hint from the query.
D. You should reproduce the whole indexes on the VideoTitle table.

Question 4

You administer a SQL Server 2008 instance. The instance contains databases for a finance application and a manufacturing application. You configure the Resource Governor to limit the maximum CPU query time allowed by the applications. The administrator of the manufacturing application reports that certain reports no longer execute successfully. You need to modify the Resource Governor configuration to allow the manufacturing application to consume more CPU time.
Which Resource Governor component should you configure?

A. The workload group that is used by default
B. The workload group that is used by the manufacturing application
C. The workload group that is used by the finance application
D. The classifier function that identifies the finance application

Question 5

You maintain a SQL Server 2008 instance that contains a database named Finance. A small percentage of data in the database is modified daily.
You need to create a read-only copy of the database for reporting purposes at the beginning of each day by ensuring that minimal disk space is used.
Which strategy should you use?

A. Use database backup and restore along with compression.
B. Use database backup and restore.
C. Use schema-bound views in a read-only database that resides on the same instance.
D. Use database snapshots.

Solutions: