Microsoft 70-442 Real Exam Questions

PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005

1: You need to optimize the query against the ReservationRequests table to fulfill the technical requirements. What should you do?
A.Add the READPAST locking hint to the query.
B.Add the READUNCOMMITTED locking hint to the query.
C.Add the UPDLOCK locking hint to the query.
D.Set the transaction isolation level to SNAPSHOT.
E.Set the transaction isolation level to REPEATABLE READ.
Correct Answers: A

2: You need to design the data access strategy for the Tailspin Toys online store search requirement. Which strategy should you use?
A.System.Xml.XmlReader
B.System.Data.SqlClient.SqlDataReader
C.System.Data.DataSet
D.CLR user-defined type
Correct Answers: C

3: You are troubleshooting concurrency problems with the code that was originally developed on the SQL Server 2000 database. You discover that there are excessive read locks on important tables. You need to resolve the problem. What should you do?
A.Set the transaction isolation level to REPEATABLE READ.
B.Set the transaction isolation level to SERIALIZABLE.
C.Set the ALLOW_SNAPSHOT_ISOLATION database option to ON.
D.Set the READ_COMMITTED_SNAPSHOT database option to ON.
Correct Answers: D

4: You need to design the user interface that will display availability to pick up items at a retail store for items that are ordered online. You also must minimize the number of connections to the database. Which data access strategy should you use?
A.ActiveX data objects (ADO)
B.multiple active result sets (MARS)
C.System.Data.DataReader
D.System.Data.DataSet
Correct Answers: B

5: You design the following stored procedure that will be used by sales managers.
CREATE PROCEDURE StoreSales (@StartDate datetime, @EndDate datetime)
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SELECT StoreID, TotalSales
FROM Sales
WHERE SaleDate BETWEEN @StartDate AND @EndDate 
While tuning the query, you notice that table locks are occurring. You need to resolve the problem. What should you do?
A.Change the isolation level to READ COMMITTED.
B.Add a HOLDLOCK table hint to the query.
C.Add a NOWAIT table hint to the query.
D.Add a READPAST table hint to the query.
Correct Answers: A

6: You are designing the stored procedure that updates inventory quantities. The stored procedure contains the following code.
CREATE PROCEDURE UpdateInventory (@ProductID int, @Amount int)
AS
BEGIN TRANSACTION
DECLARE @CurrentQuantity int
SELECT @CurrentQuantity = Quantity
    FROM Inventory.ProductInventory
    WHERE ProductID = @ProductID
UPDATE Inventory.ProductInventory
        SET Product.Quantity = @CurrentQuantity + @Amount
WHERE ProductID = @ProductID
IF @@ERROR <> 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN
You discover that the ProductInventory column is occasionally updated incorrectly. You need to resolve the problem. What should you do?
A.Add a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement to the stored procedure.
B.Remove the SELECT statement from the stored procedure. Perform the update using the Product.Quantity column instead of the @CurrentQuantity variable.
C.Add a NOLOCK hint to the SELECT statement.
D.Move the BEGIN TRANSACTION statement so that it immediately precedes the UPDATE statement.
Correct Answers: B

7: You are designing a stored procedure that records a sale and decreases the inventory for the items that are being purchased. The stored procedure contains the following code.
CREATE PROCEDURE UpdateSalesAndInventory (@ProductID int, @quantity int)
AS
SET IMPLICIT_TRANSACTION ON
INSERT INTO Sales.SalesOrder Values(@ProductID, @quantity)
UPDATE Inventory.ProductInventory
    SET Quantity = Quantity – @quantity
    WHERE ProductID = @ProductID
You run this stored procedure and discover that no updates are applied. You need to resolve the problem. What should you do?
A.Change SET IMPLICIT_TRANSACTION to OFF.
B.Use multiple active result sets (MARS) to execute the stored procedure.
C.Add logic to commit or roll back the transaction based on the successful updates of the data.
D.Add a BEGIN TRANSACTION statement to the stored procedure.
Correct Answers: C

8: You need to improve the performance and reduce the database resource usage of the stored procedure that will be used to generate the monthly agent-commission report. What should you do?
A.Remove the INSENSITIVE argument from the cursor declaration.
B.Add the OPTIMISTIC argument to the cursor declaration.
C.Rewrite the stored procedure using set operations.
D.Rewrite the stored procedure as a user-defined type.
Correct Answers: C

9: You need to design the data access object model to be used for the infrequently changing data about trading partners. What should you use?
A.ActiveX data objects (ADO)
B.System.Data.DataReader
C.System.Data.DataSet
D.System.Xml.XmlReader
Correct Answers: C

10: You need to design the data access method for accessing the SQL Server 2005 computer. You must ensure that your solution supports the Web site and the smart client application. Which data access technology should you use?
A.SqlXmlCommand
B.updategrams
C.XML template queries
D.HTTP endpoints
Correct Answers: D

Download  |  Password: certificatexam.com