Examen 70-461 V001


#1. 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?

Explanation: 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:


#2. Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a self-reference foreign key constraint?

#3. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. You need to improve the performance of the view by persisting data to disk. What should you do?



#4. The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?




#5. You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: CREATE TABLE Inventory (ItemID int NOT NULL PRIMARY KEY, ItemsInStore int NOT NULL, ItemsInWarehouse int NOT NULL) You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. Which Transact-SQL statement should you use?



Deja una respuesta

WP to LinkedIn Auto Publish Powered By : XYZScripts.com