You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a large non-partitioned table with 4 million rows. You write a
stored procedure that updates approximately 4,000 rows in the table. The stored procedure
is scheduled to run only during peak hours. The stored procedure runs frequently. Users
report performance issues. You discover that when the stored procedure runs, it results in
memory contention because of a high number of locks placed on the table. You need to
ensure that the SQL Server 2008 server places the minimum possible number of locks on
the table while the stored procedure is being executed. What should you do?

You need to design a solution to guarantee that a stored procedure is part of every
transaction. What should you do?

You have a line-of-business application. The application generates reports based on data
from database v iews. U sers report that i t t akes a l ong t ime t o g enerate reports. Y ou r
un a S QL Server Profiler trace and discover that the application is generating many
blocking processes. You need to minimize the number of blocking processes. You must
achieve this goal without modifying the statements that the application sends to the server.
What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. You are writing a query that returns XML data from a table in a database. You need to
ensure that your result set is encapsulated within a root element. What should you do?

You u se S QL S erver 2 008 t o d esign a d atabase t hat w ill h old i ncoming XML r
esponses f or a n Electronic Data Interchange system. You have the following requirements:

• The database is scalable. • The data is accessible to heterogeneous platforms. • The
database stores various types of reports from multiple sources. • The database stores large
amounts of data. • The solution allows search by keywords. • Full text search is not enabled
in the database. You need to design the database to meet these requirements. What should
you do?

You need to develop a stored procedure strategy that will manipulate temporary result sets
to meet the following requirements: • Stored procedures must receive result sets as
parameters • The strategy must be developed by using the minimum amount of effort The
result sets will contain less than 1,000 rows. What should you use?

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 string manipulations. You need to identify the appropriate function to use.
What should you do?

You are developing a security policy for the SQL Server developers in your organization.
You need to create a security strategy to protect Transact-SQL applications from SQL
injection attacks. What should you include in the strategy?

You are a database developer. You plan to design a database solution by using SQL Server
2008. Your company operates a fulfillment warehouse. The company has the following
business requirements: • The packing of items to fulfill orders is processed as quickly as
possible. • When the product picker indicates that a box is full, the exact items list of the
content and invoice are made available. You plan to design a Transact-SQL user-defined
function. The function must perform the following tasks: • Accept a list of value pairs named
ProductID and Quantity as input. • Retrieve the product price information from the
ProductPrice table. • Calculate the total price for each product. • Calculate the total price for
all products. • Return the detailed and aggregated totals. You need to design the function to
meet these requirements. You want to achieve this goal by using the minimum amount of
coding effort. What should you do?

You have a table named Sales that lists all of the products sold by your company. The
Sales table contains a column named UnitsSold. UnitsSold contains the total number of
units sold of each product. Each product appears in only one row. You have the following
view: You discover that the view does not return a list of the top 100 selling products. You
need to ensure that the view returns a list of the top 100 selling products. Which SQL
statement should you use?