(
70-442)
PRO: Design and Optimize Data Access by Using Microsoft SQL Server 2005Microsoft 70-442 Exam - Certifysky.com
Free 70-442 Sample Questions:
1. 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 realtime 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 serviceoriented 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 userdefined type
Answer: 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.
Answer: 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
Answer: 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.
Answer: 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.
Answer: 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.
Answer: C
8. 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 readonly list of available cruises,and to make reservations on cruises.Cruisex uses all default settings. Problem Statements Many of the company's inhouse 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 offsite travel agents. A monthly agentcommission 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 agentcommission 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 agentcommission 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 userdefined type.
Answer: C
10. 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
11.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
12. 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
13. 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
14. 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 TransactSQL 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
15. 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.
A. RANK
B. DENSE_RANK
C. NTILE
D. ROW_NUMBER
Answer: A
16. 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.
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 '20051026T00:00:00' AND '20051028T00:00:00'
E. SELECT COUNT(*) FROM OrdersWHERE DATEDIFF(day, OrderDate, '20051027') = 0
Answer: A
17. You are a database developer for your company. The company is not using the FullText 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.
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
18. You are a database developer for your company. You are creating a stored procedure that will use a TransactSQL 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
19. 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. ... ... ... ... ... ... ... 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.
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}') AS Filename ,col.query('description[1]') AS DescriptionFROM
@Article.nodes('/articles/article/image') AS x (col) ;
Answer: C
20. You are a database developer for your company. A table named Customers contains customer information and is designed as shown in the following display.
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: B, E
21. You are a database developer for your company. You are responsible for combining two existing SQL Server 2000 databases named DB1 and DB2 into a single database named NewDB on the companys new SQL Server 2005 computer. The following query functions properly from the old application that was used to access the DB1 database. SELECT * FROM Customer WHERE FaxNumber = NULL However, no rows are returned when the query is run on the new SQL Server 2005 computer, even though NULL values exist in the FaxNumber column. You need to resolve the problem so that the query will function properly on the SQL Server 2005 computer. You need to ensure that your solution does not affect the behavior of the tables and queries from DB2. What should you do?
A. Instead of = NULL use the ISNULL function.
B. Change = NULL to IS NULL.
C. Change = NULL to = 'NULL'.
D. Add the following command before the query: SET ANSI_NULLS ON
E. Use the ALTER DATABASE command to change the ANSI_NULLS database option to ON.
Answer: B
22. You are a database developer for your company. Your database contains a table named Inventory, which is shown in the following display.
A. COMPUTE BY
B. CUBE
C. ROLLUP
D. GROUP BY
Answer: B, D
23. You are a database developer for your company. Users want information on orders that have the highest values in the TotalDue column. Users want to be able to provide a different percentage of records to be returned each time they request the information. You need to build the code on SQL Server 2005 that will allow the frontend application to pass a variable that the end user will provide for the percentage of records to be returned. How should you write the code?
A. CREATE PROCEDURE TopPercentTotalDue@P intASSELECT TOP(@P) PERCENT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue DESC
B. CREATE PROCEDURE TopPercentTotalDue@P intASSELECT TOP(@P) PERCENT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue ASC
C. CREATE PROCEDURE TopPercentTotalDue@R intASSET rowcount @RSELECT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue DESC
D. CREATE PROCEDURE TopPercentTotalDue@R intASSET rowcount @RSELECT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue ASC
Answer: A
24. You are a database developer for your company. You are developing a stored procedure that will identify which online customers are also members of the companys loyalty program. This data is stored in two tables named Customer and Loyalty. You need to develop the stored procedure to fulfill the requirements. Which operator should you use?
A. EXCEPT
B. LEFT OUTER JOIN
C. INTERSECT
D. CROSS APPLY
Answer: C
25. You are a database developer for your company. You are developing a stored procedure that must accept a parameter of the xml data type and store the data in a relational table structure. You need to write the stored procedure to fulfill the requirements. Which functionality should you use?
A. FOR XML AUTO
B. the nodes method of the xml data type
C. the query method of the xml data type
D. the modify method of the xml data type
Answer: B
26. You are a database developer for your company. In your database, the Employees table contains approximately 1,000 rows, and the Orders table contains millions of rows. The Employees table is designed as shown in the following display.
A. SELECT e.Firstname, e.Lastname, COUNT(*) FROM Employees AS eLEFT OUTER JOIN Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.Firstname, e.Lastname
B. SELECT e.Firstname, e.Lastname, COUNT(*) FROM Employees AS eLEFT OUTER JOIN Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.EmployeeID, e.Firstname, e.Lastname
C. SELECT e.Firstname, e.Lastname, COUNT(o.OrderID) FROM Employees AS eLEFT OUTER JOIN Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.EmployeeID, e.Firstname, e.Lastname
D. SELECT e.Firstname, e.Lastname, COUNT(o.OrderID) FROM Employees AS eLEFT OUTER JOIN Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.Firstname, e.Lastname
Answer: C
27. You are a database developer for your company. A routine that processes orders for the city of Stockholm must be optimized for better performance. The current routine is shown in the following code segment. ... DECLARE OrderCursor CURSOR FOR SELECT OrderID, City, CustomerID FROM Orders; OPEN OrderCursor; FETCH NEXT FROM OrderCursor INTO @OrderID,
@CustomerID, @City; WHILE(@@FETCH_STATUS = 0) BEGIN IF(@City <> 'Stockholm') GOTO Next; Next: FETCH NEXT FROM OrderCursor INTO @OrderID, @CustomerID, @City; END ... You need to optimize the routine for the greatest performance. What should you do?
A. Change the cursor to be declared as DYNAMIC.
B. Change the cursor to be declared as KEYSET.
C. Change the cursor to be declared as STATIC.
D. Change the SELECT statement in the cursor to include WHERE City = 'Stockholm'. E.Change the IF statement to check for @City = 'Stockholm' to eliminate the need for the GOTO statement.
Answer: D
28. You are a database developer for your company. You are designing a query for a database that contains tables named Employees and Orders. The relevant columns in these tables are shown in the following display.
A. SELECT e.EmployeeID, e.Name, COUNT(*) FROM dbo.Employees AS eLEFT OUTER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.EmployeeID, e.Name
B. SELECT e.EmployeeID, e.Name, COUNT(o.OrderID) FROM dbo.Employees AS eLEFT OUTER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.EmployeeID, e.Name
C. SELECT e.Name, COUNT(*) FROM dbo.Employees AS eINNER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.Name
D. SELECT e.Name, COUNT(o.OrderID) FROM dbo.Employees AS eLEFT OUTER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.Name
Answer: B
29. You are a database developer for your company. You are writing a query that will search a resume text column for specified skills. The results must include both synonyms and exact matches. You need to write the query to fulfill the requirements. Which TransactSQL function should you use?
A. SOUNDEX
B. CHARINDEX
C. CONTAINS
D. PATINDEX
Answer: C
More Free 070-442 exam questions
70-442 Exam Prep
Learn what you need to know to pass the 70-442 exam easily
Guarantee your 70-442 success with our 70-442 Exam Resources. Our exams are developed by experiences IT Professionals working in today's prospering companies and date centers. All our practice exams including 70-442 exam guarantee you the exam success you need.
CertifySky offers free demo for 70-442 exam. 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. If you want to try 70-442 Exam Prep Demo, Click the "Try Demo" button.
70-442 Certification Exam and Study Guide
There are many online resources for preparing for the 70-442 exam. Read below to discover why Certifysky.com is your premier source for practice tests, and true testing environment.
CertifySky is the online Certification Expert recognized by a worldwide audience of IT professionals and executives alike as the definitive source of training materials for the candidate seeking insight, updates and resources for vendor certifications.
70-442 Practice Test Questions, Printable PDF Braindumps
We are all well aware that a major problem in the IT industry is that there is a lack of quality study materials. Our 70-442 Preparation Exam Material provides you everything you will need to take a certification examination. Details are researched and produced by Certification Experts who are constantly using industry experience to produce precise, logical and verified explanations for the answers. You may get questions from different web sites or books, but logic is the key.
Comprehensive questions with complete details
Questions accompanied by exhibits (when applicable)
Verified Answers Researched by Industry Experts
Drag and Drop questions as experienced in the Actual Exams (when applicable)
Questions updated on regular basis
These questions and answers are backed by our GUARANTEE
Like actual certification exams our product is in multiple-choice questions (MCQs) |
Our
70-442 Exam will provide you with exam questions and verified answers that reflect the actual exam. These questions and answers provide you with the experience of taking the actual test. Our 70-442 Exam is not just questions and answers. They are your access to high technical expertise and accelerated learning capacity. Certification Experts, Certified Computer Trainers, Technical Coworker and Comprehensive Language Masters, who have a solid, verified and certified background and high technical expertise, have compiled these detailed questions and answers.
70-442 Certification preparation Q and A provided by
Certifysky.com will make you feel like you are taking an actual exam at a Prometric or VUE center.
Furthermore, we are constantly updating our
70-442 Exam. These 70-442 Exam updates are supplied free of charge to Certify Sky.com customers- hereby becoming an investment rather than a disposable product. Our clients receive the most reliable and up-to-date information when they decide to take the exam, just contact us. Our candidates walk into the Testing Room as confident as a Certification Administrator.
Like actual certification exams our 70-442 Exam is in multiple-choice questions (MCQs). After purchasing our products you are just a step away from testing for certification. Still not convinced? Try our free samples or choose to buy your
70-442 Practice Exam now!
Our 70-442 practice exams and study questions are composed by current and active Information Technology experts, who use their experience in preparing you for your future in IT.
Commitment to Your Success: At Certifysky.com we are committed to you ongoing success. Our exams and questions are constantly being updated and compared to industry standards.
You are not about to purchase a disposable product.
70-442 practice exam updates are supplied free of charge. Regardless of how soon you decide to take the actual
70-442 examination certification, you will be able to walk into the testing room as confident as the Certification Administrator.
The CertifySky
70-442 study guide is guaranteed to be 100% braindump free. We value the quality of training you receive through the 70-442 study guide and will never support 70-442 braindumps, or any 70-442 brain dump site.
70-442 braindump sites cannot compare to the understanding, learning and comprehension you will gain from a non-
70-442 braindumps site, based on facts and case studies, like Certify Sky.
When selecting the CertifySky 70-442 exam study preparation materials, you are purchasing the highest quality CertifySky 70-442 products available through the web today. The 70-442 CertifySky practice exams and study guides are current and updated monthly, providing you with the highest 70-442 Certify Sky ROI. Start you road to 70-442 CertifySky success today, buy purchasing the CertifySky 70-442 training materials today!
Comprehensive questions with complete details
