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?

You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. You plan to create a stored procedure that queries a sales table and produces forecast data. You do not have administrative permissions, and you are not the owner of the database. You have permissions to create stored procedures. Users will only have permissions to execute your stored procedures. You need to ensure that users can execute the stored procedures. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. You configure a database on a server to use a common language runtime (CLR). You need to create a CLR assembly that enables the CLR stored procedure to access environment variables available on the server. You also need to ensure that the CLR assembly has the minimum permissions assigned. What should you do?

You have a table that has 10 million rows. The table has a view that returns all of the rows. You discover performance degradation when you run an unfiltered view. You need to recommend a solution to replace the view. The solution must require that the returned data is filtered by a parameter. What should you use?