Microsoft-PRO Designing Database Solutions and Data Access Using Microsoft SQL Server 2008

You intend to design a complex multi-statement stored procedure in the manner below. CREATE PROCEDURE Sales.GetCustomerActivity @StartDate datetime AS SELECT order_id, order_date, customer_id FROM Sales.Orders WHERE order_date >= @StartDate ... You notice that sometimes the stored procedure executes slowly and this is caused by the first statement in the stored procedure. So what should you do to make sure that the stored procedure is always executed normally in the minimum possible time?

There's a table in a database. And the table has a column defined as a smallint data type. The table is partitioned based on the smallint column. The partition function is a range-right function that has boundaries of 100 and 1,000 and it is called SpecialPF. In order to make the table contain the partitions below, you have to alter the table. < 100 >= 100 and < 400 >= 400 and < 700 >= 700 and < 1000 >= 1000 The partition function has to be altered to provide the required partitions. Which code fragment should you use?

The database captures user interactions and supports a Web site. The Activity table of the User_Activity database stores these interactions. Data older than half a year is archived to the Activity table of the Archive_Activity database. This Archive_Activity database is on a different instance of SQL Server 2008. The table below shows the structure of the Activity table. You intend to design a solution design a solution. The solution allows a single query to generate a report. User interactions for the last 12 months are summarized by the report. So what should you do to make sure that the solution is implemented?

Data from two storerooms are contained in the database in a table named Manufacures. The two storerooms are respectively called Storeroom1 and Storeroom2. And the database supports a warehousing application. A storeroom indicator field named storeroom_id is contained in the Manufacture table. Storeroom1 includes 275,000 items and Storeroom2 includes 55,000 items. A third-party application is used by the solutions. And the third-party application runs on SQL Server 2008 and uses a stored procedure that returns the storeroom inventory based on the warehouse_id parameter. You notice that when users query the storeroom1 inventory, users sometimes experience poor system performance. But the stored procedures cannot be modified in the application. So when the inventory of Storeroom1 is queried, what should you do to ensure the system performance?

There's a large table in the database. The table is updated not very often. A query is executed against the table by users. The execution of a complex calculation is required by the query. The calculation involves multiple columns for a given row. You notice that because the query is CPU intensive, the query has a bad performance. So what should you do to minimize the effect of this query on the server?

A database will contain 10 tables. Reports are generated by using the tables. The range of the data in the tables varies from 50,000 to 100,000 records. In the process that a query joins four tables, the problems below occur: It takes a quite long time for the query to execute. And besides this, the size of the tempdb database grows quickly. Now you have to analyze the execution plan to check the cause for the two problems. So what should you do to find out the most possible cause for the problems?

The database will contain a table. And the table includes a parent-child relationship to itself. Each child might also be a parent, and this relationship might exist up to 10 levels deep. Now you have to use a single Transact- SQL query to retrieve all levels. So what should you do?

The database application has a table named Deals. Millions of rows are contained in the table. The table has multiple columns. The columns include deal_id and deal_date. On the deal_date column, there is a nonclustered index. On the deal_id column, there is a clustered index. Look at the the following query: SELECT transaction_id, transaction_date, transaction_notes FROM transactions WHERE transaction_type_id = 'FXO' AND transaction_date between @start_date and @end_date You notice that the query above execute slowly. What should you do to make sure that the query retrieves data by using as little time as possible?

The database has a table named Selling which contains 10 million rows. SELECT s.sale_id, ... FROM Sales AS s JOIN Country AS c ON s.Country_id = c.Country_id AND c.Country_name = 'USA' You notice that it takes a long time for the query above to execute. The code segment below shows the summary of the execution plan. |--Hash Match(Inner Join, HASH:([s].[Country_id]) = ([c].[Country_id]) |--Clustered Index Scan(OBJECT:([Country].[PK_Country_Country_id] AS [c]) |--Clustered Index Scan(OBJECT:([Sales].[PK_Sales_Sale_id] AS [s])) What should you do to make sure that the query retrieves data as soon as possible?

Order data is stored by managers in a database. A list of customers is required for each manager. The list must be sorted in the descending order of the order amount. A query is created to generate the list at the end of each month. What should you do to make sure that the query executes as quickly as possible?