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
Correct Answer: E
You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and
SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)
You write the following Transact-SQL query:
You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?
A. Use a FORCESCAN hint in the query.
B. Add a clustered index on SalesOrderId in SalesOrderHeader. C. Use a FORCESEEK hint in the query.
D. Update statistics on SalesOrderId on both tables.
Correct Answer: D
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())) Correct Answer: D Explanation/Reference: Two answers will return the correct results (the "WHERE CONVERT..." and "WHERE ... AND ... " answers). The correct answer for Microsoft would be the answer that is most "efficient". Anybody have a clue as to which is most efficient? In the execution plan, the one that I've selected as the correct answer is the query with the shortest duration. Also, the query answer with "WHERE CONVERT..." threw warnings in the execution plan...something about affecting CardinalityEstimate and SeekPlan. I also found this article, which leads me to believe that I have the correct answer: http://technet.microsoft.com/en-us/library/ms181034.aspx QUESTION 4 You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. What should you do? A. Use the CAST function. B. Use the DATE data type. C. Use the FORMAT function. D. Use an appropriate collation. E. Use a user-defined table type. F. Use the VARBINARY data type. G. Use the DATETIME data type. H. Use the DATETIME2 data type. I. Use the DATETIMEOFFSET data type. J. Use the TODATETIMEOFFSET function. Correct Answer: F Explanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/ms188362.aspx QUESTION 5 Correct Answer: C Explanation/Reference: Reference: http://msdn.microsoft.com/en-us/library/hh213505.aspx Download Ensurepass Latest 2013 MCSA 70-461 Real Exam Questions , help you to pass exam 100%.