ou plan to deploy a new application. The application will perform the following operations: •
Create a new database • Back up the new database You need to configure a login to
support the deployment of the new application. The solution must ensure that the
application uses the most restrictive permissions possible. What should you do?

You have a server that runs SQL Server 2008. The server supports an online application.
New products are constantly being added to a database stored on the server. You identify
the following: • Searches often take too long • Users cannot search by using multiple criteria
• Users cannot adequately search product descriptions • Most queries that search the
product descriptions use “LIKE *%…%”‘ as the filter predicate You need to recommend a

search process that meets the following requirements: • Reduces the amount of time it
takes to return search results • Enables users to filter searches by using multiple criteria •
Enables users to search product descriptions • Minimizes administrative effort • Minimizes
development costs What should you do?

You review the execution plan for a query that contains a join. The execution plan contains
a RID Lookup. You need to reduce the I/O that is generated by the RID Lookup. Which
index should you create?

You have an execution plan as shown in the exhibit. (Click the Exhibit button.) The indexes
and keys for the table are as follows: • Unique index on Col1 and CoI2 • Primary key of
table not modifiable • Clustered primary key on Col1 and Col2 You need to recommend
changes to the table to minimize the response time for both queries. What should you do?

You have a server that runs SQL Server 2008 Enterprise. You have a query that contains

SUM, AVG, and MAX functions. You discover that the query takes a long time to execute.
You need to reduce the amount of time required to execute the query. What should you do?

You have production tables that are based on data from staging tables. You plan to create
stored procedures that will perform the following actions on the production tables: • Insert
new rows • Update existing rows • Ignore duplicate rows • Maintain data integrity • Create
an audit trail of all operations You need to develop a strategy for the planned stored
procedures to minimize locking operations. The solution must be developed by using use
the minimum amount of effort. What should you use?

You need to design a stored procedure that contains a transaction. If an error condition
causes the transaction to roll back, a temporary result set must be available to the query
that follows the failed transaction. Which temporary storage object should you use?

You are a database developer. You plan to design a database solution by using SQL Server
2008. Users report poor performance of a long-running query in a database. You fail to
detect the cause of poor performance when you examine the estimated execution plan. On
viewing the actual execution plan, you discover that several tasks are CPU-intensive. You
need to ensure that you can view an estimated execution plan containing the most likely
actions. You want to achieve this goal by incurring minimum possible CPU overhead. What
should you do?

You have a database that has 20 tables. The tables are not configured to have any
referential integrity. All tables have primary keys. Each table contains over 5 million rows.
Ten percent of the rows contain outdated information. You need to design a maintenance
process to delete all outdated rows. The solution must meet the following requirements: •
Minimize execution time • Minimize development effort • Minimize blocking other processes
• Prevent deletion failures from affecting the entire deletion process What should the design
include?

You are creating a new update query. You need to ensure that the query terminates if a
shared lock is encountered white the data is updating. What should you include in the
query?