Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 3

Question - 15 Maximum mark : - 27

Home
All Exams
Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Online Mock Tests
Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 3

You create the BillingByPostalCode stored procedure by using the following Transact-SQL code. CREATE PROCEDURE BillingByPostalCode (@BeginPostalCode char(5), @EndPostalCode char(5)) AS SELECT AddressLine1, AddressLine2, City, StateID, PostalCode FROM Address WHERE PostalCode BETWEEN @BeginPostalCode and @EndPostalCode During performance testing, you discover that the performance of this stored procedure is not fulfilling requirements. You need to optimize this stored procedure. Which optimization strategy should you use?

Create a nonclustered index on PostalCode. ✘ Create a nonclustered index on PostalCode, and use the INCLUDE clause to include all other columns inthe SELECT statement. ✘ Create an indexed view on the PostalCode and City columns. ✘ Create a clustered index on the PostalCode, AddressLine1, AddressLine2,City columns. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are designing a stored procedure that will be exposed to the companys energy trading partners. These partners are assigned minimal permissions in the BAP database. This procedure contains logic that constructs dynamic SQL that requires elevated permissions. You must provide the appropriate permissions. What should you do?

Add WITH EXECUTE AS OWNER to the procedure definition. ✘ Add SETUSER before the dynamic SQL statement. ✘ Add EXECUTE AS CALLER before the dynamic SQL statement. ✘ Add REVERT after the dynamic SQL statement. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are testing the PlantGeneration stored procedure. When you query for the current months data, query performance is exceeding 30 seconds. You need to correct the performance problem. What should you do?

Create a federation of database servers, and partition the data by date. ✘ Create a distributed partitioned view across multiple instances of SQL Server. Distribute the data by date. ✘ Create a view on the data, and order by date. ✘ Create a partitioned table for the data, and partition the data by date. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You are executing performance testing on the BAP application. You discover that the system is not performing according to requirements. In addition, you discover that there is excessive disk activity. You need to find out which stored procedures are causing the excessive disk activity. Which Transact-SQL statement should you run prior to executing each stored procedure that you suspect is performing poorly?

SET STATISTICS IO ON ✘ SET SHOWPLAN_TEXT ON ✘ SET SHOWPLAN_ALL ON ✘ SET STATISTICS TIME ON ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to resolve the problems with the routine named Routine112. What should you do?

Use a separate connection for each update. ✘ Open a multiple active result sets (MARS) connection, use it for both the reader and the updates, and issuethe updates on the same thread as the loop. ✘ Disable connection pooling. ✘ Implement connection pooling. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to optimize the query against the ReservationRequests table to fulfill the technical requirements. What should you do?

Add the READPAST locking hint to the query. ✘ Add the READUNCOMMITTED locking hint to the query. ✘ Add the UPDLOCK locking hint to the query. ✘ Set the transaction isolation level to SNAPSHOT. ✔ Set the transaction isolation level to REPEATABLE READ. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to implement a solution to the transactions that add and modify rows based on the CustomerID column to fulfill the technical requirements. What should you do?

Implement the repeatable read transaction isolation level. ✘ Implement the snapshot transaction isolation level. ✘ Implement the serializable transaction isolation level. ✘ Implement the read committed transaction isolation level. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to allow the new version of the application to run from the remote offices. All SQL Server 2005 features must also be available through this connection. What should you do?

Use the System.Data.OleDb namespace to connect to SQL Server. ✘ Use the System.Data.SqlClient namespace to connect to SQL Server. ✘ Create an HTTP endpoint on SQL Server and configure the client computers to connect to the endpoint. ✘ Create a Web service on another computer, and configure this computer to connect to SQL Server.Configure the client computers to connect to the Web service. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

You need to design the retrieval of the airfares for the new version of the application to fulfill the business requirements. What should you do?

Design a routine for the client computers that refreshes the information every five minutes. ✘ Design a routine for the client computers that refreshes the information every 10 seconds. ✘ Use the SqlDependency.OnChanged event on the client computers to notify them when the informationchanges. Then, if the most recent refresh was more than five minutes ago, automatically refresh the data. ✘ Use the SqlDependency.OnChanged event on the client computers to notify them when the informationchanges. Then, automatically refresh the data. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

You need to design the process for deleting customer accounts to fulfill the business requirements. What should you do?

Implement an AFTER delete trigger on the Customers table that deletes orders and invoices related to the customer when the customer is deleted. ✘ Implement cascading deletes for the foreign keys from the Orders and Invoices tables to the Customerstable. ✘ Design a stored procedure that uses a cursor to issue DELETE statements that delete each order andinvoice related to the customer account and then deletes the customer account. ✘ Design a stored procedure that uses a cursor to run stored procedures that delete each order and invoicerelated to the customer account and then deletes the customer account. ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 3

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner