Microsoft-Upgrade Transition Your MCITP SQL Server 2005 DBD to MCITP SQL Server 2008 DBD Set 2

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database application has a table named Transactions that contains millions of rows. The table has multiple columns that include transaction_id and transaction_date. There is a clustered index on the transaction_id column. There is a nonclustered index on the transaction_date column. You discover that the following query takes a long time to execute. SELECT transaction_id, transaction_date, transaction_notes FROM transactions WHERE transaction_type_id ='FXO' AND transaction_date between @start_date and @end_date The summary of the execution plan is as shown in the following code segment. |--Filter(WHERE:([transactio n_type_id]='FXO') |--Nested Loops(Inner Join) |--Index Seek(OBJECT:([transactions]. [nc_transactions_transaction_date]) |--Clustered Index Seek(OBJECT:([transactions]. [PK_transactions_transaction_id]) You need to ensure that the query retrieves data in minimum possible time. What should you do?

You have the following XML document that contains Product information. DECLARE @prodList xml =' ... '; You need to return a list of products that contains the Product Name, Category, and Price of each product. Which query should you use?

You need to capture the execution plan for a query. Which statement should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain information on retail sales transactions of more than 500 stores. The marketing department uses the solution to analyze daily sales patterns for each store.Users report that the solution takes a long time to retrieve the required data. You need to ensure that the solution provides results in the minimum possible time. What should you do?

You are using SQL Server Profiler to gather deadlock information. You need to capture an XMLdescription of a deadlock. Which event should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain three tables.The structure of the three tables is as shown in the following table. You need to minimize disk space usage without altering the data types in thetables of the database. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table that has a column definedas a smallint data type. The table is partitioned on the basis of the smallint column. The partition function, named MyRangePF1, is a range-right function that has boundaries of 100 and 1,000. The table must be altered to contain the following partitions: < 100 >= 100 and < 400 >= 400 and < 700 >= 700 and < 1000 >= 1000 You need to alter the partition function to provide the required partitions. Which code fragment should you use?

Your database is 5GB and contains a table named SalesHistory. Sales informationis frequently inserted and updated. You discover that excessive page splitting is occurring. You need to reduce the occurrence of page splitting in the SalesHistory table. Which code segment should you use?

You are a database developer. You plan todesign a database solution by using SQL Server 2008. A f requently used query takes very long to execute. You discover that the query frequently uses full-table scans instead of indexes. This causes other queries that modify the table to be blocked. The indexing strategy on the underlying tables that the query uses can change. You need to design a solution that performs the following tasks: Removes fulltable scans Allows the query optimizer to select the appropriate index. What should you do?

You use SQL Server 2008 to design a database that will hold incoming XML responses for an EDI system. You have the following requirements: The data is accessible to heterogeneous platforms. The database stores various types of reports from multiple sources. The solution allows search by keywords. The database stores large amounts of data. The database is scalable. You need to design the database to meet the given requirements. What should you do?