Data access SQL Server 2005.docx

Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 QUESTION 1 You are a database developer for Certkiller .com. In your database, the Categories table is designed as shown in the following display. You need to write a query that retrieves all product categories that exist either directly or indirectly under category 7. Which query should you use? SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT CategoryID FROM Categories WHERE CategoryID = 7 EXCEPT SELECT cs.CategoryID FROM Categories AS cs WHERE ParentCategoryID = 7) SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7 UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN Categories AS c ON c.ParentCategoryID = cs.CategoryID) WITH c AS ( SELECT CategoryID FROM Categories WHERE ParentCategoryID = 7 UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN (SELECT c.CategoryID FROM c) WITH c AS ( SELECT CategoryID FROM Categories WHERE CategoryID = 7 UNION ALL SELECT cs.CategoryID FROM Categories AS cs INNER JOIN c ON c.ParentCategoryID = cs.CategoryID)SELECT * FROM Categories AS c2 WHERE c2.CategoryID IN (SELECT c.CategoryID FROM c) Answer: C QUESTION 2 You are a database developer for Certkiller .com. You are creating a parameterized stored procedure that will query the data and return rows that include any verb tense of the verbs supplied as input to the stored procedure. The query should also return rows that contain both plural and singular forms of nouns. The data is included in several varchar(max) and varbinary(max)columns. You need to create the stored procedure to fulfill these requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) Create a nonclustered index on the appropriate column or columns. Create a full-text index on the appropriate column or columns. Use the LIKE operator. Use the CONTAINS predicate. Answer: A,D QUESTION 3 You are a database developer for Certkiller .com. 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. Which query should you use? SELECT@Article.value('(/articles/article/image)[1]/@filename', 'NVARCHAR(50)') AS Filename,@Article.value('(/articles/article/image/description)[1]', 'NVARCHAR(max)') AS Description SELECT col.query('{@filename}') AS Filename ,col.query('description[1]') AS DescriptionFROM @Articles.nodes('/articles/article/image')AS x (col) ; SELECT col.value('@filename', 'NVARCHAR(50)') AS Filename ,col.value('description[1]', 'NVARCHAR(max)') AS DescriptionFROM @Articles.nodes('/articles/article/image')AS x (col) ; 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); Answer: C QUESTION 4 You are a database developer for Certkiller .com. Your database contains a table named Inventory, which is shown in the following display. You are writing a report that must present the following output. You need to ensure that the report fulfills this requirement. Which two keywords should you use? (Each correct answer presents part of the solution. Choose two.) ROLLUP COMPUTE BY CUBE GROUP BY Answer: C,D QUESTION 5 You are a database developer for Certkiller .com. You are updating a stored procedure that declares and uses a dynamic cursor. The stored procedure originally needed to return the result set and allow for updates to the result set. The requirements have changed, and now the data will be read-only. Therefore, data changes should not be allowed to be made through the cursor. The only fetch command used in the stored procedure is FETCH NEXT. You need to rewrite the cursor definition to use resources as efficiently as possible and to provide the best performance, while fulfilling the new requirements. What should you do? Change the cursor DECLARE statement to use only the LOCAL, FORWARD_ONLY, and OPTIMISTIC options. Change the cursor DECLARE statement to use only the LOCAL and FAST_FORWARD options. Change the cursor DECLARE statement to use only the LOCAL and FORWARD_ONLY options. Add the SCROLL_LOCKS option to the current DECLARE statement with the LOCAL and DYNAMIC options. Answer: B QUESTION 6 You are a database developer for Certkiller .com. 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? DYNAMIC KEYSET FAST_FORWARD STATIC Answer: B QUESTION 7 You are a database developer for Certkiller .com. 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? SELECT Description FROM Product WHERE CONTAINS(Description,@keyword) SELECT Description FROM Product WHERE CAST(Description AS nvarchar(max))LIKE N'%' + @keyword + N'%' SELECT Description FROM Product WHERE PATINDEX(@keyword, CAST(Description as nvarchar(max)) > 0 SELECT Description FROM Product WHERE Description.exist('/Description[contains(., sql:variable("@keyword"))]') = 1 Answer: D QUESTION 8 You are a database developer for Certkiller .com. A procedure exists that saves an order item to the OrderItems table. If the item does not exist, an insert should be performed. If the item exists, an update should be performed. The OrderItems table is designed as shown in the following display. You need to develop a routine that uses a minimum amount of resources. Which routine should you use? BEGIN TRY UPDATE OrderItems SET Quantity = @Quantity WHERE OrderID = @OrderID AND ProductID = @ProductID;END TRYBEGIN CATCH INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity);END CATCH IF EXISTS (SELECT * FROM OrderItems WHERE OrderID = @OrderID AND ProductID = @ProductID) UPDATE OrderItems SET Quantity = @Quantity WHERE OrderID = @OrderID AND ProductID = @ProductID; ELSE INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity); IF NOT EXISTS (SELECT * FROM OrderItems WHERE OrderID = @OrderID AND ProductID = @ProductID) INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity);ELSE UPDATE OrderItems SET Quantity = @Quantity WHERE OrderID = @OrderID AND ProductID = @ProductID; UPDATE OrderItems SET Quantity = @QuantityWHERE OrderID = @OrderID AND ProductID = @ProductID;IF(@@ROWCOUNT = 0) INSERT OrderItems (OrderID, ProductID, Quantity) VALUES (@OrderID, @ProductID, @Quantity); Answer: D QUESTION 9 You are a database developer for Certkiller .com. 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 Transact-SQL function should you use? CHARINDEX SOUNDEX PATINDEX CONTAINS Answer: D QUESTION 10 You are a database developer for Certkiller .com. You use the following Transact-SQL code. SELECT e.Resume,j.EmployeeID FROM JobCandidate j, Employee e WHERE j.EmployeeID =* e.EmployeeID You need to rewrite this code to be ANSI compliant. How should you rewrite the code? A. SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j CROSS JOIN Employee AS e SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j LEFT OUTER JOIN Employee AS eON j.EmployeeID = e.EmployeeID SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j FULL OUTER JOIN Employee AS eON j.EmployeeID = e.EmployeeID SELECT e.Resume, j.EmployeeID FROM JobCandidate AS j RIGHT OUTER JOIN Employee AS eON j.EmployeeID = e.EmployeeID Answer: D QUESTION 11 You are a database developer for Certkiller .com. Information for the Parts database is sent daily as an XML document and is placed in a single row in the ProductInfo column of the ProductXML table. A sample of the information in the XML document is shown in the following display. ... At the end of each day, a batch job is run. This batch imports the XML document into the Product table from the ProductXML table. The products ID, name, and number will be extracted from the XML document for each Product element and will be inserted into the Product tables ProductID, Name, and Number columns. The ProductXML table is truncated at the end of the batch. You need to design the query that retrieves the rows from the ProductXML table for insertion into the Product table. Which Transact-SQL query should you use to query the data from the ProductXML table? SELECT col.query('data(@ID), data(@Name), data(@Number)') FROM ProductXMLCROSS APPLY ProductInfo.nodes('/Products/Product') AS x (col) SELECT col.value('@ID', 'integer') AS ProductID, col.value('@Name', 'varchar(50)') AS Name, col.value('@Number', 'varchar(25)') AS ProductNumber FROM ProductXMLCROSS APPLY ProductInfo.nodes('/Products/Product') AS x (col) SELECT ProductInfo.value('(/Products/Product)[1]/@ID', 'integer') AS ProductID, ProductInfo.value('(/Products/Product)[1]/@Name', 'varchar(50)') AS Name, ProductInfo.value('(/Products/Product)[1]/@Number', 'varchar(25)') AS ProductNumber FROM ProductXML SELECT ProductInfo.query(' for $p in /Products/Product return fn:concat($p/@ID, $p/@Name, $p/@Number) ') AS ResultFROM ProductXML Answer: B QUESTION 12 You are a database developer for Certkiller .com. You are a developing a yearly sales cross-tab report, which will list sales by year for each sales person. Only the data for the years 2004 and 2005 should be displayed. Several columns from the SalesOrder table are shown in the following display. You need to create the report that shows the sales data. Which code segment should you use? SELECT pvt.[SalesPersonID] ,pvt.[2004] ,pvt.[2005]FROM (SELECT so.[SalesPersonID] ,so.[SalesAmount] ,YEAR(so.[OrderDate]) AS [Year] FROM [Sales].[SalesOrder] so ) AS so PIVOT ( SUM([SalesAmount]) FOR [Year] IN ([2004], [2005])) AS pvt; SELECT so.[SalesPersonID] ,so.[SalesAmount] FROM [Sales].[SalesOrder] so WHERE YEAR(so.[OrderDate]) = 2004EXCEPTSELECT so.[SalesPersonID] ,so.[SalesAmount] FROM [Sales].[SalesOrder] so WHERE YEAR(so.[OrderDate]) = 2005 SELECT so.[SalesPersonID] ,so.[SalesAmount] FROM [Sales].[SalesOrder] so WHERE YEAR(so.[OrderDate]) = 2004INTERSECTSELECT so.[SalesPersonID] ,so.[SalesAmount] FROM [Sales].[SalesOrder] so WHERE YEAR(so.[OrderDate]) = 2005 SELECT so.[SalesPersonID] ,so.[SalesAmount] FROM [Sales].[SalesOrder] so WHERE YEAR(so.[OrderDate]) = 2004UNION ALLSELECT so.[SalesPersonID] ,so.[SalesAmount] FROM [Sales].[SalesOrder] so WHERE YEAR(so.[OrderDate]) = 2005 Answer: A QUESTION 13 You are a database developer for Certkiller .com. 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? Change = NULL to IS NULL. Instead of = NULL use the ISNULL function. Use the ALTER DATABASE command to change the ANSI_NULLS database option to ON. Add the following command before the query: SET ANSI_NULLS ON Change = NULL to = 'NULL'. Answer: A QUESTION 14 You are a database developer for a local school district. You maintain information about teachers and staff in a column of the xml data type. You need to produce a tabular report that contains the values of the EmployeeID, Title, and YearsOfEmployment columns for each employee. This report should be ordered by Title in ascending order and YearsOfEmployment in descending order. What should you use to retrieve this data? FOR XML PATH the query method of the xml data column FOR XML AUTO the value and nodes methods of the xml data column Answer: D QUESTION 15 You are a database developer for Certkiller .com. 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. You need to write the query so that it will return the number of orders that have been sold by each employee in the company. Which query should you use? 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 SELECT e.Name, COUNT(*) FROM dbo.Employees AS eINNER JOIN dbo.Orders AS o ON o.SoldByEmployeeID = e.EmployeeIDGROUP BY e.Name 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 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 Answer: A QUESTION 16 You are a database developer for Certkiller .com. 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 front-end 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? CREATE PROCEDURE TopPercentTotalDue@R intASSET rowcount @RSELECT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue ASC CREATE PROCEDURE TopPercentTotalDue@P intASSELECT TOP(@P) PERCENT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue ASC CREATE PROCEDURE TopPercentTotalDue@R intASSET rowcount @RSELECT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue DESC CREATE PROCEDURE TopPercentTotalDue@P intASSELECT TOP(@P) PERCENT TotalDue, OrderDate FROM Purchasing.PurchaseOrderHeaderORDER BY TotalDue DESC Answer: D QUESTION 17 You are a database developer for Certkiller .com. A table named Articles contains newspaper articles and is designed as shown in the following display. The XML document in the ArticleXml column appears as shown in the following display.

... ... ... ... ...

You need to design a query that will display the total number of images per CategoryID value. Which query should you use? SELECT CategoryID ,SUM(ArticleXml.value('count(/article/image)', 'INT')) AS Number of Images From ArticlesGROUP BY CategoryID; SELECT CategoryID ,COUNT(ArticleXml.value('count(/article/image)', 'INT')) AS Number of Images From ArticlesGROUP BY CategoryID; SELECT CategoryID ,SUM(ArticleXml.exist('/article/image') AS Number of Images From ArticlesGROUP BY CategoryID; D. SELECT CategoryID ,COUNT(ArticleXml.exist('/article/image') AS Number of Images From ArticlesGROUP BY CategoryID; Answer: A QUESTION 18 You are a database developer for Certkiller .com. You are developing a report that will return the customer name and phone number. Customers might have a business number, a mobile number, and a fax number. The report must return the first nonnull phone number for each customer. You need to write the query that returns the required customer information for the report. What should you use? NULLIF REPLACE IF COALESCE Answer: D QUESTION 19 You are a database developer for Certkiller .com. 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. The Orders table is designed as shown in the following display. You need to write a query that returns the number of orders placed by each employee. Which query should you use? 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 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 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 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 Answer: D QUESTION 20 You are a database developer for Certkiller .com. 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? the nodes method of the xml data type FOR XML AUTO the modify method of the xml data type the query method of the xml data type Answer: A QUESTION 21 You are a database developer for Certkiller .com. 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? SELECT COUNT(*) FROM OrdersWHERE OrderDate BETWEEN '2005-10-26T00:00:00' AND '2005-10-28T00:00:00' SELECT COUNT(*) FROM OrdersWHERE CONVERT(CHAR(8), OrderDate, 112) = '20051027' SELECT COUNT(*) FROM OrdersWHERE YEAR(OrderDate) = 2005 AND MONTH(OrderDate) = 10 AND DAY(OrderDate) = 27 SELECT COUNT(*) FROM OrdersWHERE DATEDIFF(day, OrderDate, '20051027') = 0 SELECT COUNT(*) FROM OrdersWHERE OrderDate >= '20051027' AND OrderDate < '20051028' Answer: E QUESTION 22 You are a database developer for Certkiller .com. 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? RANK ROW_NUMBER NTILE DENSE_RANK Answer: A QUESTION 23 You are a database developer for Certkiller .com. 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? Change the IF statement to check for @City = 'Stockholm' to eliminate the need for the GOTO statement. Change the SELECT statement in the cursor to includeWHERE City = 'Stockholm'. Change the cursor to be declared as STATIC. Change the cursor to be declared as KEYSET. Change the cursor to be declared as DYNAMIC. Answer: B QUESTION 24 You are a database developer for Certkiller .com. The company is upgrading from SQL Server 2000 to SQL Server 2005. The companys new coding standards require conformance with ANSI standards. Your manager asks you to convert the following query to use ANSI standard wording. SELECT E.EmployeeID, E.Hiredate, J.Resume FROM HumanResources.Employee AS E, HumanResources.JobCandidate AS J WHERE E.EmployeeID *= J.EmployeeID The new query must always produce the same result set as the existing query. You need to write the query to fulfill these requirements. Which Transact-SQL query should you use? SELECT E.EmployeeID, E.Hiredate, J.Resume FROM HumanResources.Employee AS E FULL OUTER JOIN HumanResources.JobCandidate AS J ON E.EmployeeID = J.EmployeeID SELECT E.EmployeeID, E.Hiredate, J.Resume FROM HumanResources.Employee AS E INNER JOIN HumanResources.JobCandidate AS J ON E.EmployeeID = J.EmployeeID SELECT E.EmployeeID, E.Hiredate, J.Resume FROM HumanResources.Employee AS E LEFT OUTER JOIN HumanResources.JobCandidate AS J ON E.EmployeeID = J.EmployeeID SELECT E.EmployeeID, E.Hiredate, J.Resume FROM HumanResources.Employee AS E RIGHT OUTER JOIN HumanResources.JobCandidate AS J ON E.EmployeeID = J.EmployeeID Answer: C QUESTION 25 You are a database developer for Certkiller .com. The company recently upgraded to SQL Server 2005. You are creating a query to return information that shows the total number of purchase orders placed by a specified group of employees, grouped by the vendors who requested the orders. The result set should show each employees total number of purchase orders in a separate column of the result set. Each vendor should have its own row in the result set. You need to create the query to fulfill these requirements. Which Transact-SQL command should you use? UNION PIVOT CROSS APPLY INTERSECT Answer: B QUESTION 26 You are a database developer for Certkiller .com. 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.) 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. Create a nonclustered index over the columns NameFinSweCIAS and AddressFinSweCIAS. Add two new columns named NameFinSweCIAS and AddressFinSweCIAS that use the collation Finnish_Swedish_CI_AS and use triggers to populate them. Create a nonclustered index over the column AddressFinSweCIAS. Create a nonclustered index over the column NameFinSweCIAS. Answer: A,B QUESTION 27 You are a database developer for Certkiller .com. You are creating a report that displays the companys organizational structure. Several columns from the Employee table are shown in the following display. An example of the required output is shown in the following display. You need to create the query that can return the organizational chart for the requested report. Which code segment should you use? DECLARE @ManagerID intSELECT @ManagerID = (SELECT MIN(ManagerID) FROM HumanResources.Employee)DECLARE @Level intSET @Level = 0WHILE @ManagerID IS NOT NULLBEGIN SELECT e.ManagerID, e.EmployeeID, @Level AS Level FROM HumanResources.Employee AS e INNER JOIN HumanResources.Employee m ON e.ManagerID = m.EmployeeID SELECT @Level = @Level + 1END SELECT e.ManagerID, e.EmployeeID, 0 AS LevelFROM Employee AS eWHERE ManagerID IS NULLUNION ALLSELECT e.ManagerID, e.EmployeeID, 1 AS LevelFROM Employee AS eWHERE e.ManagerID in (SELECT e.ManagerID FROM Employee) SELECT e.ManagerID , e.EmployeeID, CASE WHEN e.ManagerID IS NULL THEN 0 ELSE 1 END AS LevelFROM HumanResources.Employee AS e INNER JOIN HumanResources.Employee AS m ON e.ManagerID = m.EmployeeIDORDER BY ManagerID, EmployeeID D. WITH OrgChart (ManagerID, EmployeeID, Level)AS( SELECT e.ManagerID, e.EmployeeID, 0 AS Level FROM Employee AS e WHERE ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, d.Level + 1 FROM Employee AS e INNER JOIN OrgChart AS d ON e.ManagerID = d.EmployeeID)SELECT ManagerID, EmployeeID, LevelFROM OrgChart Answer: D QUESTION 28 You are a database developer for Certkiller .com. 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? EXCEPT INTERSECT LEFT OUTER JOIN CROSS APPLY Answer: B Topic 1, Tailspin Toys, Scenario 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. Topic 1, Tailspin Toys (6 Questions) QUESTION 29 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? Set the transaction isolation level to REPEATABLE READ. Set the transaction isolation level to SERIALIZABLE. Set the ALLOW_SNAPSHOT_ISOLATION database option to ON. Set the READ_COMMITTED_SNAPSHOT database option to ON. Answer: D QUESTION 30 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. Add a HOLDLOCK table hint to the query. Add a NOWAIT table hint to the query. Add a READPAST table hint to the query. Answer: A QUESTION 31 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? Change SET IMPLICIT_TRANSACTION to OFF. Use multiple active result sets (MARS) to execute the stored procedure. Add logic to commit or roll back the transaction based on the successful updates of the data. Add a BEGIN TRANSACTION statement to the stored procedure. Answer: C QUESTION 32 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? ActiveX data objects (ADO) multiple active result sets (MARS) System.Data.DataReader System.Data.DataSet Answer: B QUESTION 33 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? Add a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement to the stored procedure. Remove the SELECT statement from the stored procedure. Perform the update using the Product.Quantity column instead of the @CurrentQuantity variable. Add a NOLOCK hint to the SELECT statement. Move the BEGIN TRANSACTION statement so that it immediately precedes the UPDATE statement. Answer: B QUESTION 34 You need to design the data access strategy for the Tailspin Toys online store search requirement. Which strategy should you use? System.Xml.XmlReader System.Data.SqlClient.SqlDataReader System.Data.DataSet CLR user-defined type Answer: C Topic 2, Blue Yonder Airlines, Scenario Company Overview Blue Yonder Airlines is a new low-cost airline that is being created by an established international airline named Certkiller . Blue Yonder Airlines will initially provide flights on a regional bass. The company plans to add new flights on a monthly basis. Physical Locations Currently, Blue Yonder Airlines has on e regional office located in Chicago. The company plans to add new regional offices in the future. Planned Changes Certkiller stores all data for their airline reservation system on a database that uses cursors to return all data from queries. The Blue Yonder Airlines reservation system will store all data on a SQL Server 2005 database. EXISTING ENVIRONMENT There is no significant information about the existing environment. BUSINESS REQUIREMENTS General Application Solution Requirements The Blue Yonder Airlines reservation system user interface will be written by using ASP.NET.Business logic might be implemented by using database objects or middle-tier business objects. All itineraries will be stored and distributed in XML format.The client application will apply transformations to the XML to render output to the user. When users are searching for flights, the results should be returned as quickly as possible. The time that is needed to complete other database queries should be minimized. When a user makes a fight reservation, the flight information must not be persisted in the Blue Yonder Airlines database until the data is persisted in the central reservations clearinghouse database. When users make flight reservations, they can also request seat assignments. These seat assignments must occur within the transaction of making the flight reservation; but a failure to make a seat assignment should not prevent the completion of the reservation. Because of the anticipated high volume of transactions, the reservation process must minimize blocking. Available seats for any given flight change rapidly. A number of data elements, such as airport codes, city codes, and airline codes, will be used to populate drop-down boxes in the user interface. These data elements do not change frequently. Performance Users should be able to retrieve their existing reservations in 10 seconds or less. When searching for a new fight,users want to see results as soon as they become available, without waiting for all the results. As additional flight options become available from the database, the options should be presented to the Web server. Round trips to the database must be minimized. Some of the data that will be used by the user interface is dynamic. In some cases, data can be queried only by using data from a previous query. This capability must not negatively impact performance and must not increase resource utilization on the SQL Server database. Some of the data that is presented to the user changes infrequently. A strategy must be developed to minimize round trips to the database for this data. This data is retrieved on a connection that uses all default settings. Scalability The initial deployment of the Blue Yonder Airlines reservation system will support the existing regional office. However,as new regional offices open, the application must scale to support those new offices. The current design includes a table named Flight that will hold millions of rows of data. This data will be queried frequently by users who are searching for flights. The data will not be modified except when new flights are added. New flights are added only during a maintenance period. The Blue Youder Airlines system must support a minimum of 1,000 concurrent users who are searching for flights. Availability Blue Yonder Airlines wants their reservation system to be available to customers 24 hours a day,seven days a week, with the exception of four two-hour maintenance periods each month. Recoverability All customer transactions must be consistent and recoverable across applications that comprise the Blue Yonder Airlines reservation system. Interoperability The Blue Yonder Airlines reservation system will interact with the central reservations clearinghouse database. In addition,the Blue Yonder Airlines system must exchange data with the Certkiller airline reservation system. TECHNICAL REQUIREMENTS There are no additional technical requirements. Topic 2, Blue Yonder Airlines (4 Questions) QUESTION 35 You need to design a stored procedure named RequestSeatAssignments that will be called to request seat assignments. You need to ensure that the flight reservations can be completed, even if the seat assignments are not completed. What should you do? Make the call to RequestSeatAssignments within a TRY/CATCH block. If an error occurs, roll back the transaction. Check the return value from RequestSeatAssignments. If there is an error, roll back the transaction. Make the call to RequestSeatAssignments. Check @@ERROR. If there is an error, roll back the transaction. Within RequestSeatAssignments, specify a savepoint. If an error occurs, roll back to the savepoint. Answer: D QUESTION 36 You need to design a query strategy for the Flight table. You need to ensure that the solution fulfills the scalability requirements. Which query hint should you use? NOLOCK HOLDLOCK TABLOCK ROWLOCK Answer: A QUESTION 37 You need to design a strategy for database consistency. You need to ensure that the Blue Yonder Airlines databases can be recovered to a logical point in case of failure. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.) Issue a BEGIN TRANSACTION WITH MARK statement in all stored procedures. In each database, create a stored procedure named MarkOne that issues a BEGIN TRANSACTION WITH MARK statement. Issue a BEGIN DISTRIBUTED TRANSACTION statement in all stored procedures. In each database, create a stored procedure named MarkAll that calls MarkOne for all instances. Set the recovery model of each database to simple. Periodically call MarkAll from all stored procedures. Answer: B,D,F QUESTION 38 You need to design the stored procedure that will make flight reservations. Which statement should you issue in the stored procedure? BEGIN TRANSACTION WITH MARK BEGIN TRANSACTION BEGIN DISTRIBUTED TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Answer: C Topic 3, Humongous Insurance, Scenario Company Overview Humongous Insurance is a large corporation that specializes in life insurance. The company employs approximately 10,000 people. Planned Changes The company is currently reviewing their database system, with a plan to perform several upgrades and to optimize query performance. The database is located on a four-processor server that has 16 GB of RAM and that runs SQL Server 2005 Standard Edition. EXISTING ENVIRONMENT Existing Application Environment A stored procedure named spInsuranceOrderAdd adds an order of a new insurance policy to the InsuranceOrders table. The order must also store the items of the order in the InsuranceOrderItems tables; an order must contain at least one item and no more than 10 items. The information is passed to the procedure as an XML parameter. Occasionally when this stored procedure runs, no insurance items items are added to the insurance order. This problem must not occur after the upgrade. The InsuranceOrders table contains a very large number of rows. The table is configured as shown in the following display. The only index that exists on this table is the primary key clustered index. The InsuranceOrderItems table contains a very large number of rows. The table is configured as shown in the following display. The only index that exists on this table is the primary key clustered index. A query against a SQL Server Profiler trace table that was taken from the current database server returns the information shown in the following table. A stored procedure named spProductSearch is run frequently. It occasionally has very slow response time. It uses several queries to calculate and return results. The SELECT statement that is causing the procedure to run slowly needs different indexes depending on the parameters that are supplied by the application. The InsuranceOrder and InsuranceOrderItems tables are queried frequently. The following queries are by far the most frequently executed queries. SELECT * FROM InsuranceOrders WHERE InsuranceOrderID = ? SELECT * FROM InsuranceOrders WHERE CreatedDate = ? SELECT COUNT (*) FROM InsuranceOrders WHERE CustomerID = ? SELECT * FROM InsuranceOrdersItems WHERE InsuranceOrderItemID = ? SELECT COUNT (DISTNCT InsuranceOrderID) FROM InsuranceOrdersItems WHERE Price = ? SELECT * FROM InsuranceOrdersItems WHERE Product ID = ? These queries must have very short response times. The space required by the InsuranceOrders and InsuranceOrderItems tables and their indexes must be kept to a minimum. A stored procedure updates rows in the InsuranceOrders table based on several calculations. The process inside the procedure is executed in a transaction and performs the following operations. It reads several rows from the table. It does some calculations. It updates the same rows as in step 1, based on the calculations done in step 2. This process must be optimized to minimize blocking, deadlocks, and update conflicts. The data that is read in step 1 is allowed to be changed by another transaction before step 3 is run. BUSINGESS REQUIREMENTS General Requirements All user-defined error messages that are sent to the client application must be centralized. The messages must support multiple languages. Development time for this feature must be minimized, including the process for editing the messages. TECHNICAL REQUIREMENTS Maintainability A table named Events logs everything that occurs in Humongous Insurance??s application. This table is becoming very large. The hard disks that are currently being used for storing the Events table are very expensive. The company wants to use large, less expensive hard disks for storing this table and to use faster, smaller hard disks for the remainder of the database. Referential integrity between the Events table and tables that it references must be maintained. The current version of the database occasionally raises error number 50999 within transactions. When this error occurs, the execution should be stopped immediately and the transaction should be committed if possible. This transaction processing should not cause additional errors. Topic 3, Humongous Insurance (4 Questions) QUESTION 39 You need to design the method for managing user-defined error messages to fulfill the business requirements. What should you do? Create a Service Broker queue to contain the error messages. Use SQL Servers built-in system for managing error messages. Create a new table to store the error messages. Create two new tables to store the error messages: use one for the languages that are available, and use one for messages. Answer: B QUESTION 40 You need to design the storage of the Events table to fulfill the technical requirements. What should you do? Place all nonclustered indexes on another filegroup. Place the table on another filegroup. Move the Events table to another database. Move the Events table to another server. Answer: B QUESTION 41 You need to ensure that the stored procedure spInsuranceOrderAdd functions according to specifications. What should you do? Wrap the inserts into the InsuranceOrders and InsuranceOrderItems tables in a TRY/CATCH block. Issue a rollback statement in the CATCH block. On the InsuranceOrderItems table, place a foreign key that points to the InsuranceOrders table. Insert the insurance order into the InsuranceOrders table, and then insert the items on the order into the InsuranceOrderItems table. Verify that the items were inserted by using the OUTPUT clause of the INSERT statement. If an incorrect number of items is found, roll back the transaction. D. Place a check constraint on the InsuranceOrders table. Design the check constraint to use a user-defined function to verify that the order has corresponding rows in the InsuranceOrderItems table. Answer: C QUESTION 42 You need to optimize the stored procedure spProductSearch. What should you do? Execute the stored procedure by using the recompile option. Modify the stored procedure by adding the recompile option to it. Modify the stored procedure by adding the recompile option to the problematic query. Create a plan guide for the stored procedure that tells the problematic query to use a specific execution plan. Answer: C Topic 4, Certkiller 'stravel, Scenario BACKGROUND Company Overview Certkiller 's Travel is a small regional travel agency that handles many different types of travel arrangements. Planned Change Certkiller 's Travel has purchased a COM component named Cruisex to be used to access cruise lines information. Certkiller '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 agent 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 cancellation 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. Certkiller '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 minimize 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 resources. 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. Topic 4, Certkiller 'stravel (6 Questions) QUESTION 43 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? SqlXmlCommand updategrams XML template queries HTTP endpoints Answer: D QUESTION 44 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? BEGIN TRANSACTION Acct WITH MARK 'Accounting' BEGIN DISTRIBUTED TRANSACTION Acct SET IMPLICIT_TRANSACTIONS ON SAVE TRANSACTION Acct Answer: A QUESTION 45 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? Remove the INSENSITIVE argument from the cursor declaration. Add the OPTIMISTIC argument to the cursor declaration. Rewrite the stored procedure using set operations. Rewrite the stored procedure as a user-defined type. Answer: C QUESTION 46 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? Set the transaction isolation level to READ COMMITTED. Set the transaction isolation level to REPEATABLE READ. Set the READ_COMMITTED_SNAPSHOT database option to ON. Set the READ_COMMITTED_SNAPSHOT database option to OFF. Answer: B QUESTION 47 You need to design the data access model for available flight information. What should you use? XML template queries ActiveX data objects (ADO) System.Data.DataSet System.Data.DataReader Answer: D QUESTION 48 You need to design the data access object model to be used for the infrequently changing data about trading partners. What should you use? ActiveX data objects (ADO) System.Data.DataReader System.Data.DataSet System.Xml.XmlReader Answer: C Topic 5, WoodgroveBank, Scenario BACKGROUND Company Overview Woodgrove Bank provides credit card services and traditional banking services to its customers across the United States. More than 200,000 credit accounts and 100,000 banking accounts are handled. The bank is testing a Web based application to provide stock analysis to its customers. Planned Changes Woodgrove Bank plans to implement a Web site to support the new stock analysis project. A database named StockPortfolios has been built on a new server to support this project. The company's security department has approved limited usage of dynamic SQL queries to be supported inside of parameterized stored procedures that will be used to look up stock information in the StockInfo table in the StockPortfolios database based on four-character stock symbols. EXISTING ENVIRONMENT Databases All of the company's database servers run SQL Server 2005.The four main databases are described in the following table. The CreditAccount table of the Credit database includes information on a customer's credit card account. The table includes a CustomerBalance column that contains the current customer's credit card balance. The table also includes a BalanceLastUpdated column that is maintained through an update trigger. The CurrentTransactions table of the Credit database includes information relating to all financial transactions from the most recent five years, including pending transactions that have not been completed. The Pending column is set to 0 after a transaction is completed and is no longer pending. Performance In the Banking database,s everal stored procedures that run against the BankCustomer table perform slowly on an occasional basis. These stored procedures run frequently. They have problems only when an employee runs a report searching for customers with parameters outside of the normal search scope. The majority of these stored procedures are searching for customers who have more than $250 in the AvgMonthlyBalance column. Under what is expected to be a normal load for the server. The StockPortfolios test database has been performing poorly. Statistics gathered by using System Monitor are displayed in the following table. The following query to the Credit database is running very slowly. SELECT CustomerID,FirstName,LastName,CustomerBalance.Creditlimit FROM CrdeitAccount WITH {INDEX(0)} WHERE LastName=? You view the graphical execution plan. It indicates that a clustered index scan is being performed. When the HR database was created, the nvarchar(400)data type was chosen for all nonnumeric fields, The bigint data type was used for all primary and foreign key columns, and money was used for all monetary fields.No table is expected to have more than 10 million rows. Some fields hold monetary values of over one million dollars and are expected to grow. Tests run on the HR database have shown that at least a portion of the performance problems on this database are due to the large row sizes in the database. A significant number of joins that are run on the tables in the HR database are performing poorly. All primary key fields in these tables are identity columns. Company management is reviewing the indexing on the Credit database. One month ago, a maintenance plan was implemented to rebuild indexes and maintain statistics, based on how quickly the indexes are becoming fragmented. Management has decided that this maintenance plan is optimal and is keeping the indexes in a usable state. All statistical analysis of the indexes should include all statistics that have been gathered since the shutdown that occurred after the maintenance plans were added. No additional shutdowns have occurred. Scalability Although real-time stock quotes will be received through a subscription service, a copy of the historical stock data will be maintained locally in the StockPortfilios database. The database will contain billions of rows of historical stock data. This data will be divided alphabetically among three servers based on the stocks trading symbols. All access to this data will be managed through a distributed view. BUSINESS REQUIREMENTS General Requirements A customer can be marked as inactive only if the customer's credit card account has a balance of $0 for 60 days or longer. Certain credit card customers are allowed to have balances greater than their credit limit. When an update to the CustomerBalance column results in this over-limit balance, the credit manager must be notified by e-mail without using unnecessary resources. This message should also be sent to the front-end application. Employees in the customer service department regularly need to run reports on all inactive customers. These employees and the front- end application should not need to specify when a customer was first marked inactive. TECHNICAL REQUIREMENTS Most queries in the Credit database refer to active customers. Company policy states that all data must be preserved for future analysis, including data for inactive customers. The database administrator has stated that data for an inactive customer should be moved to a separate server six months after the customer is marked inactive. Updates will not be made to customers on this separate server.If a customer is reactivated, the corresponding data will be manually moved back to the Credit database. The data will never exist in both databases simultaneously. Topic 5, WoodgroveBank (7 Questions) QUESTION 49 You are designing a parameterized stored procedure that will be called to update a credit card account and mark it as inactive. You need to ensure that the companys requirements are fulfilled when a customers credit card account is marked as inactive. You create a portion of the stored procedure as shown in the following code segment. CREATE PROCEDURE InactivateCustomer_proc @CustID int = 0 AS IF @custID = 0 OR @custid NOT IN (SELECT customerid FROM CreditAccount) BEGIN RAISERROR ('You must supply a valid customer id number.',16,1) RETURN END DECLARE @datediff datetime DECLARE @CustomerBalance money /*Insert Code to Complete Procedure Here*/ UPDATE CreditAccount Set Inactive ='T' WHERE CustomerID = @CustID You need to complete the stored procedure to send an error message to the front-end application when a customer cannot be marked inactive. Which code segment should you include immediately after the remark in the stored procedure? SELECT @datediff = datediff(dd,BalanceLastUpdated,getdate()), @CustomerBalance = CustomerBalance FROM creditaccount WHERE CustomerID = @CustID IF @datediff < 60 OR @CustomerBalance <> 0 BEGIN RAISERROR ('Customer cannot be marked inactive because the account balance has not been $0 for at least 60 days.',16,1) END SELECT @datediff = datediff(dd,BalanceLastUpdated,getdate()), @CustomerBalance = CustomerBalance FROM creditaccount WHERE CustomerID = @CustID IF @datediff < 60 OR @CustomerBalance <> 0 BEGIN RAISERROR ('Customer cannot be marked inactive because the account balance has not been $0 for at least 60 days.',16,1) RETURN END SELECT @datediff = datediff(dd,BalanceLastUpdated,getdate()), @CustomerBalance = CustomerBalanceFROM creditaccount WHERE CustomerID = @CustID BEGIN TRYIF @datediff < 60 OR @CustomerBalance <> 0RAISERROR ('Customer cannot be marked inactive because theaccount balance has not been $0 for at least 60 days.',16,1)END TRY BEGIN CATCHSELECT @datediff AS [Days Since Change], @CustomerBalance AS [Current Balance]END CATCH SELECT @datediff = datediff(dd,BalanceLastUpdated,getdate()), @CustomerBalance = CustomerBalanceFROM creditaccount WHERE CustomerID = @CustID BEGIN TRYIF @datediff < 60 OR @CustomerBalance <> 0RAISERROR ('Customer cannot be marked inactive because theaccount balance has not been $0 for at least 60 days.',16,1)END TRY BEGIN CATCHSELECT @datediff, @CustomerBalanceRETURNEND CATCH Answer: B QUESTION 50 You are assigned to work on the index redesign project for the Credit database. You need to find out which indexes are causing a large amount of maintenance overhead while being used infrequently for queries. Your analysis should fulfill the requirement that any statistical analysis of the indexes must include all statistics that have been generated since the maintenance plan was put in place. You need to find out the number of times the index is accessed for maintenance activities in comparison to the number of times the index is accessed for user seeks and scans. What should you do? Run SQL Trace during business hours for two days. Use the trace file to verify which queries are being run, the table scans that are being performed, and the number of index rebuilds that are being run. Run SQL Trace during business hours for two days. Use the trace file as input for the Database Engine Tuning Advisor to confirm the best indexes to keep. Use the sys.dm_db_index_operational_stats dynamic management function. Use the sys.dm_db_index_usage_stats dynamic management view. Answer: D QUESTION 51 You need to optimize the poorly performing queries in the Banking database. You want to ensure that performance for these queries is as fast as possible when the most common parameters are used. What should you do? Create a plan guide and use the RECOMPILE query hint. Create a plan guide and use the OPTIMIZE FOR query hint. Execute the stored procedure and use the WITH RECOMPILE option. Alter the stored procedure to include the FAST number_rows query hint. Answer: B QUESTION 52 You are configuring the credit card system to send required e- mail messages to the credit manager. You configure the appropriate settings for Database Mail and SQL Server Agent operators. You create the following user-defined message. sp_ addmessage 50015,10,'The Customer with a Customer ID of %d has a credit balance higher than his credit limit. The contact name and telephone number are %s.' You create an alert that is associated with error number 50015 to send an e-mail message, including the error message text, to the credit manager. You need to write the trigger that will cause the required e-mail messages to be sent.Which command should you use to invoke the alert? PRINT RAISERROR RAISERROR WITH LOG xp_logevent Answer: C QUESTION 53 You are attempting to correct the performance problems on the test server for the StockPortfolios database. You review the statistics that were gathered by using System Monitor. You need to resolve the performance problems. What should you do? Upgrade the network adapter. Install a second processor. Add additional memory. Install an additional hard disk. Answer: B QUESTION 54 You need to develop the code that will be used to insert historical stock data rows into the StockPortfolios database. What should you do? Write an INSTEAD OF trigger on the view. Write a stored procedure with insert and update commands issued against the view. Write an AFTER trigger on each of the underlying tables. Write a stored procedure that uses transactions with insert or update commands issued against the underlying tables. Remove the distributed view and replace it with a partitioned table. Write a stored procedure with insert and update commands issued against the partitioned table. Write an INSTEAD OF trigger on each of the underlying tables. Write a stored procedure with insert and update commands issued against the underlying tables. Answer: A QUESTION 55 You need to optimize the poorly performing query to the CreditAccount table in the Credit database. What should you do? Remove the WITH (INDEX(0)) table hint from the query. Add the NOWAIT table hint to the query. Create a composite clustered index on the CreditAccount table with all of the columns from the query. D. Alter the nonclustered index to include the CustomerID column. Answer: A Topic 6, Consolidated Messenger, Scenario OVERVIEW Company Overview Consolidated Messenger manages information about company press releases and news coverage. When new press releases or news coverage about companies are found, Consolidated Messenger notifies customers about companies that they have subscribed to. There are different subscription levels that have different prices. Planned Changes Consolidated Messenger plans to implement a process that will run every hour to mark as inactive all price quotes that have been available for 48 hours. This process must be performed on a per-customer basis: all price quotes for one customer are processed, and then all price quotes for the next customer, and so on. Only the customers that exist at the start of the job should be processed. In the existing application, subscribers can receive only a plain text version of a press release or news article that they want to view. Consolidated Messenger plans to allow customers to download a PDF version that can contain images. These PDF files are generated from information in the database and must always be up- to- data. At any given time, thousands of PDF files might be available, and they can each be up to 5 MB in size. Many customers might download the same PDF file. These concurrent downloads must demand a minimum amount of resources from the Web server and the database servers. EXISTING ENVIRONMENT Existing Application Environment Several employees at Consolidated Messenger use a Windows application to monitor all news information about companies. This application displays a list of all companies and summary information about each company. The list of companies in the application can contain thousands of rows and needs to be updated as soon as possible after an update to a row in the Companies table. Because this updating can occur frequently, the network bandwidth that is used needs to be kept to a minimum. The subscription prices that are charged to customers are frequently updated to reflect currency. fluctuations. When price quotes are being sent to customers, the prices are read several times from the table to calculate the correct quote. If a price is changed or a new price is added during this process, the price quote would be inaccurate. Inaccurate price quotes are not allowed. BUSINESS REQUIREMENTS General Requirements Consolidated Messenger has partners around the world that provide Consolidated Messenger with news information. These partners need to be able to use their own applications to upload press releases and articles into Consolidated Messenger's database. Performance Because the application has many users and processes, blocking caused by reading and writing data between connections must be kept to a minimum. Recoverability When a new subscription is inserted into the Subscriptions table, a row needs to be inserted into two separate logging tables,named PrimarySubscriptionLog and SecondarySubscriptionLog,in a database on a separate server. If the insert into the PrimarySubscriptionLog fails, the insert into the local Subscriptions table needs to be cancelled. If the insert into the SecondarySubscriptionLog fails, the insert into the previous tables must be kept. The separate server runs SQL Server 2005 and is defined as a linked server named Certkiller 2 on the current database server. TECHNICAL REQUIREMENTS Brief comments about companies that are reviewed by Consolidated Messenger are collected from the Internet and are inserted into a proprietary application. When these comments are loaded into the database, some inserts might fail due to foreign key constraints and check constraints on the Comments table. These rows should be ignored, because these comments are not valid and should not be inserted. There will occasionally be many comments to insert. Therefore, the routine needs to be as fast as possible. One of the forms in the Windows client application uses a method named ManageNewSubscriptions. This method issues either an update or an insert using a separate connection inside a loop that iterates through a System.Data.SqlClient.SqlDataReader object. Currently this method is experiencing sever blocking problems while no other queries are running. Because the transaction that updates a subscription price is run frequently, the resources that are used and the time that is needed to complete the transaction must be minimized. The transaction needs to verify only that the update of the price was successful. If it was not, an error message must be issued. Blocking for this operation is not a problem. Currently a Web page on the corporate intranet displays the 10 most recent subscriptions. This page must be modified to display all active subscriptions. There might be several thousand subscriptions. The data that is displayed must not include dirty reads. The solution must use a minimum amount of memory on the Web server. Topic 6,Consolidated Messenger (9 Questions) QUESTION 56 You need to design the transaction that calculates price quotes for customers according to the requirements. What should you do? To answer, move the appropriate actions from the list of actions to the answer area, and arrange them in the correct order. Answer: QUESTION 57 You need to design the process that will mark price quotes as inactive, while fulfilling the requirements. What should you do? Use a dynamic Transact-SQL cursor over the Customers table.Issue one update per customer. Use a keyset Transact-SQL cursor over the Customers table.Issue one update per customer. Issue a single update on the Customers table and use a correlated subquery against the table that contains the price quotes. Issue a single update, and join the Customers table to the table that contains the price quotes. Answer: B QUESTION 58 You need to design the process of refreshing the list of companies in the application when new information about a company is made available. What should you do? Use a single SELECT statement to populate the list. Use a SqlDependency object to subscribe to the OnChanged event. When the event is raised, reissue the SELECT statement and repopulate the list. Use three SELECT statements with different WHERE clauses to populate the list. One statement will select the first third of available companies, one will select the second third, and one will select the final third. Use a SqlDependency object to subscribe to the OnChanged event on each select. When the event is raised, reissue the affected SELECT statement and repopulate that part of the list. Repopulate the entire list once every 10 minutes. Repopulate the entire list once every two minutes. Answer: B QUESTION 59 You need to select a data access method for the intranet Web page that displays active subscriptions. The method must fulfill the technical requirements. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.) Use a DataSet object to populate the Web page. Use a DataTable object to populate the Web page. Use a DataReader object to populate the Web page. Use an XmlDocument object to populate the Web page. Populate the XmlDocument object by using a SELECT ... FOR XML ... statement. Use an XmlReader object to populate the Web page. Populate the XmlReader object by using a SELECT ... FOR XML ... statement. Answer: C,E QUESTION 60 You need to design the process for uploading the data from the partner applications. What should you do? Allow access through an HTTP endpoint that uses SSL ports, and design the endpoint to run a stored procedure. Allow access through an HTTP endpoint that uses SSL ports, and design the endpoint to run a user-defined function. Allow access through the ADO.NET System.Data.SqlClient namespace. Grant the partner application access to the appropriate stored procedure. Allow access through the ADO.NET System.Data.SqlClient namespace. Grant the partner application access to the appropriate user-defined function. Answer: A QUESTION 61 You need to design the transaction that inserts subscriptions into the Subscriptions, PrimarySubscriptionLog, and SecondarySubscriptionLog tables. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) Use a distributed transaction. Use a local transaction. Issue a transaction savepoint prior to the insert into the SecondarySubscriptionLog table. Commit the transaction prior to the insert into the SecondarySubscriptionLog table. Commit the transaction after the insert into the SecondarySubscriptionLog table. Answer: A,D QUESTION 62 You need to resolve the problems with the ManageNewSubscriptions method. What should you do? Start a transaction before the SELECT statement that the System.Data.SqlClient.SqlDataReader object is iterating through. Use the serializable transaction isolation level. Use a multiple active result sets (MARS) connection. Use the same connection for both the reader and the UPDATE and INSERT statements. Enable multiple active result sets (MARS) for both the reader connection and the connection that is used for the UPDATE and INSERT statements. Answer: C QUESTION 63 You need to design the transaction isolation for the query that populates the active subscriptions page on the corporate intranet. Which transaction isolation level should you use? read committed read committed snapshot repeatable read read uncommitted serializable Answer: B QUESTION 64 You need to design the download process of PDF documents by customers, while fulfilling the requirements. What should you do? Store the name of each PDF document in the database. Store the PDF documents on the database servers hard disks. Perform the following steps.1. When the first customer downloads a given PDF document, copy the document to the Web servers hard disk.2. Perform subsequent downloads of that document against the Web servers hard disk. Store the PDF documents in a varbinary(max) field in the database.Take customer downloads of the PDF documents directly from the database. Store the PDF documents in a varbinary(max) field in the database. Perform the following steps.1. When the first customer downloads a given PDF document, copy the document to an ASP.NET cache object on the Web server.2. Perform subsequent downloads of that document against the Web servers ASP.NET cache object.3. If the PDF document in the database changes, design the data access object to repeat step 1. Store the PDF documents in a varbinary(max) field in the database. Perform the following steps.1. When the first customer downloads a given PDF document, copy it to the Web servers hard disk.2. Perform subsequent downloads of that document against the Web servers hard disk.3. If the PDF document in the database changes, design the data access object to repeat step 1. Answer: D Topic 7, Wide World Importers, Scenario BACKGROUND Company Overview Wide World Importers imports, sells, and delivers bicycles from all over the world. The application that is used for tracking orders and deliveries is written in Microsoft Visual C# 2005 by using Microsoft.NET Framework 2.0.The databases that are used by the application are hosted in a multi-CPU server that runs SQL Server 2005 Enterprise Edition. Planned Changes The company is currently developing an updated version of its application in order to resolve various problems and to improve the application's performance. EXISTING ENVIRONMENT Existing Application Environment The current application has problems with data validation. When phone numbers are inserted into tables, the phone numbers must have a specified format. Currently, no check is made in the database for this format. Wide World Importers wants the new version of the application to prohibit incorrectly formatted phone numbers from being entered into tables. The verification process must have minimal negative impact on performance. The Deliveries table contains information about all bicycle deliveries that are made by Wide World Importers. This table is populated every Wednesday at 23:00 with the data from the preceding seven days. Because a very large number of rows might be inserted, the indexes on the table must be rebuilt following the import. This process takes several hours and impairs the usability of the database for the users who run queries on Thursday moming. The time that is needed for rebuilding the indexes must be minimized. In addition, the performance of the existing indexes must be changed as little as possible. The TransactionHistory table contains a very large number of rows. The overall query performance against this table is very poor and needs to be optimized. Inserts into the table occur infrequently; they are usually performed at night.column.Samples of these typical queries are shown in the following code segments. SELECT*FRON TransactionHistory WHERE TransactionID=@TransactionID SELECT*FRON TransactionHistory WHERE CustomerID=@CustomerID The Customers table and its indexes currently use too much memory. The table is designed as shown in the following display. The primary key is created as a nonclustered index. There are eight other nonclustered indexes over various columns, and there is a clustered indexes on the Address column. The Orders table is designed as shown in the following display. A query that retrieves all orders based on the customer's ID from the Orders table is performing poorly.The query execution plan shows that clustered index lookups are being used to return the data from the query. The query is designed as shown in the following code segment. SELECT OrderID,OrderDate,ShipDate FROM Orders WHERE CustomerID=@CustomerID; The clustered index is named PKOrderID and is created over the OrderID column. There is also a nonclustered index,which is named IdxCustomerID.It is created over the CustomerID column. A check of the fragmentation of the two indexes returns the partial result shown in the following display. This query needs to be optimized because it is run frequently. Inserts to the table are performed only nightly; their performance is of only secondary importance. The memory used by the table must also be minimized. A report that displays all orders received for a specific month, independent of year, is not performing well. This report rectums thousands of rows. The query that is used by the report is shown in the following code segment. SELECT OrderID,OrderDate,CustomerID FROM Orders WHERE MONTH(OrderDate)=@OrderMonth BUSINESS REQUIREMENTS General Requirements All application-specific error messages that are sent from the database to the application must generate an error in the application and must cause the application to handle that error. The messages that are sent are edited regularly and must be available in English, German, and French. It must also be easy to find which messages are available in each language. Performance Performance metrics of query executions must be automatically logged so that they can be queried. These metrics must be searched to identify queries that are likely to have locking or blocking problems. Recoverability If the database server fails, a plan must be in place for finding out if any configuration changes were made shortly before the failure. Database snapshots must be created every hour. TECHNICAL REQUIREMENTS Security The possibility that SQL injection attcks might occur against stored procedures that use dynamic SQL must be minimized. Maintainability Employees who work with documentation for the database need to have a stored procedure that can be run with a table name as a parameter.The stored procedure must always display the first five rows of that table.The employees must not have direct access to the table except through this stored procedure. Topic 7, Wide World Importers (6 Questions) QUESTION 65 You need to design the solution for logging and reviewing the performance metrics of the production database. The solution must fulfill the requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) Use SQL Server Profiler at the specified intervals for finding the problematic queries. Schedule a SQL Trace to run at specified intervals. Create a job that searches this trace after the specified intervals for the problematic queries. Search for queries that have a low value for duration and a high value for reads and writes. Search for queries that have a high value for CPU and a low value for duration. Search for queries that have a high value for duration and a low value for reads and writes. Answer: B,E QUESTION 66 You need to design the stored procedure that will retrieve the first five rows from any table in the database. Your design must fulfill the requirements. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.) Create the stored procedure with the EXECUTE AS SELF option. Create the stored procedure with the EXECUTE AS OWNER option. Use dynamic SQL. Use a data type for the table name parameter that is not larger than 128 characters. Design the stored procedure to check that the table named in the parameter exists in the sys.tables catalog view before executing the SELECT statement against the table. Answer: B,C,E QUESTION 67 You need to minimize the memory usage of the Customers table as much as possible to fulfill the requirements. What should you do? Rebuild the clustered index with a fill factor of 10. Rebuild the clustered index with a fill factor of 100. Rebuild the index on the primary key column. Change the data type of the primary key column to a smaller data type. Use a different column with a smaller data type for the clustered index. Answer: E QUESTION 68 You need to design the validation of the phone number format to fulfill the requirements. What should you do? Design all stored procedures that insert and update tables containing phone numbers to verify that the phone numbers have the correct format by using a user-defined function. Design CHECK constraints on the tables containing phone numbers to verify that the phone numbers have the correct format by using a user-defined function. Design AFTER triggers on the tables containing phone numbers to verify that the phone numbers have the correct format by using a user-defined function. Design INSTEAD OF triggers on the tables containing phone numbers to verify that the phone numbers have the correct format by using a user-defined function. Answer: B QUESTION 69 You need to optimize the report for orders received per month. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) Create a clustered index on the OrderDate column. Create a computed column over MONTH(OrderDate) and change the query to filter on this column. Create a nonclustered index on the computed column and include the columns OrderID, CustomerID, and OrderDate. Change the query to use the DATEPART function instead of the MONTH function. Change the query to use the BETWEEN operator on the OrderDate column instead of the = operator. Answer: B,C QUESTION 70 You need to design a plan for finding out which configuration changes might have been made shortly before the system failure. Which plan should you recommend? Query the dynamic management views in the most recent database snapshot. Restore the most recent full backup of the database, using another database name. Query the dynamic management views in the restored database. Use SQL Server Profiler to start a trace. Query the default trace for what occurred before the failure. Answer: D Topic 8, Contoso, Scenario BACKGROUND Company Overview Contoso, Ltd., manages an online reservation system for low-fare airlines. Physical Locations The main office is located in London. The company also has offices around the world that connect by VPN to the company??s reservation system. Planned Changes The existing application was built by using Microsoft .NET Framework 1.1 and SQL Server 2005. The application will be upgraded to Microsoft.NET Framework 2.0 and revised with various improvements. EXISTING ENVIRONMENT Existing Application Environment The Contoso, Ltd., reservation system is hosted on a Web farm that consists of four Web servers that run Microsoft Windows Server 2003 Web Edition and a clustered database server that runs SQL Server 2005 Enterprise Edition. BUSINGESS REQUIREMENTS General Requirements The new version of the application must allow personnel of Contoso, Ltd., to view the current airfares for all airline routes. This capability was not implemented in the existing version because it led to performance problems when all client computers refreshed their data. When the data is being refreshed, dirty reads must be prevented and update conflicts must be minimized. The airfare information is changed continuously. It is sometimes changed many times per minute, and more than 100 client computers will be monitoring the data. The client application can display data that is up to five minutes old. The airfare list must be made available as an XML text file on the company extranet. This file must be created as fast as possible and must require a minimum amount of memory on the server that creates the file. Passenger lists that contain thousands of passenger names must be made available on the corporate intranet for verification purposes. Because these lists are retrieved frequently, use of Web server memory must be minimized. Maintainability Personnel at Contoso, Ltd., occasionally need to delete inactive customer accounts. In the existing application, all invoices and orders must be manually deleted before it is possible to delete the customer account. In the new version this data must be deleted automatically. The delete process must also be as fast as possible. TECHNICAL REQUIREMENTS General Requirements The information about the airlines that are handled by Contoso, Ltd., is imported from a separate server by using an INSERT command with a nested linked server subquery. The data is then updated in several sequential steps. If any step fails, the entire import must be cancelled and the data must be restored to its original state. The query must have the fewest possible requirements on the other server. A routine in the client application named Routine 112 issues updates inside a loop through a DataReader object using a separate connection. Currently this routine is experiencing server problems with deadlocks and blocking. These problems appear even when no other queries are running. Stored procedures will be used throughout the application. Frequently several procedures will be run in a single transaction. Some stored procedures will run other stored procedures within transactions. It must be apparent whether a nested or previous stored procedure has encountered an error. The inner stored procedure that is being called must also be depended on to cancel the transaction. When a customer requests a reservation, the reservation is placed in the ReservationRequests table. This table is queried and updated constantly. In the existing version of the application, these queries occasionally take a long time to execute, and deadlocks occur regularly. The query that is used in the existing application to fetch a reservation that has not yet been handled by another employee is shown in the following code segment. UPDATE TOP (1) ReservationRequests SET HandledByEmployeeID = @ EmplyeeID OUTPUT INSERTED .ReservationRequest ID Some transactions that add and modify ranges of rows based on searches of the foreign key column CustomerID require protection against phantom rows. This protection must be implemented with the minimum amount of negative impact on concurrency and the performance of other queries. Topic 8, Contoso(8 Questions) QUESTION 71 You need to design the solution to create the airfare XML text file for the company extranet. What should you do? Use a SELECT statement to fetch the data. Load the result set into a DataSet object.Write the XML code in the DataSet object to the text file by using the WriteXml method. Use a SELECT FOR XML statement to generate the XML code. Load the XML code from the database into an XmlDocument object.Write the XML code in the XmlDocument object to the text file by using the Save method. Use a SELECT FOR XML statement to generate the XML code. Load the result set into a DataSet object.Write the XML code in the DataSet object to the text file by using the WriteXml method. Use a SELECT FOR XML statement to generate the XML code. Read the XML code from the database by using an XmlReader object.Write the XML code to the text file using an XmlTextWriter object. Answer: D QUESTION 72 You need to resolve the problems with the routine named Routine112. What should you do? Use a separate connection for each update. Open a multiple active result sets (MARS) connection, use it for both the reader and the updates, and issue the updates on the same thread as the loop. Disable connection pooling. Implement connection pooling. Answer: B QUESTION 73 You need to design the retrieval of the airfares for the new version of the application to fulfill the business requirements. What should you do? Design a routine for the client computers that refreshes the information every five minutes. Design a routine for the client computers that refreshes the information every 10 seconds. Use the SqlDependency.OnChanged event on the client computers to notify them when the information changes. Then, if the most recent refresh was more than five minutes ago, automatically refresh the data. Use the SqlDependency.OnChanged event on the client computers to notify them when the information changes. Then, automatically refresh the data. Answer: C QUESTION 74 You need to design the retrieval of the passenger lists for the company intranet to fulfill the business requirements. What should you do? Use a DataReader object to populate the Web page. Use a DataSet object to populate the Web page. Retrieve an XmlDocument object from a DataSet object, and use the XmlDocument object to populate the Web page. D. Use the FOR XML clause of the SELECT statement to retrieve the data. Load the data into an XmlDocument object and use the XmlDocument object to populate the Web page. Answer: A QUESTION 75 You need to optimize the query against the ReservationRequests table to fulfill the technical requirements. What should you do? Add the READPAST locking hint to the query. Add the READUNCOMMITTED locking hint to the query. Add the UPDLOCK locking hint to the query. Set the transaction isolation level to SNAPSHOT. Set the transaction isolation level to REPEATABLE READ. Answer: A QUESTION 76 You need to allow the new version of the application to run from the remote offices. All SQL Server 2005 features must also be available through this connection. What should you do? Use the System.Data.OleDb namespace to connect to SQL Server. Use the System.Data.SqlClient namespace to connect to SQL Server. Create an HTTP endpoint on SQL Server and configure the client computers to connect to the endpoint. Create a Web service on another computer, and configure this computer to connect to SQL Server. Configure the client computers to connect to the Web service. Answer: B QUESTION 77 You need to implement a solution to the transactions that add and modify rows based on the CustomerID column to fulfill the technical requirements. What should you do? Implement the repeatable read transaction isolation level. Implement the snapshot transaction isolation level. Implement the serializable transaction isolation level. Implement the read committed transaction isolation level. Answer: B QUESTION 78 You need to design the process for deleting customer accounts to fulfill the business requirements. What should you do? Implement an AFTER delete trigger on the Customers table that deletes orders and invoices related to the customer when the customer is deleted. Implement cascading deletes for the foreign keys from the Orders and Invoices tables to the Customers table. Design a stored procedure that uses a cursor to issue DELETE statements that delete each order and invoice related to the customer account and then deletes the customer account. Design a stored procedure that uses a cursor to run stored procedures that delete each order and invoice related to the customer account and then deletes the customer account. Answer: B Topic 9, Certkiller .com, Scenario BACKGROUND Company Overview Certkiller .com is a nonprofit organization that provides research support to many organizations across the United States. Planned Changes Surveys are conducted at different times of the year for various customers. A new table named AnnualSurvey will be added to the Customer database. This table will hold the results from only the most recent annual survey for each customer. This table will have a SurveyDate column that will hold the date the survey was given. Rows should not be inserted in this table if the survey was conducted more than 12 months ago. The SurveyDate column should not be modified to a date of more than 12 months ago at any time. Problem Statements The Customer database is experiencing a large amount of locking contention. Employees have been instructed to report when applications that are using the Customer database appear to fail or do not return data in a timely manner. Employees should leave the application running when they contact the database administrator so that troubleshooting can be performed. Employees report slow response times from a database named Contractor. EXISTING ENVIRONMENT Database Servers and Databases Certkiller .com has the servers and databases shown in the following table. Additional Server Information All database servers run Windows Server 2003 and SQL Server 2005. Certkiller 3 is also used to run other non-SQL processes. These processes are scheduled and run locally on Certkiller 3. Engineering Database Engineers have permissions to create objects in the database to support their current needs. They do not have permissions to remove any objects that they did not create. Parts Database Engineers have permissions to create objects in the database to support their current needs.They do not have permissions to remove any objects that they did not create. Parts Database The Parts database is extremely normalized.Most of the common queries require joining multiple tables. The documentation for the Parts database is very poor. Changes can be made to the objects in the Parts database. Part numbers and other codes related to the parts in the Parts database are currently being stored in columns with the char(100) data type. These codes are assigned manually by the part manufacturers of the engineering team.Future part numbers and codes will not contain more characters than the existing part numbers and codes. Performance Queries against the Parts database are performing very slowly. Database maintenance jobs rebuild all indexes every weekend. Therefore, index fragmentation does not appear to be the cause of the slow performance. On a regular basis, during standard production hours ,the company runs a SQLTrace that uses the Tuning template on each database server. he resulting trace file is then saved as a baseline for all database optimization. BUSINESS REQUIREMENTS General Application Solution Requirements When a contract is added to the Customer database for a new customer, the front-end application will request all necessary information for the new customer and the new contract. The first contract for a customer must not be added to the Customer database without the successful addition of the customer. Any error messages that are generated when a new customer is added should be reported to and handled by the front-end application.Over time,a customer might have many contracts. Customers with contracts in the Contract table must not be deleted from the Customers table, and their customer ID must not be changed. TECHNICAL REQUIREMENTS Performance Whenever possible, the solution that uses the fewest rollbacks should be used. Maintainability All object creations,deletions,and modifications in the Engineering database should be logged to a table named EngLogging.This logging should not run for any other databases on the server and should require a minimal amount of development time.The script that is used to create the EngLogging table is shown in the following code segment. USE Engineering CREATE TABLE EngLogging (LogID int IDENTITY PRIMARY KEY,LoginName varchar(50),MessageBody XML) Topic 9, Certkiller .com (6 Questions) QUESTION 79 You notice that some of the performance problems on the Parts database are due to a high number of pages being read for many of the most common queries. You need to find out the optimal sizes for the part number and code columns in the tables that are used in the most common queries. What should you do? For each table and part number or code column in the Parts database, run the following SQL statement:SELECT MIN(ColumnName) FROM TableNamewhere TableName is the actual table name and ColumnName is the actual column name. For each table and part number or code column in the Parts database, run the following SQL statement:SELECT COUNT(ColumnName) FROM TableName where TableName is the actual table name and ColumnName is the actual column name. For each table and part number or code column in the Parts database, run the following SQL statement:SELECT MAX(ColumnName) FROM TableName where TableName is the actual table name and ColumnName is the actual column name. For each table and part number or code column in the Parts database, run the following SQL statement:SELECT MAX(LEN(ColumnName)) FROM TableName where TableName is the actual table name and ColumnName is the actual column name. Answer: D QUESTION 80 You need to find out what is causing the slow response times to the Contractor database. Because the hardware on Certkiller 3 should be sufficient for the Contractor database and because regular database maintenance is performed, you want to investigate what else could be causing the problem. Which tool should you use? SQL Server Profiler System Monitor Network Monitor Database Engine Tuning Advisor Answer: B QUESTION 81 You need to develop a strategy that will add all required data to the EngLogging table. You must ensure that only required data is added to the EngLogging table. What should you do? Use the DDL_DATABASE_LEVEL_EVENTS event group, specifying ALL SERVER as the scope. Use the DDL_DATABASE_LEVEL_EVENTS event group, specifying DATABASE as the scope. Set up event notifications for the DDL_TABLE_VIEW_EVENTS event group, specifying DATABASE as the scope. D. Use the DDL _AUTHORIZATION_SERVER_EVENTS event group, specifying ALL SERVER as the scope. Answer: B QUESTION 82 You need to optimize the indexing strategy for the Parts database. You must ensure that index maintenance is minimized. What should you do? Create clustered indexes on all tables that are queried by SELECT statements that use the ORDER BY clause. Create the indexes on all columns that are included in any ORDER BY clauses. Create indexes on all primary key constraint fields that are used in the common queries. Create indexes on all foreign key constraint fields that are used in the common queries. Add nonclustered indexes to cover all queries that are issued against the database. Answer: C QUESTION 83 You are investigating the performance problems on the Parts database. You need to find out whether inefficient queries are part of these problems. You need to troubleshoot the longest-running queries first. What should you do to find these queries? Use the Windows System Monitor to examine the SQLServer:Transactions object. Use the Windows System Monitor to examine the SQLServer:Wait Statistics object. Use SQL Server Profiler. Use the most recent existing trace for Certkiller 2 and change the trace properties to group by application. Use SQL Server Profiler. Use the most recent existing trace for Certkiller 2 and change the trace properties to group by duration. Answer: D QUESTION 84 You need to design a strategy that will support the front-end application that adds new contracts for new customers. You need to fulfill all requirements. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) On the Contract table, create a foreign key constraint that references the Customer table. On the Customer table, create a foreign key constraint that references the Contract table. On the Contract table, create a check constraint to check for a valid customer ID. Create a stored procedure that will perform the following actions:Add the new contract information.Add the new customer information. Check for errors. If the new customer insert fails, send an error message to the front- end application and roll back the insert into the Contract table. If there are no errors, pass relevant data to the front-end application. E. Create a stored procedure that will perform the following actions:Add the new customer information. Check for errors.If errors occur, pass all relevant data or error messages to the front- end application. If no errors occur, use the new customer ID that was generated during the insert into the Customer table to add the new contract information to the Contract table. Answer: A,E Topic 10, City Power & Light, Scenario BACKGROUND Company Overview City Power & Light is a regional electricity provider. Planned Changes The data for customer billing is sent to a third -party billing company, which is responsible for printing and mailing bills and managing customer accounts. City Power & Light plans to create an online, self-service billing and payment Web site named BAP for its customers. EXISTING ENVIRONMENT Existing Application Environment The current City Power & Light metering and recording system is named MRS and is based on SQL Server 2000.Meter readings are taken and stored on a daily basis. The City Power & Light power generation recording system records power generation information on a minute-to-minute basis for all of its power generation plants. BUSINESS REQUIREMENTS General Application Solution Requirements All data for the BAP Web site will be hosted on a SQL Server 2005 database. City Power & Light wants to expose an interface to the BAP Web site for use by its energy trading partners. This interface will provide read-only access for the partners to facilitate research on user energy-consumption patterns. The BAP interface must access sensitive data; but it must not grant elevated permissions to the users of the interface. If any permission sets are changed, the changes should be in effect for the shortest duration possible and should not provide any additional rights. The BAP interface must prevent SQL injection attacks and overflow errors. When an error occurs in a stored procedure, the following information must be logged: line mumber, message, procedure name ,severity ,and state. As part of the BAP Web site, a report named CustomerUsage must be created for a specified customer. The report displays kilowatt-per-hour usage for a given date range. The UsageDate column should be used for the date filtering. As part of the BAP application, when a customer service representative is assisting a customer, The representative must be able to query the database by LastName, FirstName, StreetAddress, and PostalCode columns. For this query, a stored procedure named CallCenterCustomerLookup must be developed. All parameters to the CallCenterCustomerLookup stored procedure are optional except LastName. However, StreetAddress cannot be specified unless FirstName has been specified. In addition, PostalCode cannot be specified unless StreetAddress has been specified. A stored procedure named BillingByPostalCode must be developed that will accept a start and end PostalCode parameter and return a billing list for all accounts within those PostalCode parameters. A stored procedure named PlantGeneration must be developed that will return some or all of the electricity generation data for a given generation plant. In most instances, only the current month's data will be retrieved. Performance Performance testing must be executed before the BAP system goes live. Performance metrics at the server, database,a nd operating system levels must be captured. While performing user acceptance testing of the MRS, users report that the application is performing poorly. The BAP database has the options set sa shown in the following table. TECHNICAL REQUIREMENTS There are no additional technical requirements. Topic 10, City Power & Light (7 Questions) QUESTION 85 You are executing performance testing on the BAP application. You discover that the system is not performing according to requirements. In addition, you discover that there is excessive disk activity. You need to find out which stored procedures are causing the excessive disk activity. Which Transact-SQL statement should you run prior to executing each stored procedure that you suspect is performing poorly? SET STATISTICS IO ON SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET STATISTICS TIME ON Answer: A QUESTION 86 You are testing the PlantGeneration stored procedure. When you query for the current months data, query performance is exceeding 30 seconds. You need to correct the performance problem. What should you do? Create a federation of database servers, and partition the data by date. Create a distributed partitioned view across multiple instances of SQL Server. Distribute the data by date. Create a view on the data, and order by date. Create a partitioned table for the data, and partition the data by date. Answer: D QUESTION 87 You are designing a stored procedure that will be exposed to the companys energy trading partners. These partners are assigned minimal permissions in the BAP database. This procedure contains logic that constructs dynamic SQL that requires elevated permissions. You must provide the appropriate permissions. What should you do? Add WITH EXECUTE AS OWNER to the procedure definition. Add SETUSER before the dynamic SQL statement. Add EXECUTE AS CALLER before the dynamic SQL statement. Add REVERT after the dynamic SQL statement. Answer: A QUESTION 88 You are attempting to identify the query performance problems that are being reported by the testers of the MRS application. You need to identify the problem queries. Which tool should you use? SQL Server Profiler Database Engine Tuning Advisor SQLDiag utility dta utility Answer: A QUESTION 89 You are designing the CustomerUsage report. During testing, you discover that the report is taking 30 seconds or longer to complete. You need to resolve the performance problem. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.) Create a clustered index on the CustomerID column. Create a clustered index on the UsageDate column. Filter the results by adding BETWEEN to the WHERE clause. Filter the results by adding a GROUP BY clause. Answer: B,C QUESTION 90 You create the BillingByPostalCode stored procedure by using the following Transact-SQL code. CREATE PROCEDURE BillingByPostalCode (@BeginPostalCode char(5), @EndPostalCode char(5)) AS SELECT AddressLine1, AddressLine2, City, StateID, PostalCode FROM Address WHERE PostalCode BETWEEN @BeginPostalCode and @EndPostalCode During performance testing, you discover that the performance of this stored procedure is not fulfilling requirements. You need to optimize this stored procedure. Which optimization strategy should you use? Create a nonclustered index on PostalCode. Create a nonclustered index on PostalCode, and use the INCLUDE clause to include all other columns in the SELECT statement. Create an indexed view on the PostalCode and City columns. Create a clustered index on the PostalCode, AddressLine1, AddressLine2,City columns. Answer: B QUESTION 91 You are troubleshooting a slow-running query in the BAP database. You confirm that appropriate indexes exist for this query. You examine the graphical execution plan for this query. You notice that some of the text is displayed in red. You need to correct the performance problem. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.) Execute sp_updatestats. Add VARYING = OUTPUT to the query. Set Auto Create Statistics to True. Set Auto Update Statistics to True. Add WITH RECOMPILE to the query. Answer: A,C,D