Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 71:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. The database has a table named Sales. The Sales table contains 10 million rows.

  You discover that the following query takes a long time to execute.

  SELECT s.sale_id,...

  FROM Sales AS s

  JOIN Country AS c

  ON s.Country_id = c.Country_id

  AND c.Country_name = 'USA'

  A summary of the execution plan is as shown in the following code segment. |--Hash Match(Inner Join,

  HASH:

  ([s].[Country_id]) = ([c].[Country_id]) |--Clustered Index Scan(OBJECT:([Country].

  [PK_Country_Country_id] AS

  [c]) |--Clustered Index Scan(OBJECT:([Sales].[PK_Sales_Sale_id] AS [s]))

  You need to ensure that the query retrieves data in minimum possible time.

  What should you do?

  A. Modify the query to use a loop join hint.
  B. Modify the query to use a merge join hint.
  C. Create a nonclustered index in the Country_id column of the Sales table.
  D. Create a nonclustered index in the Country_name column of the Country table.
  C. Create a nonclustered index in the Country_id column of the Sales table.

• Question 72:

  You are designing a database that will be used for reporting purposes. You need to minimize the data storage requirements and improve the application response time. What should you recommend?

  A. row compression
  B. sparse columns
  C. table partitioning
  D. XML
  A. row compression

• Question 73:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a view that has the following features: What should you do?

  A. Create an INSTEAD OF trigger on the view.
  B. Create a unique clustered index on the view.
  C. Alter the view by adding the WITH CHECK OPTION clause.
  D. Alter the view by adding the WITH SCHEMABINDING clause.
  C. Alter the view by adding the WITH CHECK OPTION clause.

• Question 74:

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

  The database contains a table named Products.

  The database has two stored procedures named ModifyProduct and RetrieveProducts. ModifyProduct updates a single row in the Products table. RetrieveProducts returns all rows from the Products table.

  RetrieveProducts is used by a report. Users who run the report experience contention problems. You discover that RetrieveProducts is being blocked by ModifyProduct.

  The report must not include rows that are currently being modified.

  You need to ensure that the report is executed as quickly as possible.

  Which locking hint should you use in RetrieveProducts?

  A. NOLOCK
  B. NOWAIT
  C. READPAST
  D. READUNCOMMITTED
  C. READPAST

• Question 75:

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

  You have a Web site supported by a database that has the full-text search component installed. You plan to create a table named Courses that will have the following structure.

  

  Users of the Web site will search for courses based on the CourseTitle field. You need to construct a full-text query that ensures the following compliances when a user launches the search for a course: What should you specify in the full-text query?

  A. A FREETEXT predicate
  B. A CONTAINS predicate
  C. A FREETEXTTABLE function
  D. A CONTAINSTABLE function
  D. A CONTAINSTABLE function

• Question 76:

  You are a database developer. You develop a database application for a SQL Server 2008 instance.

  

  The instance hosts a third-party database. You are not allowed to modify the database schema. The database contains two tables that are as shown in the following diagram.

  You plan to extract address information about full-time employees based on the FullTimeIndicator flag.

  You need to design a data access layer to simplify the extraction process.

  What should you do?

  A. Design an Entity Data Model that contains the EMPLOYEES and ADDRESS entities.
  B. Create a view on the database to include full-time employees and their address details.
  C. Re-design the underlying database model to include employee and address information in one table.
  D. Design a conceptual Entity Data Model that contains an entity named EMPLOYEE_ADDRESS. Ensure that this entity contains information about employees and their addresses.
  D. Design a conceptual Entity Data Model that contains an entity named EMPLOYEE_ADDRESS. Ensure that this entity contains information about employees and their addresses.

• Question 77:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. You plan to design a complex multi-statement stored procedure in the following manner.

  CREATE PROCEDURE Sales.GetCustomerActivity @StartDate datetime AS SELECT order_id, order_date, customer_id FROM Sales.Orders WHERE order_date >= @StartDate ...

  On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute.

  You discover that this degradation is caused by the first statement in the stored procedure.

  You need to ensure that the stored procedure is consistently executed in the minimum possible time.

  What should you do?

  A. Run the EXEC sp_recompile GetCustomerActivity command.
  B. Create a plan guide to apply the OPTION (RECOMPILE) clause to the first statement.
  C. Modify the stored procedure by adding the WITH RECOMPILE clause.
  D. Replace the first statement in the stored procedure with the following Transact-SQL statement.UPDATE STATISTICS Sales.GetCustomerActivity WITH RESAMPLE
  B. Create a plan guide to apply the OPTION (RECOMPILE) clause to the first statement.

• Question 78:

  You need to design a solution to guarantee that a stored procedure is part of every transaction. What should you do?

  A. Enable implicit transactions.
  B. Enable Distributed Transaction Coordinator (DTC).
  C. Set the NOCOUNT option to ON.D Set the XACT_ABORT option to ON.
  A. Enable implicit transactions.

• Question 79:

  Your company is developing an application. The database platform for the application will be vendor-independent.

  You need to ensure that the application automatically stores the current date and time data when a record is inserted or updated, regardless of the database platform.

  What should you do?

  A. Use Entity Framework and DateTime.Now.
  B. Create a database column that has a default value of GETDATEQ.
  C. Create an INSERT trigger that uses the GETDATEQ function.
  D. Use the datetime2 data type.
  A. Use Entity Framework and DateTime.Now.

• Question 80:

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

  The database will contain a view. The view will contain a computed column that will be indexed.

  The computed column will use a user-defined scalar function. The function will perform complex mathematical manipulations using random generated float numeric values.

  You need to identify the appropriate function to use to generate random values.

  Which function should you use?

  A. a deterministic Transact-SQL table-valued function
  B. a nondeterministic common language runtime (CLR) scalar-valued function
  C. a deterministic common language runtime (CLR) scalar-valued function
  D. a nondeterministic Transact-SQL table-valued function to generate random values
  C. a deterministic common language runtime (CLR) scalar-valued function