Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 31:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  The database contains a table named Claims. The structure of the Claims table is as shown in the following table.

  

  Only two percent of the claims are open at any point in time.

  You discover that queries on claims that have an Open status take a long time to execute.

  You need to optimize the performance of the claim-processing queries.

  What should you do?

  A. Use a partitioning function to partition the Claims table on the open_date column.
  B. Create a view for the Claims table by using a WHERE clause to include all rows that have a NULL value in the close_date column.
  C. Create an index for the Claims table by using a WHERE clause to include all rows that have a NULL value in the close_date column.
  D. Create a table-valued function for the Claims table by using a WHERE clause to include all rows that have a NULL value in the close_date column.
  C. Create an index for the Claims table by using a WHERE clause to include all rows that have a NULL value in the close_date column.

• Question 32:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  A stored procedure uses the INSERT, UPDATE, and DELETE statements separately to load data into a table. You need to rewrite the stored procedure to use a single statement to load the data.

  What should you do?

  A. Write a MERGE statement by using a WHEN MATCHED clause and a WHEN NOT MATCHED BY TARGET clause.
  B. Write a MERGE statement by using a WHEN MATCHED clause and a WHEN NOT MATCHED BY SOURCE clause.
  C. Write a MERGE statement by using a WHEN MATCHED clause, a WHEN NOT MATCHED BY TARGET clause, and a WHEN NOT MATCHED BY SOURCE clause.
  D. Write a MERGE statement by using a WHEN MATCHED clause and two WHEN NOT MATCHED BY SOURCE clauses.
  C. Write a MERGE statement by using a WHEN MATCHED clause, a WHEN NOT MATCHED BY TARGET clause, and a WHEN NOT MATCHED BY SOURCE clause.

• Question 33:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  Your company operates a fulfillment warehouse. The company has the following business requirements:

  You plan to design a Transact-SQL user-defined function. The function must perform the following tasks:

  You need to design the function to meet these requirements. You want to achieve this goal by using the minimum amount of coding effort.

  What should you do?

  A. Create a multistatement table-valued function that accepts an XML parameter.
  B. Create a multistatement table-valued function that accepts a table-valued parameter.
  C. Create an inline table-valued function that accepts an XML parameter.
  D. Create an inline table-valued function that accepts a table-valued parameter.
  B. Create a multistatement table-valued function that accepts a table-valued parameter.

• Question 34:

  You have a table in a database that contains 30 million rows. You need to ensure that the table meets the following requirements: What should you do?

  A. Create a filtered index.
  B. Create a clustered index.
  C. Configure Service Broker.
  D. Configure a Full-Text-Search.
  D. Configure a Full-Text-Search.

• Question 35:

  You have an execution plan as shown in the exhibit. (Click the Exhibit button.)

  

  The indexes and keys for the table are as follows:

  You need to recommend changes to the table to minimize the response time for both queries.

  What should you do?

  A. Include all the columns of the table in the existing nonclustered index.
  B. change the order of the columns in the existing nonclustered index.
  C. Add a unique nonclustered index to Col1 and Col2.
  D. Remove the nonclustered index.
  C. Add a unique nonclustered index to Col1 and Col2.

• Question 36:

  You are designing a database that contains a data definition language (DDL) trigger. The DDL trigger will provide the maximum amount of data available when any attempt is made to change the database schema.

  You need to design a table to meet the following requirements:

  Accept the EVENTDATA information that is provided by the trigger Support the searching and retrieval of nodes and values Minimize development time

  Which data type should you use?

  A. nvarchar(max)
  B. varchar(max)
  C. varbinary
  D. XML
  D. XML

• Question 37:

  You need to develop a stored procedure strategy that will manipulate temporary result sets to meet the following requirements:

  The result sets will contain less than 1,000 rows.

  What should you use?

  A. a common table expression (CTE)
  B. a view
  C. a user-defined table type
  D. a scalar function
  C. a user-defined table type

• Question 38:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. The database supports a Web site and captures user interactions. These interactions are stored in the Activity table of the User_Activity database. Data older than six months is archived to the Activity table of the Archive_Activity database on a different instance of SQL Server 2008.

  The structure of the Activity table is as shown in the following table.

  

  You plan to design a solution that allows a single query to generate a report that summarizes user interactions for the last 12 months. You need to ensure that the solution is implemented.

  Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

  A. Create a partition function and a partition scheme.
  B. Modify the Activity tables to use the partition scheme.
  C. Move the archived data back to the User_Activity database.
  D. Create a view by using the UNION ALL clause to retrieve data from the two Activity tables.
  E. Create CHECK constraints on the two Activity tables to limit the values in the activity_date column to an exclusive range.
  D. Create a view by using the UNION ALL clause to retrieve data from the two Activity tables.
  E. Create CHECK constraints on the two Activity tables to limit the values in the activity_date column to an exclusive range.

• Question 39:

  You have a database that has 20 large tables. All the tables have qualified indexes. As the tables grow, you discover that queries that contain JOIN statements execute more slowly. You need to recommend a solution to decrease the query response time and the I/O. The solution must minimize hardware costs.

  What should you recommend?

  A. Implement query hints.
  B. Create a filegroup strategy.
  C. Implement an index strategy.
  D. Create a partitioning strategy.
  D. Create a partitioning strategy.

• Question 40:

  Your company has the following development policy for XML data:

  You need to recommend guidelines for generating well-formed XML result sets.

  What should you recommend?

  A. FOR XML AUTO
  B. FOR XML PATH
  C. OPENXML ( )
  D. XQUERY
  B. FOR XML PATH