You are a database developer. You design a database solution by using SQL Server 2008.
Your company has offices in Europe, Asia, North America, and Africa. The company data is
updated for each region after business hours. The queries used by reports on the database
are blocked when the data is being updated. Occasionally, the data is updated
simultaneously for the Africa and Europe regions. You need to ensure maximum
concurrency for the database by using minimum possible system resources. Which isolation
level should you use?

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a stored procedure that is created by using the following DDL
code segment. You discover that the UPDATE statement occasionally throws an exception
that causes the entire transaction to roll back. You need to ensure that when the UPDATE
statement causes an exception, SP1 performs the following tasks: • The INSERT statement
is committed, • All data is in a consistent state. • The Transaction count is equal to 0 after
execution completes. What should you do?

You need to recommend a solution that will enable two or more application sessions to
share the same transaction and locks. The solution must enable the applications to work on
the same data without creating lock conflicts. What should you recommend?

A new data analytics application is being implemented in your organization. Users will run a
process that compares result sets before and after executing complex data modifications.
The data will always be queried by using the same conditions. Some tables updated by the
processes will not need to be compared. Your need to design a locking strategy for the
process that meets the following requirements: • Enables other processes or users to
modify tables that are not being compared • Prevents other processes from performing data
manipulation language (DML) activity on the tables that are being compared What should
the strategy include?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain a view. The view will contain a computed column that will
be indexed. The computed column will use a user-defined scalar function. The function will
perform complex mathematical manipulations using random generated float numeric values.
You need to identify the appropriate function to use to generate random values. Which
function should you use?

You are designing a table to store customer data. The table will contain a column to store
the e-mail addresses of the customers. You need to recommend a solution to store the
e-mail addresses. The solution must meet the following requirements: • E-mail addresses
must contain the @ symbol • E-mail addresses must be validated by using a regular
expression • E-mail addresses must contain a top-level domain of .com, .org, or .edu What
should you recommend?

You have a table named Sales that contains the data listed in the following table. You need
design a query that retrieves a list that contains all sales people that have top three values
from the TotalSales column. Which query should you design?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain a table to stage data from an external source. The imported
data will contain a field named TransactionTime. The TransactionTime field will use the
hh:mm:ss.[nnn] format (such as 12:55:30.123). You need to select the appropriate data type
for the TransactionTime field. You need to achieve this goal by incurring the minimum
amount of storage cost. Which data type 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 that has two partitions. The first partition contains the
current data, and the second partition contains historical data. There is a high frequency of
data manipulation on the current data. The testers report that they are frequently
unsuccessful in querying the historical data only. You discover that locks are being
escalated to the table-level lock. You need to ensure that without modifying the SELECT
statements, you enable users to query the historical data. You want to achieve this goal by
using the minimum amount of administrative effort and minimally affecting other queries.
What should you do?

You are designing a new database. The tables of the database will be replicated to multiple
offices. You need to design the tables to meet the following requirements: • A row identifier
must be set when a row is created • The row identifier must be unique across the entire
organization What should you recommend?