Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 51:

  You are designing a database for a reporting solution that is based on data from an Online Transaction Processing (OLTP) database. The reports will contain aggregated data. You need to ensure that the reports will not affect query performance on the OLTP database. The solution must minimize the use of joins when performing the aggregate calculations.

  What should you do?

  A. Add a persisted computed column.
  B. Create indexed views in the OLTP database.
  C. Create partitioned tables in the OLTP database.
  D. Create a new denormalized database based on the OLTP database.
  D. Create a new denormalized database based on the OLTP database.

• Question 52:

  You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment.

  You plan to create a stored procedure that queries a sales table and produces forecast data.

  You do not have administrative permissions, and you are not the owner of the database. You have permissions to create stored procedures. Users will only have permissions to execute your stored procedures.

  You need to ensure that users can execute the stored procedures.

  What should you do?

  A. Set the TRUSTWORTHY property of the database to ON.
  B. Include an EXECUTE AS OWNER clause when you create each stored procedure.
  C. Include an EXECUTE AS CALLER clause when you create each stored procedure.
  D. Include a SETUSER statement before you query the sales table in each stored procedure.
  B. Include an EXECUTE AS OWNER clause when you create each stored procedure.

• Question 53:

  You need to create an application that will represent the relationship between managers and employees.

  You must achieve this goal by using the minimum amount of tables.

  What should you do?

  A. Create one table that contains the hierarchyid data type.
  B. Create one table that contains the uniqueidentifer data type.
  C. Create two tables. Establish a foreign key relationship between the tables.
  D. Create two tables. Create a trigger that maintains the relationship between the two tables.
  A. Create one table that contains the hierarchyid data type.

• Question 54:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a large table that is infrequently updated. Users execute a query against the table. The query requires the execution of

  a complex calculation that involves multiple columns for a given row. You discover that the query performance is poor because the query is CPU intensive.

  You need to reduce the effect of this query on the server.

  What should you do?

  A. Create a computed column on the table.
  B. Create a persisted computed column on the table.
  C. Create an index on each field used by the calculation.
  D. Create a view on the table that includes the calculation.
  B. Create a persisted computed column on the table.

• Question 55:

  You need to design a stored procedure that contains a transaction. If an error condition causes the transaction to roll back, a temporary result set must be available to the query that follows the failed transaction. Which temporary storage object should you use?

  A. a temporary table
  B. a derived table
  C. a table variableD a common table expression (CTE)
  C. a table variableD a common table expression (CTE)

• Question 56:

  You have a line-of-business application.

  The application generates reports based on data from database views. Users report that it takes a long time to generate reports.

  You run a SQL Server Profiler trace and discover that the application is generating many blocking processes.

  You need to minimize the number of blocking processes. You must achieve this goal without modifying the statements that the application sends to the server.

  What should you do?

  A. Change the isolation level of the database.
  B. Implement a database Service Broker.
  C. Create new database report views.
  D. Change the isolation level of each query.
  A. Change the isolation level of the database.

• Question 57:

  You review the execution plan for a query that contains a join. The execution plan contains a RID Lookup.

  You need to reduce the I/O that is generated by the RID Lookup.

  Which index should you create?

  A. unique nonclustered
  B. clustered
  C. nonunique covering
  D. Full-Text-Search
  B. clustered

• Question 58:

  You have a table named Table1. A sample of the data in Table1 is shown in the following table.

  

  There is a defined nonclustered index on the SalesOrderNumber column.

  The following query executes against the table.

  WHERE `SO3' = LEFT (SalesOrderNumber,3)

  You need to minimize the amount page I/O that is generated when the query runs.

  What should you do?

  A. Use a query hint.
  B. Add a non-filtered index.
  C. Rewrite the WHERE clause to use a LIKE statement.
  D. Rewrite the WHERE clause to use a substring function on the SalesOrderNumber column.
  C. Rewrite the WHERE clause to use a LIKE statement.

• Question 59:

  You are a database developer. You create a database that uses SQL Server 2008 in an enterprise environment.

  You plan to import data from an external source into a table. You need to ensure that the following tasks are accomplished:

  What should you do?

  A. Use CHECK constraints.
  B. Use an AFTER trigger.
  C. Use an INSTEAD OF trigger.
  D. Disable the foreign keys during the import process.
  C. Use an INSTEAD OF trigger.

• Question 60:

  You plan to create a Service Broker solution. The solution will transport data from one queue to another queue.

  You need to identify which message type must be used to transport binary data. The solution must minimize the amount of data transported.

  Which message type should you use?

  A. EMPTY
  B. NONE
  C. VALID_XML WITH SCHEMA COLLECTION
  D. WELL_FORMED_XML
  B. NONE