70-461

You generate a daily report according to the following query: You need to improve the performance of the query. What should you do?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 )...

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format: <row OrderId="1" OrderDate="2000-01-01T00:00:00"...

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.) Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record...

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions: Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert...

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers...

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 ensure that users are able to modify data by using the view. What should you do?A . Create an AFTER trigger on the view.B...

CORRECT TEXT You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.) You have the following query: You need to recreate the query to meet the following requirements: • Reference columns by using one-part names only. • Sort aggregates by SalesTerritoryID, and then...

You are developing a database that will contain price information. You need to store the prices that include a fixed precision and a scale of six digits. Which data type should you use?A . FloatB . MoneyC . SmallmoneyD . DecimalView AnswerAnswer: D Explanation: Decimal is the only one in...

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical. 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 . HOLDLOCKB . ROWLOCKC...

CORRECT TEXT You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.) You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements: • Use one-part names to reference columns....