READ Free Dumps For Microsoft- 70-461
Question ID 15098 | You generate a daily report according to the following query: You need to improve the performance of the query. |
Option A | Drop the UDF and rewrite the report query as follows: WITH cte(CustomerID, LastOrderDate) AS ( SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate] FROM Sales.SalesOrder GROUP BY CustomerID ) SELECT c.CustomerName FROM cte INNER JOIN Sales.Customer c ON cte.CustomerID = c.CustomerID WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE()) |
Option B | Drop the UDF and rewrite the report query as follows: SELECT c.CustomerName FROM Sales.Customer c WHERE NOT EXISTS ( SELECT s.OrderDate FROM Sales.SalesOrder s WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE()) AND s.CustomerID = c.CustomerID) |
Option C | Drop the UDF and rewrite the report query as follows: SELECT DISTINCT c.CustomerName FROM Sales.Customer c INNER JOIN Sales.SalesOrder s ON c.CustomerID = s.CustomerID WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE()) |
Option D | Rewrite the report query as follows: SELECT c.CustomerName FROM Sales.Customer c WHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c.CustomerID, 90)) Rewrite the UDF as follows: CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime) RETURNS TABLE AS RETURN ( SELECT OrderDate FROM Sales.SalesOrder s WHERE s.CustomerID = @CustomerID AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE()) |
Correct Answer | A |
Question ID 15099 | You develop a Microsoft SQL Server 2012 server database that supports an application. |
Option A | ALTER TABLE Inventory ADD TotalItems AS ItemslnStore + ItemsInWarehouse |
Option B | ALTER TABLE Inventory ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED |
Option C | ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED |
Option D | ALTER TABLE Inventory ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) |
Correct Answer | B |