You intend to use SQL Server 2008 instance to create a solution. You create a new database. In the database, a table is used to store Microsoft Office documents. You require that the snapshots of the database are used and the documents are part of the database backup. The documents have to be stored by using an suitable data type. So which data type should you use?

You intend to import data from an external source into a table. You must make sure that the tasks below are achieved:
* The rows that fail the foreign key constraints during import are inserted into a separate table.
* Even if the import encounters rows that fail foreign key constraints, it is successfully completed.
So what should you do?

You have a database that has 20 large tables. All the tables have qualified indexes. As the tables grow, you discover that queries that contain JOIN statements execute more slowly.
You need to recommend a solution to decrease the query response time and the I/O. The solution must minimize hardware costs. What should you recommend?

You are designing a table that will store transactions for an online retailer. You anticipate that the online retailer will have an average of 500,000 transactions per day. You need to design a solution for data aging and archiving. The solution must minimize the amount of time it takes to return query results for active data. What should you implement?

You are designing a database that will be used for reporting purposes. You need to minimize the data storage requirements and improve the application response time. What should you recommend?

You are a database developer. You plan to design a database solution by using SQL Server 2008. You plan to design a complex multi-statement stored procedure in the following manner.

CREATE PROCEDURE Sales.GetCustomerActivity
@StartDate datetime
AS
SELECT order_id, order_date, customer_id
FROM Sales.Orders
WHERE order_date >= @StartDate
…

On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute. You discover that this degradation is caused by the first statement in the stored procedure. You need to ensure that the stored procedure is consistently executed in the minimum possible time. What should you do?

You have a table that contains 5 million rows. The table has a clustered index. You plan to add an additional index on a column that contains 80 percent null values. The column is used extensively in WHERE clauses. You need to implement an index strategy for the column. The solution must minimize the amount of storage space required for the index. Which type of index should you use?

You have a stored procedure that uses a cursor. The stored procedure updates several related tables. You discover that the stored procedure runs slowly and uses a significant amount of resources on the database server. You need to recommend changes to the stored procedure to meet the following requirements:
* Minimize execution time
* Minimize development effort
* Minimize server resource requirements
What should you recommend?

Users frequently update millions of rows in a table at a time. Users report that it takes a long time to update the rows. You need to recommend a solution to reduce the time it takes to update the rows. The solution must be developed in the minimum amount of time. 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 named Policies. The table contains information about 100 million insurance policies. A complex stored procedure executes daily to calculate the risk amount of each policy and stores the information in the table. When the stored procedure is executed, users experience poor performance and query time-out errors. The queries used in the stored procedure are optimized for performance. You need to ensure that the disruption to users is minimal while the stored procedure is being executed. What should you do?