New Updated Microsoft MCITP 70-451 Real Exam Questions and Answers Download 111-120

Ensurepass

QUESTION 111

You need to design a solution to guarantee that a stored procedure is part of every transaction.

What should you do?

 

A.

Enable implicit transactions.

B.

Enable Distributed Transaction Coordinator (DTC).

C.

Set the NOCOUNT option to ON.

D Set the XACT_ABORT option to ON.

 

Correct Answer: A

 

 

QUESTION 112

You have a line-of-business application. The application generates reports based on data from database views. Users report that it takes a long time to generate reports. You run a SQL 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?

 

A.

Change the isolation level of the database.

B.

Implement a database Service Broker.

C.

Create new database report views.

D.

Change the isolation level of each query.

 

Correct Answer: A

 

 

QUESTION 113

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?

 

A.

Use the RAW mode along with an ELEMENTS directive.

B.

Use the PATH mode along with a ROOT directive.

C.

Use the EXPLICIT mode along with a TYPE directive.

D.

Use the AUTO mode along with a ROOT directive.

 

Correct Answer: B

 

 

QUESTION 114

You use SQL Server 2008 to design a database that will hold incoming XML responses for an 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?

 

A.

Use SQL Server 2008 tables to store the data in a varchar (max) column.

B.

Use SQL Server 2008 tables to store the data in a varbinary (max) column with FILESTREAM option.

C.

Use SQL Server 2008 tables to store the data in a varbinar y(max) column.

D.

Use SQL Server 2008 tables to store the data using XML datatype.

 

Correct Answer: D

 

 

QUESTION 115

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?

 

A.

a common table expression (CTE)

B.

a view

C.

a user-defined table type

D.

a scalar function

 

Correct Answer: C

 

 

QUESTION 116

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?

 

A.

Use a nondeterministic Transact-SQL table-valued function.

B.

Use a deterministic Transact-SQL table-valued function.

C.

Use a nondeterministic common language runtime (CLR) scalar-valued function.

D.

Use a deterministic common language runtime (CLR) scalar-valued function.

 

Correct Answer: D

 

 

QUESTION 117

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?

 

A.

Require certificates for Service Broker communications.

B.

Parse input parameters to prevent the use of the following strings:

?;

?–

?/*

?*/

C.

Parse input parameters to prevent the use of the following strings:

?&

?@

?//

?++

D.

Disable Service Broker.

 

Correct Answer: B

 

 

QUESTION 118

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?

 

A.

Create a multistatement table-valued function that accepts an XML parameter.

B.

Create a multistatement table-valued function that accepts a table-valued parameter.

C.

Create an inline table-valued function that accepts an XML parameter.

D.

Create an inline table-valued function that accepts a table-valued parameter.

 

Correct Answer: B

 

 

QUESTION 119

You have a table that contains a string column. Most of the rows have the same value. You need to create a multistep stored procedure that meets the following requirements:

 

– Accepts a text input parameter

– Provides optimal performance for all input values

– Has only one step using the input parameter as a predicate

 

What should you use?

 

A.

sp_recompile

B.

WITH RECOMPILE

C.

sp_configure

D.

a RECOMPILE query hint

 

Correct Answer: D

 

 

QUESTION 120

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?

 

70-451-demo-18

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: C

 

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

Try Microsoft MCITP 70-451 Free Demo

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.