Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 41:

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

  The database contains a table that is stored as a heap. You perform page-level compression on the table.

  When data is added to the table by using BULK INSERT, the new pages are not compressed.

  You need to ensure page-level compression for all data pages when they are allocated to the table.

  Which query hint should you use?

  A. PAG LOCK
  B. NOLOCK
  C. NOWAIT
  D. TABLOCK
  C. NOWAIT

• Question 42:

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

  Your online transaction processing (OLTP) database contains a tabled named SalesOrders. Your data warehouse contains a table named factBuyingHabits. The factBuyingHabits table has no indexes.

  You need to synchronize data between the two tables on a weekly basis. The synchronization process has the following requirements:

  You need to design an appropriate synchronization solution. You want to achieve this goal by using minimum amount of coding and administrative efforts.

  What should you do?

  A. Design an SSIS package each for the INSERT, UPDATE, and DELETE operations. Schedule a job to run this package.
  B. Design a single SSIS package that uses the Slowly Changing Dimension task. Schedule a job to run this package.
  C. Write one stored procedure that contains a MERGE statement to perform the INSERT, UPDATE, andDELETE operations. Schedule a job to run the stored procedure.
  D. Write three stored procedures each for the INSERT, UPDATE, and DELETE operations. Schedule a job to run the stored procedures in a sequential manner.
  C. Write one stored procedure that contains a MERGE statement to perform the INSERT, UPDATE, andDELETE operations. Schedule a job to run the stored procedure.

• Question 43:

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

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

  

  The solution uses the following stored procedure to return customer details.

  

  Users report that the stored procedure takes a long time to execute. You need to optimize the performance of the stored procedure.

  What should you do?

  A. Recreate the primary key as a nonclustered unique index and build a clustered index on the country_id column.
  B. Build a nonclustered index on the country_id column and use the INCLUDE clause to include the customer_name column.
  C. Build a nonclustered index on the country_id, customer_name, and customer_id columns.
  D. Build a nonclustered index only on the country_id column.
  B. Build a nonclustered index on the country_id column and use the INCLUDE clause to include the customer_name column.

• Question 44:

  You have a server that runs SQL Server 2008 Standard. The server provides data storage for a third- party contact management application.

  A service-level agreement for the application does not allow for modifications to be made to the application or to the database objects used by the application. You need to recommend a strategy to mitigate performance issues. The strategy

  must minimize database growth.

  What should you recommend?

  A. a scalable shared database
  B. clustered indexes
  C. plan guides
  D. partitioned tables
  C. plan guides

• Question 45:

  You need to recommend a solution that will enable two or more application sessions to share the same transaction and locks. The solution must enable the applications to work on the same data without creating lock conflicts.

  What should you recommend?

  A. a stoplist
  B. bound sessions
  C. nested transactions
  D. savepoints
  B. bound sessions

• Question 46:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. You create a stored procedure that uses the TRY/CATCH syntax in a new database. When the stored procedure is executed, it logs

  information about each step in the TRY block into a table named dbo.ExecutionLog.

  When an error occurs, the stored procedure must perform the following tasks:

  What should you do?

  A. 1.Start a transaction in the TRY block.2. After each step, insert log entries into the dbo.ExecutionLog table.3. In the CATCH block, commit the transaction.4. After the CATCH block, use data in the dbo.ExecutionLog table to reverse any changes made to the target tables.5. Commit the transaction if one exists.
  B. 1. Start a transaction in the TRY block.2. Before each step, define a transactional save point.3. After each step, insert log entries into the dbo.ExecutionLog table.4. In the CATCH block, roll back to the transactional save points.5. After the CATCH block, commit the transaction.
  C. 1. Define a temporary table before the TRY block by using the same columns as that of the dbo.ExecutionLog table.2. Start a transaction in the TRY block.3. After each step, insert log entries into the temporary table.4. In the CATCH block, roll back the transaction.5. After the CATCH block, insert the rows from the temporary table into the dbo.ExecutionLog table.6. Commit the transaction if one exists.
  D. 1. Define a table variable before the TRY block by using the same columns as that of thedbo.ExecutionLog table.2. Start a transaction in the TRY block.3. After each step, insert log entries into the table variable.4. In the CATCH block, roll back the transaction.5. After the CATCH block, insert the rows from the table variable into the dbo.ExecutionLog table.6. Commit the transaction if one exists.
  D. 1. Define a table variable before the TRY block by using the same columns as that of thedbo.ExecutionLog table.2. Start a transaction in the TRY block.3. After each step, insert log entries into the table variable.4. In the CATCH block, roll back the transaction.5. After the CATCH block, insert the rows from the table variable into the dbo.ExecutionLog table.6. Commit the transaction if one exists.

• Question 47:

  You have a table that contains 10 million rows.

  You need to design a query that returns a 100,000-row sample dataset. The solution must minimize the query's execution time.

  The sample may contain inconsistent data.

  Which isolation level should you set to ON?

  A. READ COMMITTED SNAPSHOT
  B. READ COMMITTED
  C. REPEATABLE READ
  D. READ UNCOMMITTED
  D. READ UNCOMMITTED

• Question 48:

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

  Your company has offices in Europe, Asia, North America, and Africa. The company data is updated for each region after business hours.

  The queries used by reports on the database are blocked when the data is being updated. Occasionally, the data is updated simultaneously for the Africa and Europe regions.

  You need to ensure maximum concurrency for the database by using minimum possible system resources.

  Which isolation level should you use?

  A. READ COMMITTED
  B. REPEATABLE READ
  C. SNAPSHOT
  D. SERIALIZABLE
  A. READ COMMITTED

• Question 49:

  You need to configure a security solution for an application. The solution must meet the following requirements: What should you create?

  A. a database user that has no login
  B. a new login that has a blank password
  C. an application role
  D. a proxy object
  A. a database user that has no login

• Question 50:

  You have a legacy application. You do not have access to the application source code. The application has a large denormalized table that contains 100 columns. The application uses stored procedures and views to perform all data

  manipulation language (DML) activities on the table.

  You need to optimize the performance of the application to meet the following requirement:

  What should you do?

  A. Create nonclustered indexes on all columns in the table.
  B. Create new stored procedures that use the existing views.
  C. Create new views. Perform DML activities against the views.
  D. Create smaller tables. Update the views and stored procedures.
  D. Create smaller tables. Update the views and stored procedures.