Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 181:

  You develop a Microsoft SQL Server 2012 database.

  You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each. Performs aggregations on 5 fields.

  The view is frequently used in several reports. You need to improve the performance of the reports.

  What should you do?

  A. Convert the view into a table-valued function.
  B. Convert the view into a Common Table Expression (CTE).
  C. Convert the view into an indexed view.
  D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.
  C. Convert the view into an indexed view.

• Question 182:

  Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column.

  The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.

  Which Transact-SQL query should you use?

  A. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())
  B. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()
  C. SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
  D. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
  D. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

• Question 183:

  You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. You write the following Transact-SQL query:

  INSERT INTO OrdersHistorical

  SELECT * FROM CompletedOrders

  You need to optimize transaction logging and locking for the statement.

  Which table hint should you use?

  A. HOLDLOCK
  B. ROWLOCK
  C. XLOCK
  D. UPDLOCK
  E. TABLOCK
  E. TABLOCK