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

You are a database developer. You provide solutions by using SQL Server 2008 in an enterprise environment. Your online transaction processing (OLTP) database contains a tabled named SalesOrders. Your data warehouse contains a table named factBuyingHabits. The factBuyingHabits table has no indexes. You need to synchronize data between the two tables on a weekly basis. The synchronization process has the following requirements: New records in the SalesOrders table are inserted in the factBuyingHabits table. When a record is modified in the SalesOrders table,the modificationis updated in the factBuyingHabits table. Records that are deleted from the SalesOrders table are also deleted from the factBuyingHabits table. You need to design an appropriate synchronization solution.You want to achieve this goal by using minimum amount of coding and administrative efforts. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a table named Products. The database has two stored procedures named ModifyProduct and RetrieveProducts. ModifyProduct updates a single row in the Products table. RetrieveProducts returns all rows from the Products table. RetrieveProducts is used by a report.Users who run the report experience contention problems. You discover that RetrieveProducts is being blocked by ModifyProduct. The report must not include rows that are currently being modified. You need to ensure that the report is executed as quickly as possible. Which locking hint should you use in RetrieveProducts?

Your database contains sales information formillions of orders. You need to identify the orders with the highest average unit price and an order total greater than 10,000. The list should contain no more than 20 orders. Which query should you use?

You have a table named Employee. You document your company's organizational hierarchy by inserting the EmployeeID of each employee's manager in the ReportsTo column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee's level in the hierarchy. You write the following code segment. (Line numbers are included for reference only.) 01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level) 02 AS ( 03 04 ) 05 SELECT EmployeeID, FullName, ManagerName, Level 06 FROM EmployeeList Which code segment should you insert a t line 3?

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 need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Which TransactSQL statement should you use?

You are a database developer. You plan to design a database solution by using SQL Server 2008. A stored procedurein a database uses a transaction to retrieve data from a table and produces aggregations. You must design a solution that meets the following requirements: Update operations cannot be performed on the retrieved data while the stored procedure is being executed. Insert operations in the table can be performed while the stored procedure is being executed. You need to ensure that the solution meets the requirements. What isolation level should you use?

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?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a table that will store customer data as XML data.The data supports an application that cannot be altered. You plan to prevent the following types of errors in the XML data. NULL values in the Customer Name field Non-numeric values in the Customer Telephone field. Invalid values in the Gender field You need to implement the plan without modifying the application. What should you do?

You have a table named Inventory. You open a Microsoft Windows PowerShell session at the following location by using the SQL Server Windows PowerShell provider. PS SQLSERVER:\SQL\CONTOSO\DEFAULT \Databases\ReportServer\Tables\dbo.Inventory\Columns> Using the SQL Server Windows PowerShell provider, you need to query all the columns in the table. Which cmdlet should you use?