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

Ensurepass

QUESTION 51

You have a table that contains 5 million rows. The table has a clustered index. You plan to add an additional index on a column that contains 80 percent null values. The column is used extensively in WHERE clauses. You need to implement an index strategy for the column. The solution must minimize the amount of storage space required for the index. Which type of index should you use?

 

A.

clustered

B.

filtered

C.

nonclustered

D.

unique

 

Correct Answer: B

 

 

QUESTION 52

You have a table named Books that contains information about books. Books has the columns in the following table.

 

70-451-demo-9

 

You plan to create several queries that will filter on Title and ISBN. The queries will return values from Title, ISBN, and Description.

You need to recommend an indexing solution to meet the following requirements:

 

– Minimize the amount of time required to return the results of the planned queries

– Minimize the number of Indexes

 

What should you recommend?

 

A.

Create a nonclustered index on each column.

B.

Create a clustered index on Title, ISBN and Description as the key value.

C.

Create a clustered index on Title and ISBN and set the index fill factor to 75.

D.

Create a nonclustered index on Title and ISBN and include the Description column.

 

Correct Answer: D

 

 

QUESTION 53

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database supports a Web site and captures user interactions. These interactions are stored in the Activity table of the User_Activity database. Data older than six months is archived to the Activity table of the Archive_Activity database on a different instance of SQL Server 2008. The structure of the Activity table is as shown in the following table.

 

70-451-demo-10

 

You plan to design a solution that allows a single query to generate a report that summarizes user interactions for the last 12 months. You need to ensure that the solution is implemented. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A.

Create a partition function and a partition scheme.

B.

Modify the Activity tables to use the partition scheme.

C.

Move the archived data back to the User_Activity database.

D.

Create a view by using the UNION ALL clause to retrieve data from the two Activity tables.

E.

Create CHECK constraints on the two Activity tables to limit the values in the activity_date column to

an exclusive range.

 

Correct Answer: DE

 

 

QUESTION 54

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database supports a warehousing application and contains data from two warehouses in a table named Product. The Product table contains a warehouse indicator field named warehouse_id.

The two Warehouse B contains 55,000 items.

The solution uses a third-party application that runs on SQL Server 2008. The application uses a stored procedure that returns the warehouse inventory based on the warehouse_id parameter. Users report that occasionally, the system performance is unacceptable when the Warehouse A inventory is queried. You cannot modify the stored procedures in the application. You need to ensure acceptable performance when users query the inventory of Warehouse A. What should you do?

 

A.

Create a clustered index on the warehouse_id column.

B.

Create a nonclustered index on the warehouse_id column.

C.

Create a plan guide that sets the MAXDOP query hint to 1.

D.

Create a plan guide that uses the OPTIMIZE FOR clause for Warehouse A.

 

Correct Answer: D

 

 

QUESTION 55

You are a database developer. You plan to design a database solution by using SQL Server 2008. You plan to design a complex multi-statement stored procedure in the following manner.

 

CREATE PROCEDURE Sales.GetCustomerActivity

@StartDate datetime

AS

SELECT order_id, order_date, customer_id

FROM Sales.Orders

WHERE order_date >= @StartDate

 

On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute.

You discover that this degradation is caused by the first statement in the stored procedure. You need to ensure that the stored procedure is consistently executed in the minimum possible time.

What should you do?

 

A.

Run the EXEC sp_recompile GetCustomerActivity command.

B.

Create a plan guide to apply the OPTION (RECOMPILE) clause to the first statement.

C.

Modify the stored procedure by adding the WITH RECOMPILE clause.

D.

Replace the first statement in the stored procedure with the following Transact-SQL statement.

UPDATE STATISTICS Sales.GetCustomerActivity WITH RESAMPLE

 

Correct Answer: B

 


 

QUESTION 56

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain three tables. The structure of the three tables is as shown in the following table.

 

70-451-demo-11

 

You need to minimize disk space usage without altering the data types in the tables of the database.

What should you do?

 

A.

Implement row-level compression on all tables.

B.

Implement row-level compression on Table1 and page-level compression on Table2 and Table3.

C.

Implement row-level compression on Table2 and page-level compression on Table1 and Table3.

D.

Implement row-level compression on Table3 and page-level compression on Table1 and Table2.

 

Correct Answer: B

 

 

QUESTION 57

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a table that is stored as a heap. You perform page-level compression on the table. When data is added to the table by using BULK INSERT, the new pages are not compressed. You need to ensure page-level compression for all data pages when they are allocated to the table.

Which query hint should you use?

 

A.

PAG LOCK

B.

NOLOCK

C.

NOWAIT

D.

TABLOCK

 

Correct Answer: C

 

 

QUESTION 58

You are a database developer. You plan to design a database solution by using SQL Server 2008. Your company has a main office in New York and several branch offices globally. All the offices use databases on their local SQL Server 2008 servers to support trading applications. A low-bandwidth WAN link connects the offices. The WAN link is subject to interruptions. You plan to implement a reporting instance that will be deployed in the main office. You need to design a solution to transfer data from all offices to the reporting instance in the main office. You also need to ensure that the solution has minimum effect on the performance of all trading servers. What should you do?

 

A.

Create a warm standby server in the main office by using log shipping from the branch offices.

B.

use distributed transactions in the branch offices to write the trade information to the local trading

systems and the main office trading system.

C.

Create a mirrored database solution in high-safety mode in each branch office. Place the mirrored

databases in the main office.

D.

Create a publication by using transactional replication in each branch office. Create subscriptions

in the main office.

 

Correct Answer: D

 

 

QUESTION 59

Your company is developing an application. The database platform for the application will be vendor-independent. You need to ensure that the application automatically stores the current date and time data when a record is inserted or updated, regardless of the database platform. What should you do?

 

A.

Use Entity Framework and DateTime.Now.

B.

Create a database column that has a default value of GETDATEQ.

C.

Create an INSERT trigger that uses the GETDATEQ function.

D.

Use the datetime2 data type.

 

Correct Answer: A

 

 

QUESTION 60

A database contains two tables named Table1 and Table1_Details. Table1_Details contains details about the items in Table1. You need to ensure that when an item is removed from Table1, all related items are removed from Table1_Details. You must achieve this goal by using the minimum amount of Transact-SQL code. What should you do?

 

A.

Create a foreign key relationship. Set Cascade Delete to Set Null.

B.

Create a foreign key relationship. Set Cascade Delete to Cascade.

C.

Create a trigger on Table1_Details that fires on the Delete action.

D.

Create a stored procedure that deletes all related items from Table1_Detai!s.

 

Correct Answer: B

 

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

Try Microsoft MCITP 70-451 Free Demo