Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 111:

  You plan to deploy a new application. The application will perform the following operations:

  You need to configure a login to support the deployment of the new application. The solution must ensure that the application uses the most restrictive permissions possible.

  What should you do?

  A. Add the login to the sysadmin server role.
  B. Add the login to the diskadmin and secuntyadmin server roles. After the database is created, add a user to the db_backupoperator database role.
  C. Add the login to the dbcreator server role.
  D. Add the login to the diskadmin and serveradmin server roles. After the database is created, add a user to the db_backupoperator database role.
  C. Add the login to the dbcreator server role.

• Question 112:

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

  The database contains two stored procedures named ModifySales and RetrieveSalesTrend. The ModifySales stored procedure uses a transaction that updates a table named SalesOrders. The RetrieveSalesTrend stored procedure retrieves

  and aggregates data from the SalesOrders table for a

  sales trend analysis report.

  Both stored procedures are executed frequently each day. Users report a considerable wait time while they run the sales trend analysis report. You need to ensure that sales trend analysis report runs as quickly as possible.

  What should you do?

  A. Change the isolation level to SERIALIZABLE for ModifySales.
  B. Change the isolation level to READ UNCOMITTED for ModifySales.
  C. Add the NOWAIT hint to the SELECT statement in RetrieveSalesTrend.
  D. Add the NOLOCK hint to the SELECT statements in RetrieveSalesTrend.
  D. Add the NOLOCK hint to the SELECT statements in RetrieveSalesTrend.

• Question 113:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. A database will contain 10 tables that are used to generate reports. Data in the tables ranges from 50,000 to 100,000 records.

  During a query execution that joins four tables, you discover the following problems:

  You need to identify the most likely cause for the problems by analyzing the execution plan.

  What should you do?

  A. Look for table scans in the execution plan.
  B. Look for Merge Join operators in the execution plan.
  C. Look for Hash Match operators in the execution plan.
  D. Look for Nested Loops operators in the execution plan.
  C. Look for Hash Match operators in the execution plan.

• Question 114:

  You plan to implement a Web-based application that will save XML data to a column in a table.

  You need to design a query that ensures that before saving the XML data to the table, the data contains valid elements. The solution must be developed by using the minimum amount of effort.

  What should you include in the query?

  A. .exist()
  B. .query()
  C. FOR XML PATH
  D. sp_xml_preparedocument
  A. .exist()

• Question 115:

  You have a SQL Server Integration Services (SSIS) package that contains an Execute Process task. You need to schedule the SSIS package to run on a regular basis. What should you do?

  A. Create a credential and a login. Configure a SQL Server Agent job to run the package by using the login.
  B. Create a credential and a proxy. Configure a SQL Server Agent job to run the package by using the proxy.
  C. Create a login and map a user to the login. Add the user to the db_owner role. Configure a SQL Server Agent job to run the package by using the login.
  D. Create a login and map the user to a login. Add the user to the db_securityadmin role. Configure a SQL Server Agent job to run the package by using the login.
  B. Create a credential and a proxy. Configure a SQL Server Agent job to run the package by using the proxy.

• Question 116:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain information on retail sales transactions of more than 500 stores. The marketing department uses the solution to analyze daily sales patterns for each store. Users report that the solution takes a long time to retrieve the required data. You need to ensure that the solution provides results in the minimum possible time.

  What should you do?

  A. Create a nonclustered index on a view of the sales transactions.
  B. Create a covering index on a view that aggregates the sales transactions.
  C. Create a clustered index on a view that aggregates the sales transactions.
  D. Create a nonclustered index on a view that aggregates the sales transactions.
  C. Create a clustered index on a view that aggregates the sales transactions.

• Question 117:

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

  The database contains a table that has two partitions. The first partition contains the current data, and the second partition contains historical data. There is a high frequency of data manipulation on the current data.

  The testers report that they are frequently unsuccessful in querying the historical data only. You discover that locks are being escalated to the table-level lock.

  You need to ensure that without modifying the SELECT statements, you enable users to query the historical data. You want to achieve this goal by using the minimum amount of administrative effort and minimally affecting other queries.

  What should you do?

  A. Use the ALTER TABLE ... SET (LOCK_ESCALATION = AUTO) command.
  B. Move the historical partition to a read-only filegroup.
  C. Set the Server Startup Parameters to include -T1211.
  D. Set DEADLOCK PRIORITY to HIGH.
  D. Set DEADLOCK PRIORITY to HIGH.

• Question 118:

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

  The database has a table named Sales.Inventory. The table is partitioned into four geographic regions.

  You update the Sales.Inventory table for each region by using the following stored procedure.

  CREATE STORED PROCEDURE usp_Update

  @RegionID tinyint

  AS

  UPDATE Sales.Inventory

  SET Qty = T.CurrentQuantity

  FROM Sales.Inventory I

  JOIN Sales.TempData T ON I.ItemID = T.ItemID

  AND I.RegionID = @RegionID;

  The UPDATE statement locks the Sales.Inventory table when a single region is updated. You need to prevent the locking of the Sales.Inventory table when a single region is updated.

  What should you do?

  A. Modify the usp_Update stored procedure to use the NOLOCK table hint for the UPDATE statement.
  B. Modify the usp_Update stored procedure to use the SERIALIZABLE table hint for the UPDATE statement.
  C. Run the following Transact-SQL statement.ALTER TABLE Sales.Inventory SET LOCK_ESCALATION = AUTO
  D. Run the following Transact-SQL statement.ALTER TABLE Sales.Inventory SET LOCK_ESCALATION = TABLE
  C. Run the following Transact-SQL statement.ALTER TABLE Sales.Inventory SET LOCK_ESCALATION = AUTO

• Question 119:

  You have a table that has an XML column named XMLOrderHeader. You need to design a stored procedure that extracts the order header values and stores them in a table.

  The solution must meet the following requirements:

  What should the solution include?

  A. Use a single Exists() method.
  B. Use a single XPATH statement.
  C. For each value, use the Exists() method.
  D. For each value, use an XPATH statement.
  D. For each value, use an XPATH statement.

• Question 120:

  You need to create a Service Broker solution. Which object should you create first?

  A. Contract
  B. Dialog
  C. Message Type
  D. Services
  C. Message Type