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. A stored procedure in 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 are a database developer. You develop a database by using SQL Server 2008 in an enterprise environment. The database has a table named Sales.Inventory. The table is partitioned into four geographic regions. You update the Sales.Inventory table for each region by using the following stored procedure:

CREATE STORED PROCEDURE usp_Update
@RegionID tinyint
AS
UPDATE Sales.Inventory
SET Qty = T.CurrentQuantity
FROM Sales.Inventory I
JOIN Sales.TempData T ON I.ItemID = T.ItemID
AND I.RegionID = @RegionID;

The UPDATE statement locks the Sales.Inventory table when a single region is updated. You need to prevent the locking of the Sales.Inventory table when a single region is updated. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. A frequently 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 full-table scans
* Allows the query optimizer to select the appropriate index.
What should you do?

You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. You are creating a SQL Agent job that uses Transact-SQL to update data in two related databases on two different servers. You have the following requirements:
* The job can only execute once each evening.
* The databases on each server use the full-recovery model.
* Transaction log backups for the two databases occur at different times.
* The job uses transactions to ensure that in the event of an error, all updates are rolled back.
You need to ensure that when you restore a database on either server, the two databases are restored to a state that reflects the last time the job successfully executed. What should you do?

You have a stored procedure that is used to set up maintenance tasks. The stored procedure executes every night. The stored procedure contains three critical data manipulation language (DML) statements that operate against a single table. You need to prevent users from modifying any data in the table while the stored procedure executes. Which locking hint should you use?

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?

You are a database developer. You plan to create a database by using SQL Server 2008. A database contains a table named Sales. The Sales table contains customer order summary information. You create a stored procedure that uses a SELECT statement. At the moment of execution, the procedure must return a precise summation of the total sales for the current day. You need to use a query hint to prevent any data modification in the Sales table when the stored procedure is being executed. Which query hint should you recommend?

You need to configure a security solution for an application. The solution must meet the following requirements:
* The application must have access to tables in a database
* The tables must only be accessed through the application
* Database access must not require a password
What should you create?

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 modification is 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?