Examen 70-461 V001

16 octubre, 201818 octubre, 2018 JuanCastillo 0 comentarios

Username *

User Password *

User Email *

Confirm Password *

Results

#1. 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 that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use?

EXECUTE AS CALLER

USE Database2

EXECUTE AS OWNER

USE Database1

Explanation:

http://msdn.microsoft.com/en-us/library/ms188354.aspx http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-callingstoredprocedure-on-linked-server/

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

HOLDLOCK

TABLOCK

ROWLOCK

UPDLOCK

XLOCK

Explanation:

http://technet.microsoft.com/en-us/library/ms189857.aspx

http://msdn.microsoft.com/en-us/library/ms187373.aspx

#3. You develop a Microsoft SQL Server 2012 database. You need to create and call a stored procedure that meets the following requirements: Accepts a single input parameter for CustomerID. Returns a single integer to the calling application. Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)

DECLARE @CustomerRatingByCustomer INT DECLARE @Result INT EXECUTE @Result = dbo.GetCustomerRating 1745, @CustomerRatingByCustomer

CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN @Result GO

CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT, @CustomerRating INT OUTPUT AS SET NOCOUNT ON SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURN GO

CREATE PROCEDURE dbo.GetCustomerRating @CustomerID INT AS DECLARE @Result INT SET NOCOUNT ON SELECT @Result = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID RETURNS @Result GO

DECLARE @CustomerRatingByCustomer INT EXECUTE dbo.GetCustomerRating @CustomerID = 1745, @CustomerRating = @CustomerRatingByCustomer OUTPUT

EXECUTE dbo.GetCustomerRating 1745

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

Update statistics on SalesOrderId on both tables

Use a FORCESCAN hint in the query

Add a clustered index on SalesOrderId in SalesOrderHeader.

Use a FORCESEEK hint in the query.

Explanation:

http://msdn.microsoft.com/en-us/library/ms187348.aspx

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

SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())

SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()

SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)

SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

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:

http://technet.microsoft.com/en-us/library/ms181034.aspx