Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 1

You need to design the data access strategy for the Tailspin Toys online store search requirement. Which strategy should you use?

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?

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?

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?

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?

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?

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?

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?

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?

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?