Which index should you create?
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 need to recommend a search process that meets the following requirements…?
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 need to optimize the statement
You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
Your investigation shows the root cause is a query against a read-only table that has a clustered
index.
The query returns the following six columns:
One column in its WHERE clause contained in a non-clustered index ·
Four additional columns
One COUNT (*) column based on a grouping of the four additional columns
You need to optimize the statement.
What should you do?
You need to recommend changes to the table to minimize the response time for both queries
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?
Which Transact-SQL query should you use?
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as
shown in the exhibit. (Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row having the CustomerId value
set to 1 in the following XML format.
Which Transact-SQL query should you use?
You need to reduce the amount of time required to execute the query
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 need to develop a strategy for the planned stored procedures to minimize locking operations
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?
Which Transact-SQL query should you use?
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as
shown in the exhibit. (Click the Exhibit button.)
You need to display rows from the Orders table for the Customers row having the Customerld value
set to 1 in the following XML format.
Which Transact-SQL query should you use?
Which temporary storage object 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?
Which isolation level should you use?
You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements.
There is high contention between readers and writers on several tables used by your transaction.
You need to minimize the use of the tempdb space. You also need to prevent reading queries from
blocking writing queries.
Which isolation level should you use?