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

Ensurepass

QUESTION 61

You need to design a database solution that meets the following requirements:

 

– Supports different types of databases

– Provides reliable messaging between databases

– Provides data workload distribution across several databases

 

Which SQL Server component should you use?

 

A.

SQL Server Agent

B.

SQL Mail

C.

Notification Services

D.

Service Broker

 

Correct Answer: D

 

 

QUESTION 62

You have a table that contains 10 million rows. You need to design a query that returns a 100,000- row sample dataset. The solution must minimize the query’s execution time. The sample may contain inconsistent data. Which isolation level should you set to ON?

 

A.

READ COMMITTED SNAPSHOT

B.

READ COMMITTED

C.

REPEATABLE READ

D.

READ UNCOMMITTED

 

Correct Answer: D

 

 

QUESTION 63

You are a database developer. You design a database solution by using SQL Server 2008. Your company has offices in Europe, Asia, North America, and Africa. The company data is updated for each region after business hours. The queries used by reports on the database are blocked when the data is being updated. Occasionally, the data is updated simultaneously for the Africa and Europe regions. You need to ensure maximum concurrency for the database by using minimum possible system resources. Which isolation level should you use?

 

A.

READ COMMITTED

B.

REPEATABLE READ

C.

SNAPSHOT

D.

SERIALIZABLE

 

Correct Answer: A

 

 

QUESTION 64

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a stored procedure that is created by using the following DDL code segment.

 

70-451-demo-12

 

You discover that the UPDATE statement occasionally throws an exception that causes the entire transaction to roll back. You need to ensure that when the UPDATE statement causes an exception, SP1 performs the following tasks:

 

– The INSERT statement is committed,

– All data is in a consistent state.

– The Transaction count is equal to 0 after execution completes.

 

What should you do?

 

A.

– Add a TRY/CATCH block after the INSERT statement.

– Place the UPDATE statement inside the new TRY block.

– Add a ROLLBACK TRANSACTION statement inside the new CATCH block.

B.

– Add a TRY/CATCH block after the INSERT statement.

– Place the UPDATE statement inside the new TRY block.

– Add error-handling code inside the new CATCH block.

C.

– Add an XACT_ABORT ON statement to the first line of the procedure.

D.

– Add a SAVE TRANSACTION TR1 statement after the INSERT statement.

– Add a TRY/CATCH block after the SAVE TRANSACTION TR1 statement.

– Place the UPDATE statement inside the new TRY block.

– Add a ROLLBACK TRANSACTION TR1 statement inside the new CATCH block.

 

Correct Answer: D

 

 

QUESTION 65

You need to recommend a solution that will enable two or more application sessions to share the same transaction and locks. The solution must enable the applications to work on the same data without creating lock conflicts. What should you recommend?

 

A.

a stoplist

B.

bound sessions

C.

nested transactions

D.

savepoints

 

Correct Answer: B

 

 

QUESTION 66

A new data analytics application is being implemented in your organization. Users will run a process that compares result sets before and after executing complex data modifications. The data will always be queried by using the same conditions. Some tables updated by the processes will not need to be compared.

Your need to design a locking strategy for the process that meets the following requirements:

 

– Enables other processes or users to modify tables that are not being compared

– Prevents other processes from performing data manipulation language (DML) activity on the tables that are being compared

 

What should the strategy include?

 

A.

Set the transaction isolation level to SERIALIZABLE.

B.

Use a transaction that uses the WITH (NOLOCK) hint.

C.

Use a transaction that uses the WITH (HOLDLOCK) hint.

D.

Set the transaction isolation level to READ UNCOMMITTED.

 

Correct Answer: B

 

 

QUESTION 67

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 mathematical manipulations using random generated float numeric values. You need to identify the appropriate function to use to generate random values. Which function should you use?

 

A.

a deterministic Transact-SQL table-valued function

B.

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

C.

a deterministic common language runtim
e (CLR) scalar-valued function

D.

a nondeterministic Transact-SQL table-valued function to generate random values

 

Correct Answer: C

 

 

QUESTION 68

You are designing a table to store customer data. The table will contain a column to store the e-mail addresses of the customers. You need to recommend a solution to store the e-mail addresses. The solution must meet the following requirements:

 

– E-mail addresses must contain the @ symbol

– E-mail addresses must be validated by using a regular expression

– E-mail addresses must contain a top-level domain of .com, .org, or .edu

 

What should you recommend?

 

A.

Create an e-mail profile for the Database Mail SQL Server component.

B.

Use a CHECK constraint that uses a CLR function.

C.

Add a custom schema to the database.

D.

Use a non-persisted computed column.

 

Correct Answer: B

 

 

QUESTION 69

You have a table named Sales that contains the data listed in the following table.

 

70-451-demo-13

 

You need design a query that retrieves a list that contains all sales people that have top three values from the TotalSales column. Which query should you design?

 

70-451-demo-14

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: C

 

 

QUESTION 70

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a table to stage data from an external source. The imported data will contain a field named TransactionTime. The TransactionTime field will use the hh:mm:ss.[nnn] format (such as 12:55:30.123).

You need to select the appropriate data type for the TransactionTime field. You need to achieve this goal by incurring the minimum amount of storage cost. Which data type should you use?

 

A.

time

B.

datetime2

C.

smalldatetime

D.

time (1)

 

Correct Answer: A

 

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

Try Microsoft MCITP 70-451 Free Demo