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

Ensurepass

QUESTION 121

You are designing a CLR stored procedure that will access a remote Web service. You need to ensure that the CLR stored procedure can access the remote Web service. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

 

A.

Grant EXECUTE on the CLR stored procedure.

B.

Implement a custom trust level policy.

C.

Configure the CLR stored procedure to use the SAFE permission set.

D.

Configure the CLR stored procedure to use the EXTERNAL_ACCESS permission set.

 

Correct Answer: BD

 

 

QUESTION 122

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table named Customer. The structure of the Customer table is as shown in the following table.

 

70-451-demo-19

 

The solution uses the following stored procedure to return customer details.

 

70-451-demo-20

 

Users report that the stored procedure takes a long time to execute. You need to optimize the performance of the stored procedure. What should you do?

 

A.

Recreate the primary key as a nonclustered unique index and build a clustered index on the

country_id column.

B.

Build a nonclustered index on the country_id column and use the INCLUDE clause to include the

customer_name column.

C.

Build a nonclustered index on the country_id, customer_name, and customer_id columns.

D.

Build a nonclustered index only on the country_id column.

 

Correct Answer: B

 

 

QUESTION 123

You are a database developer. You plan to design a database solution by using SQL Server 2008. A frequently used query takes very long to execute. You discover that the query frequently uses full- table scans instead of indexes. This causes other queries that modify the table to be blocked. The indexing strategy on the underlying tables that the query uses can change. You need to design a solution that performs the following tasks:

 

– Removes full-table scans

– Allows the query optimizer to select the appropriate index.

 

What should you do?

 

A.

Use the INDEX table hint.

B.

Use the INDEX(0) table hint.

C.

Use the NOEXPAND table hint.

D.

Use the FORCESEEK table hint.

 

Correct Answer: D

 

 

QUESTION 124

You are a database developer. You develop a database by using SQL Server 2008 in an enterprise environment. The database has a table named Sales.Inventory. The table is partitioned into four geographic regions. You update the Sales.Inventory table for each region by using the following stored procedure.

 

CREATE STORED PROCEDURE usp_Update

@RegionID tinyint

AS

UPDATE Sales.Inventory

SET Qty = T.CurrentQuantity

FROM Sales.Inventory I

JOIN Sales.TempData T ON I.ItemID = T.ItemID

AND I.RegionID = @RegionID;

 

The UPDATE statement locks the Sales.Inventory table when a single region is updated. You need to prevent the locking of the Sales.Inventory table when a single region is updated. What should you do?

 

A.

Modify the usp_Update stored procedure to use the NOLOCK table hint for the UPDATE statement.

B.

Modify the usp_Update stored procedure to use the SERIALIZABLE table hint for the UPDATE statement.

C.

Run the following Transact-SQL statement.

ALTER TABLE Sales.Inventory SET LOCK_ESCALATION = AUTO

D.

Run the following Transact-SQL statement.

ALTER TABLE Sales.Inventory SET LOCK_ESCALATION = TABLE

 

Correct Answer: C

 

 

QUESTION 125

You are a database developer. You plan to design a database solution by using SQL Server 2008. A stored procedure in a database uses a transaction to retrieve data from a table and produces aggregations.

You must design a solution that meets the following requirements:

 

– Update operations cannot be performed on the retrieved data while the stored procedure is being executed.

– Insert operations in the table can be performed while the stored procedure is being executed.

 

You need to ensure that the solution meets the requirements. What isolation level should you use?

 

A.

SERIALIZABLE

B.

READ COMMITTED

C.

REPEATABLE READ

D.

READ UNCOMMITTED

 

Correct Answer: C

 

 

QUESTION 126

You have a database that contains two tables. Both the XACT_ABORT database option and the IMPLICIT_TRANSACTIONS database option are set to OFF. You need to update both tables. If an update fails on either table, neither table should be updated. What should you do?

 

A.

Use a transaction.

B.

Change the isolation level.

C.

Use the TABLOCK query hint.

D.

Use the UPDLOCK query hint.

 

Correct Answer: A

 

 

QUESTION 127

You use SQL Server 2008 to design a database that will hold incoming XML responses for an EDI system. You have the following requirements:

 

– The data is accessible to heterogeneous platforms:

– The database stores various types of reports from multiple sources.

– The solution allows search by keywords.

– The database stores large amounts of data.

– The database is scalable.

 

You need to design the database to meet the given requirements. What should you do?

 

A.

Use SQL Server 2008 tables to store data and include proper indexes.

B.

Use ANSI text files to store text reports, and use SQL Server 2008 tables to store numerical reports.

C.

Save reports in binary format in a file within a Windows folder. Save the path of the file in SQL Server

2008 tables.

D.

Store reports in XML format, and use SQL Server 2008 tables to store the data. Index the XML data to

improve performance.

 

Correct Answer: D

 

 

QUESTION 128

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a table that has two partitions. The first partition contains the current data, and the second partition contains historical data. There is a high frequency of data manipulation on the current data. The testers report that they are frequently unsuccessful in querying the historical data only. You discover that locks are being escalated to the table-level lock. You need to ensure that without modifying the SELECT statements, you enable users to query the historical data. You want to achieve this goal by using the minimum amount of administrative effort and minimally affecting other queries. What should you do?

 

A.

Use the ALTER TABLE … SET (LOCK_ESCALATION = AUTO) command.

B.

Move the historical partition to a read-only filegroup.

C.

Set the Server Startup Parameters to include -T1211.

D.

Set DEADLOCK PRIORITY to HIGH.

 

Correct Answer: D

 

 

QUESTION 129

You are designing a new database. The tables of the database will be replicated to multiple offices. You need to design the tables to meet the following requirements:

 

– A row identifier must be set when a row is created

– The row identifier must be unique across the entire organization

 

What should you recommend?

 

A.

Add a column to all the tables that have the hierarchyid data type.

B.

Add a column to all the tables that have a time data type. Configure the DEFAULT constraint to use

the GETDATE () function.

C.

Add a column to all the tables that have the identity property.

D.

Add a column to all the tables that have a uniqueidentifier data type. Configure the DEFAULT constraint

to use the NEWIDQ function.

 

Correct Answer: D

 

 

QUESTION 130

You need to design a table that will contain a monetary value. The value must support five digits to the right of the decimal point. Which data type should you choose?

 

A.

decimal

B.

bigint

C.

int

D.

money

 

Correct Answer: A

 

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

Try Microsoft MCITP 70-451 Free Demo