70-442 Microsoft Licensing Braindump ExamSoon 70-442 Exams Microsoft PRO:Design & Optimize Data Access by Using MS SQL Serv 2005 O rder : 70-442 Exam Practice Exam: 70-442 Exam Number/Code: 70-442 Exam Name: PRO:Design & Optimize Data Access by Using MS SQL Serv 2005 Questions and Answers: 110 Q&As Free 70-442 Braindumps Exam : Microsoft 70-442 Title : PRO:Design & Optimize Data Access by Using MS SQL Serv 2005 1. You are a database developer for your company. An XML document is stored in the XML variable @Article. The XML document in the variable can contain hundreds of article elements. The XML document is shown in the following segment. <articles> <article> <title>...</title> <paragraph>...</paragraph> <image filename="..."> <description>...</description> </image> <paragraph>...</paragraph> <image filename="..."> <description>...</description> </image> <paragraph>...</paragraph> <paragraph>...</paragraph> </article> ... </articles> You need to design a query that will return the filename and description for all images from the XML document in the following tabular format. Which query should you use? A. SELECT@Article.value('(/articles/article/image)[1]/@filename', 'NVARCHAR(50)') AS Filename,@Article.value('(/articles/article/image/description)[1]', 'NVARCHAR(max)') AS Description B. SELECT col.value('(articles/article/image)[1]/@filename', 'NVARCHAR(50)') AS Filename ,col.value('articles/article/image/description[1]', 'NVARCHAR(max)') AS DescriptionFROM @Article.nodes('/') AS x (col); C. SELECT col.value('@filename', 'NVARCHAR(50)') AS Filename ,col.value('description[1]', 'NVARCHAR(max)') AS DescriptionFROM @Article.nodes('/articles/article/image') AS x (col) ; D. SELECT col.query('<filename>{@filename}</filename>') AS Filename ,col.query('description[1]') AS DescriptionFROM @Article.nodes('/articles/article/image') AS x (col) ; Answer: C 2. BACKGROUND Company Overview Margie's Travel is a small regional travel agency that handles many different types of travel arrangements. Planned Change Margie's Travel has purchased a COM component named Cruisex to be used to access cruise lines information. Margie's Travel hosts the component in COM+.Cruisex will be used for two purposes:to retrieve a read-only list of available cruises,and to make reservations on cruises.Cruisex uses all default settings. Problem Statements Many of the company's in-house travel agents that the Web site that is used for making travel reservations is too difficult to use. The company wants to create a smart client application that will deliver a richer client experience for the agents. The solution must support the Web site application for off-site travel agents. A monthly agent-commission report has been created. This report will be executed once per month.This report currently uses a cursor with the INSENSITIVE argument.The test team reports that this report runs slowly and consumes a significant amount of database server resources. A stored procedure named UpdateSeatSelection is used to update seat selections for airline reservations. The test team reports that there is significant blocking when this stored procedure is being executed. EXISTING ENVIRONMENT Existing Supporing Infrastructure The current travel agent application stores its data in a SQL Server 2005 database. BUSINESS REQUIREMENTS General Application Solution Requirements The duplication of application logic must be minimized. After an airline reservation is made,the aqent must help the customer with seat selection. The seat selection process should not block the application from performing other reservation tasks.However,the agent must have the ability to cancel a seat selection query prior to its completion.The application must receive a reply that cancellatio was successful. A report named FlightsBooked is being developed.The test team reports that executing the report twice within a single transaction using the same parameters occasionally produces different results. However,subsequnt executions with those same parameters produce consistent results. A feature must be developed that will allow travel agents to seach for customers by name of partial name.The data that is returned must not include any data that is not yet committed. Performance To improve performance for the data access layer,the number of round trips to the database must be limited,particularly for data that changes only infrequently. Infrequent changes are made to the information about trading partners such as airlines,cruise lines,and hotel chains.Margie's Travel wants to limit the bandwidth that is required to ensure that the smart client application has the current values for trading partners .The company wants to mininize the development effort that is needed to support this requirement. When an agent queries for available fight information,the data must be returned as quickly as possible.This data will not be modified by the agent. A monthly agent-commission report has been created.This report will be executed once per month.This report currently uses a cursor with the INSENSITIVE argument.The test team reports that this report runs slowly and consumes a significant amount of database server reslurces. Recoverability Once per month,all sales data must be transferred to the Accounting database,which is on the same database server instance.In the case of failure,recovery of the travel agent database and the Accounting database must be ensured to the same logical point in time. TECHNICAL REQUIREMENTS Security External access to company data must be allowed for only a specified set of IP addresses. 9. 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. Answer: C 3. 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. Answer: B 4. 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 5. 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 Answer: D 6. 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 7. You are a database developer for your company. The company is not using the Full-Text Search feature of SQL Server 2005. You are constructing a query to search product descriptions from the Products table, which is shown in the following display. Your query must filter the results by selecting only those rows that contain a specific keyword in their element content. This keyword is available in a variable named @keyword. Which code segment should you use? A. SELECT Description FROM Product WHERE CAST(Description AS nvarchar(max))LIKE N'%' + @keyword + N'%' B. SELECT Description FROM Product WHERE CONTAINS(Description,@keyword) C. SELECT Description FROM Product WHERE Description.exist('/Description[contains(., sql:variable ("@keyword"))]') = 1 D. SELECT Description FROM Product WHERE PATINDEX(@keyword, CAST(Description as nvarchar(max)) > 0 Answer: 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. Answer: C 9. You are a database developer for your company. You are designing a query that calculates the number of orders that were made on October 27, 2005. The Orders table is designed as shown in the following display. An index is defined on the OrderDate column. You need to design the query to provide the fastest performance. Which Transact-SQL query should you use? A. SELECT COUNT(*) FROM OrdersWHERE OrderDate >= '20051027' AND OrderDate < '20051028' B. SELECT COUNT(*) FROM OrdersWHERE YEAR(OrderDate) = 2005 AND MONTH(OrderDate) = 10 AND DAY (OrderDate) = 27 C. SELECT COUNT(*) FROM OrdersWHERE CONVERT(CHAR(8), OrderDate, 112) = '20051027' D. SELECT COUNT(*) FROM OrdersWHERE OrderDate BETWEEN '2005-10-26T00:00:00' AND '2005-10- 28T00:00:00' E. SELECT COUNT(*) FROM OrdersWHERE DATEDIFF(day, OrderDate, '20051027') = 0 Answer: A 10. You need to design the data access model for available flight information. What should you use? A. XML template queries B. ActiveX data objects (ADO) C. System.Data.DataSet D. System.Data.DataReader Answer: D 11. 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 Answer: C 12. 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 13. You are attempting to correct the FlightsBooked report. The report contains the following code segment. CREATE PROCEDURE FlightBookings (@AsOfTime datetime) AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT BookingID, AgentFullName,FlightID, FlightDate FROM FlightBookings JOIN Agent ON FlightBookings.AgentID = Agent.AgentID WHERE BookingTime <= @AsOfTime You need to correct the problem. What should you do? A. Set the transaction isolation level to READ COMMITTED. B. Set the transaction isolation level to REPEATABLE READ. C. Set the READ_COMMITTED_SNAPSHOT database option to ON. D. Set the READ_COMMITTED_SNAPSHOT database option to OFF. Answer: B 14. 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 15. You need to design the transfer of sales data to the Accounting database. You are designing the stored procedure that will be used to transfer the data. You must ensure that all requirements are fulfilled. Which Transact-SQL statement should you use first? A. BEGIN TRANSACTION Acct WITH MARK 'Accounting' B. BEGIN DISTRIBUTED TRANSACTION Acct C. SET IMPLICIT_TRANSACTIONS ON D. SAVE TRANSACTION Acct Answer: A 16. You are a database developer for your company. You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure. This cursor must have a fixed membership where the order of the rows does not change. The data in the rows that are retrieved through the cursor should be updatable. These updates should be viewable after they are made. While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor. You need to create the stored procedure to fulfill these requirements. Which option should you use in the DECLARE CURSOR statement? A. DYNAMIC B. FAST_FORWARD C. KEYSET D. STATIC Answer: C 17. You are a database developer for your company. A table named Customers contains customer information and is designed as shown in the following display. Some queries need to search the table and filter on a combination of the Name and Address columns. These queries require the comparison to use the collation Finnish_Swedish_CI_AS. You need to optimize these queries for the shortest response times while minimizing the impact on other queries. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) A. Add two new columns named NameFinSweCIAS and AddressFinSweCIAS that use the collation Finnish_Swedish_CI_AS and use triggers to populate them. B. Add two computed columns named NameFinSweCIAS and AddressFinSweCIAS that use the collate operator on the Name and Address columns to change their collation to Finnish_Swedish_CI_AS. C. Create a nonclustered index over the column NameFinSweCIAS. D. Create a nonclustered index over the column AddressFinSweCIAS. E. Create a nonclustered index over the columns NameFinSweCIAS and AddressFinSweCIAS. Answer: BE 18. 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 19. 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. Answer: C 20. You are a database developer for your company. You are developing a sales report for a given time period. The report must list all sales representatives and their position based on their total sales for the period. If two sales representatives had the same total sales, they should be assigned the same position. A sample output is shown in the following display. You need to create the report to fulfill the requirements. Which operator should you use? A. RANK B. DENSE_RANK C. NTILE D. ROW_NUMBER Answer: A More 70-442 Braindumps Information Exam Description 1. ExamSoon offer free update service for three month. After you purchase our product, we will offer free update in time for three month. 2. High quality and Value for the 70-442 Exam. ExamSoon Practice Exams for 70-442 are written to the highest standards of technical accuracy, provided by our certified subject matter experts and published authors for development. 3. 100% Guarantee to Pass Your Microsoft Licensing exam and get your Microsoft Licensing Certification. We guarantee your success in the first attempt. If you do not pass the Microsoft Licensing "70-442" (PRO:Design & Optimize Data Access by Using MS SQL Serv 2005 on your first attempt, send us the official result. We will give you a FULLY REFUND of your purchasing fee and send you another same value product for free. 4. ExamSoon Microsoft Licensing 70-442 Exam Downloadable. Our PDF or Testing Engine Preparation Material of Microsoft Licensing 70-442 exam provides everything which you need to pass your exam. The Microsoft Licensing Certification details are researched and produced by our Professional Certification Experts who are constantly using industry experience to produce precise, and logical. You may get "70-442 exam" questions from different websites or books, but logic is the key. Our Product will help you not only pass in the first PRO:Design & Optimize Data Access by Using MS SQL Serv 2005( Microsoft Licensing ) exam try, but also save your valuable time. Comprehensive questions with complete details about 70-442 exam. 70-442 exam questions accompanied by exhibits. Verified Answers Researched by Industry Experts and almost 100% correct. Drag and Drop questions as experienced in the Real Microsoft Licensing exam. 70-442 exam questions updated on regular basis. Like actual Microsoft Licensing Certification exams, 70-442 exam preparation is in multiple-choice questions (MCQs). Tested by many real Microsoft Licensing exams before publishing. Try free Microsoft Licensing exam demo before you decide to buy it in http://www.ExamSoon.com High quality and Valued for the 70-442 Exam: 100% Guarantee to Pass Your 70-442 exam and get your Microsoft Licensing Certification. Come to http://www.ExamSoon.com The easiest and quickest way to get your Microsoft Licensing Certification. ExamSoon professional provides Microsoft Licensing 70-442 the newest Q&A, completely covers 70-442 test original topic. With our completed Microsoft Licensing resources, you will minimize your Microsoft Licensing cost and be ready to pass your 70-442 test on Your First Try, 100% Money Back Guarantee included! 70-442 Exam Study Guide 70-442 exam is regarded as one of the most favourite Microsoft Licensing Certifications. Many IT professionals prefer to add 70-442 exam among their credentials. ExamSoon not only caters you all the information regarding the 70-442 exam but also provides you the excellent 70-442 study guide which makes the certification exam easy for you. ExamSoon Engine Features Comprehensive questions and answers about 70-442 exam 70-442 exam questions accompanied by exhibits Verified Answers Researched by Industry Experts and almost 100% correct 70-442 exam questions updated on regular basis Same type as the certification exams, 70-442 exam preparation is in multiple-choice questions (MCQs). Tested by multiple times before publishing Try free 70-442 exam demo before you decide to buy it in ExamSoon.com ExamSoon Help You Pass Any IT Exam ExamSoon.com offers incredib le career enhancing opportunities. We are a team of IT professionals that focus on providing our customers with the most up to date material for any IT certification exam. This material is so effective that we Guarantee you will pass the exam or your money back. 70-236 TS:Configuring Exchenge Server 2007 70-237 pro:Designing Messaging Solutions with MS Exchange Serv 2007 70-542 MS Office SharePoint Server 2007.Application Development 70-542CSharp MS Office SharePoint Server 2007-Application Development Related 70-442 Exams 70-630 TS:Configuring Microsoft Office SharePoint Server 2007 70-622 Pro:Microsoft Desktop Support-Enterprise 70-622Big5 Pro:Microsoft Desktop Support-Enterprise 70-621 Pro:Upgrading Your MCDST Certo MCITP Enterprise Support 70-624 TS:Deploying&Maintaining Vista&Client Office System 07 DT 70-540 TS:Microsoft Windows Mobile Application Development 70-122 Designing and Providing Microsoft Volume Licensing Solutions to Large Organizations 70-123 Planning, Implementing and Maintaining a Software Asset Management (SAM) Program 70-552 UPGRADE:MCAD Skills to MCPD Wdows Dvlpr by Using MS.NET Fmwk 75-003 *INTL ONLY* MS EMPLOYEE:APPLICATIONS EXAM APPLEMAC V6/7 70-446 PRO:Design a Business Intellig Infrastr Usi MS SQL Serv2005 70-999 MS 100 DEMINSTRATION EXAM 70-500 TS:MS Windows MObile Desiging.Implementing.and Managing 70-623 Pro:Microsoft Desktop Support Consument 70-121 Designing and Providing Microsoft Volume Licensing Solutions for Small and Medium Organizations 70-442 PRO:Design & Optimize Data Access by Using MS SQL Serv 2005 Other Microsoft Exams MB3-006 74-135 MB6-203 70-567 70-241 70-351 70-653 MB4-535 70-505 MB2-421 70- 536ChineseVB 70-671 70-620 70-219 MB3-180 70-285 70-503 70-216 MB7-638 70-563