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 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 create a database by using SQL Server 2008 in an
enterprise environment. The database contains two stored procedures named ModifySales
and RetrieveSalesTrend. The ModifySales stored procedure uses a transaction that
updates a table named SalesOrders. The RetrieveSalesTrend stored procedure retrieves
and aggregates data from the SalesOrders table for a sales trend analysis report. Both
stored procedures are executed frequently each day. Users report a considerable wait time
while they run the sales trend analysis report. You need to ensure that sales trend analysis
report runs as quickly as possible. What should you do?

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 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 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 develop solutions by using SQL Server 2008 in an
enterprise environment. An application contains two stored procedures. The tasks
performed by the stored procedures are as shown in the following table. You discover that
the procedures occasionally throw foreign key violation errors. IncludeDetails throws an
error when it inserts records into the ProductInfo table. ImportNewProducts throws an error
when it inserts records into the ProductCurrentPrice table. You need to ensure that the
following requirements are met: ·If an error occurs in the INSERT statement of ProductInfo,
records inserted into ProductHeader and ProductCurrentPrice are committed. ·If an error
occurs in the INSERT statement of ProductCurrentPrice, all transactions are rolled back.
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 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 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?