Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 21:

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

  The database includes a table that contains the following product inventory information:

  You plan to write a query that produces the sum of quantity data broken into the following groups:

  You need to write the query by using the minimum possible number of Transact-SQL statements.

  What should you recommend?

  A. Write a single query that contains a GROUP BY clause.
  B. Write a single query that contains a GROUP BY WITH CUBE clause.
  C. Write a single query that contains a GROUP BY WITH ROLLUP clause.
  D. Write a single query that contains a GROUP BY GROUPING SETS clause.
  D. Write a single query that contains a GROUP BY GROUPING SETS clause.

• Question 22:

  You are a database solutions architect. Your company plans to develop a solution by using a SQL Server 2008 instance.

  The solution has the following business requirements:

  Import data from various data sources such as Microsoft Office Excel, Microsoft SQL Server 2000,Microsoft SQL Server 2005, and CSV files.

  Profile the source data before it is imported.

  Provide collaboration and offline capabilities to mobile users.

  Allow mobile users to use heterogeneous data stores.

  You need to configure the appropriate SQL Server components to accomplish the business requirements.

  You want to achieve this goal by using the minimum amount of administrative effort. Which two SQL Server components should you use? (Each correct answer presents part of the solution. Choose two.)

  A. Analysis Services
  B. Reporting Services
  C. Integration Services
  D. Notification Services
  E. Microsoft Sync Framework
  C. Integration Services
  E. Microsoft Sync Framework

• Question 23:

  Users frequently update millions of rows in a table at a time. Users report that it takes a long time to update the rows. You need to recommend a solution to reduce the time it takes to update the rows. The solution must be developed in the minimum amount of time.

  What should you do?

  A. Use a table variable.
  B. Use a temporary table.
  C. Split the update operation into small batches.
  D. Use the NOLOCK optimizer hint and use a single transaction.
  C. Split the update operation into small batches.

• Question 24:

  You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. An application contains two stored procedures. The tasks performed by the stored procedures are as shown in the following table.

  

  You discover that the procedures occasionally throw foreign key violation errors. IncludeDetails throws an error when it inserts records into the ProductInfo table. ImportNewProducts throws an error when it inserts records into the ProductCurrentPrice table.

  If an error occurs in the INSERT statement of ProductInfo, records inserted into ProductHeader and ProductCurrentPrice are committed. If an error occurs in the INSERT statement of ProductCurrentPrice, all transactions are rolled back.

  What should you do?

  A. 1. Add a SET XACT_ABORT OFF statement in IncludeDetails.2. Add a SET XACT_ABORT ON statement in ImportNewProducts.
  B. 1. Add a SET XACT_ABORT ON statement in IncludeDetails.2. Add a SET XACT_ABORT OFF statement in ImportNewProducts.
  C. 1. Enclose all statements of IncludeDetails in a TRY/CATCH block.2. Add a ROLLBACK TRANSACTION statement in the CATCH block.
  D. 1. Enclose all statements of ImportNewProducts in a TRY/CATCH block.2. Add a ROLLBACK TRANSACTION statement in the CATCH block.
  A. 1. Add a SET XACT_ABORT OFF statement in IncludeDetails.2. Add a SET XACT_ABORT ON statement in ImportNewProducts.

• Question 25:

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

  The database will contain a table that has a parent-child relationship to itself. Each child might also be a parent. This might exist up to 10 levels deep.

  You need to retrieve all levels by using a single Transact-SQL query.

  What should you do?

  A. Write a query to return the first level, and then add a correlated subquery to get the remaining levels.
  B. Write a query to return the first level, and then use the CROSS JOIN operator to join the table back to itself to get the remaining levels.
  C. Create a common-table expression to return the first level and then union back to itself to get the remaining levels.
  D. Create a view that returns the first level, and then use the FULL OUTER JOIN operator to join the table back to the view to get the remaining levels.
  C. Create a common-table expression to return the first level and then union back to itself to get the remaining levels.

• Question 26:

  You have a database that contains two tables.

  Both the XACT_ABORT database option and the IMPLICIT_TRANSACTIONS database option are set to OFF.

  You need to update both tables. If an update fails on either table, neither table should be updated.

  What should you do?

  A. Use a transaction.
  B. Change the isolation level.
  C. Use the TABLOCK query hint.
  D. Use the UPDLOCK query hint.
  A. Use a transaction.

• Question 27:

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

  A database contains a table named Employee_Vacation.

  You are given an updated list of employee vacations used. The list is in an XML formatted file. The extract of the XML format is written in the following manner.

  You plan to import the data and update the Employee_Vacation table. You design a query to calculate the number of vacation days used by each employee.

  You need to ensure that vacation days are accurately counted for each employee.

  What should you do?

  A. Use an XQuery expression along with the LET clause and the count function. Return the count in XML format.
  B. Use an XML index. Aggregate the number of vacation days for each employee, and then return the total count in XML format.
  C. Use the OPENXML function to convert XML data into a standard table format. Execute the Transact-SQL count function on the vacation days, and then return the count in XML format.
  D. Use an XQuery expression to write the information from XML format to a SQL Server table. Aggregate the number of vacation days from the tables, and then return the count in XML format.
  A. Use an XQuery expression along with the LET clause and the count function. Return the count in XML format.

• Question 28:

  You are designing a database that will store telephone numbers. You need to ensure that only phone numbers that use a specific format are written to the database. What should you create?

  A. a CHECK constraint
  B. a computed column
  C. a DEFAULT constraint
  D. a persisted computed column
  A. a CHECK constraint

• Question 29:

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

  You are writing a query that returns XML data from a table in a database.

  You need to ensure that your result set is encapsulated within a root element.

  What should you do?

  A. Use the RAW mode along with an ELEMENTS directive.
  B. Use the PATH mode along with a ROOT directive.
  C. Use the EXPLICIT mode along with a TYPE directive.
  D. Use the AUTO mode along with a ROOT directive.
  B. Use the PATH mode along with a ROOT directive.

• Question 30:

  You have an office in Tokyo and an office in New York. Each office contains a server that runs SQL Server 2008 Enterprise. The New York office contains a central database that is mirrored to the Tokyo office for high- availability.

  You need to recommend a solution that enables users in the Tokyo office to generate reports locally. The solution must minimize WAN utilization.

  What should you recommend?

  A. Implement a failover cluster.
  B. Create distributed partitioned views.
  C. Create database snapshots.
  D. Implement snapshot replication.
  C. Create database snapshots.