70-442 Exam

Free 70-442 Demo Download

pass999.info offers free demo for Microsoft Licensing 70-442 exam (PRO:Design & Optimize Data Access by Using MS SQL Serv 2005). You can check out the interface, question quality and usability of our practice exams before you decide to buy it. We are the only one site can offer demo for almost all products.

Download 70-442 Exam Testing Engine

70-442 Exam Description

It is well known that latest 70-442 exam test is the hot exam of Microsoft certification. pass999.info offer you all the Q&A of the 70-442 real test . It is the examination of the perfect combination and it will help you pass 70-442 exam at the first time!

　
　
Exam : Microsoft 70-442
Title : PRO:Design & Optimize Data Access by Using MS SQL Serv 2005


1. 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.
Answer: A

2. BACKGROUND
company overview
Tailspin toys is a regional toy store chain,with stores in 30 cities.
Planned Changes
The company wants to add an online store to its chain of retail stores.A prototype of the online store was developed by using SQL server 2000 .However,the IT manager wants to move to SQL Sever 2005 to take advantage of its new features.
The IT manager plans to automate certain database maintenance operations,including scripting of database objects,backup and restore activities ,and creation and maintenance of endpoints.
Problem Statement
The nightly processing of the sales data from each store executes very slowly.with the addition of the online store,this performance must be improved in order to complete the processing within the allotted frame.
EXISTING ENVIRONMENT
Existing Application Environment
The prototype of the online store is experiencing poor concurrency.
Existing supporting Infrastructure
Currently,the point of sale(POS) system for the retail stores has a SQL Server 2000 database.Tailspin Toys has an Enterprise Resource planning(ERP) system running on UNIX that must be able to communicate with the online store's Inventory database.
BUSINESS REQUIREMENTS
General Application Solution Requirements
The company is developing an online store that is capable of displaying pictures,prices,and information on products that are sold by Tailspin Toys.
Customers must be able to find out whether toys that are available online are also available at a physical store for pickup.The store inventory for a selected store must be queried real-time as the individual toys are displayed.Inventory quantities for a toy must all be displayed prior to processing the next toy.
Store managers often run reports that query for all sales for a given date range.
New toys are introduced at the online store only once per month.
Performance
When a customer uses the online store to perform a search for toys,it should return results in 15 seconds or less.
Users can search for a toy category. The results of this search should be used for future searches by the same user,to minimize round trips to the database.
TECHNICAL REQUIREMENTS
Resource usage on the database server must be minimized.
The online store will have two databases named Sales and Inventory.When a sale is completed,both databases must be kept consistent with each other.
The IT department has specified that the online store will use a service-oriented architecture for data access.
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
Answer: C

3. 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
Answer: B

4. 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
Answer: C

5. 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.
Answer: D