Ensurepass

QUESTION 101

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?

 

A.

Create a stoplist that contains the product descriptions.

B.

Create a nonclustered index and include the product description.

C.

Create filtered indexes on the product description.

D.

Implement Full-Text Search.

 

Correct Answer: D

 

 

QUESTION 102

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?

 

A.

unique nonclustered

B.

clustered

C.

nonunique covering

D.

Full-Text-Search

 

Correct Answer: B

 

 

QUESTION 103

You have an execution plan as shown in the exhibit. (Click the Exhibit button.)

 

70-451-demo-17

 

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?

 

A.

Include all the columns of the table in the existing nonclustered index.

B.

change the order of the columns in the existing nonclustered index.

C.

Add a unique nonclustered index to Col1 and Col2.

D.

Remove the nonclustered index.

 

Correct Answer: C

 

 

QUESTION 104

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?

 

A.

Add computed columns that have persisted values to the table that contains the aggregate values

on a per row basis.

B.

Create an indexed view in which each aggregate function is computed.

C.

Add computed columns to the table that contains the aggregate values on a per row basis.

D.

Create a view in which each aggregate function is computed.

 

Correct Answer: B

 

 

QUESTION 105

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?

 

A.

Transactions that have the READ UNCOMMITTED isolation level

B.

Transactions that have SERIALIZABLE isolation level

C.

a MERGE statement that uses a READCOMMITTEDLOCK table hint

D.

a MERGE statement that uses the OUTPUT clause

 

Correct Answer: D

 

 

QUESTION 106

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?

 

A.

a temporary table

B.

a derived table

C.

a table variable

D a common table expression (CTE)

 

Correct Answer: C

 

 

QUESTION 107

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?

 

A.

Rebuild all indexes.

B.

Use the SET STATISTICS PROFILE ON statement.

C.

Use the SET STATISTICS XML ON statement.

D.

Update statistics on the appropriate tables.

 

Correct Answer: D

 

 

QUESTION 108

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?

 

A.

a WHILE loop that contains a single DELETE statement

B.

multiple TRUNCATE TABLE statements

C.

a single transaction that contains multiple DELETE statements

D.

an IF statement that contains a single DELETE statement

 

Correct Answer: A

 

 

QUESTION 109

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?

 

A.

WITH (NOWAIT)

B.

sp_who

C.

OPTION (MERGE JOIN)

D.

sp_lock

 

Correct Answer: A

 

 

QUESTION 110

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?

 

A.

set the LOCK_ESCALATION option of the table to AUTO.

B.

Add the PAGLOCK hint to the UPDATE statement in the procedure.

C.

Disable the LOCK_ESCALATION option of the table.

D.

Add the TABLOCK hint to the UPDATE statement in the procedure.

 

Correct Answer: D

 

Instant Access to Download Latest Complete Collection of Microsoft MCITP 70-451 Real Exam

Try Microsoft MCITP 70-451 Free Demo

Comments are closed.