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

Ensurepass

QUESTION 21

You are designing a database that will store telephone numbers. You need to ensure that only phone numbers that use a specific format are written to the database. What should you create?

 

A.

a CHECK constraint

B.

a computed column

C.

a DEFAULT constraint

D.

a persisted computed column

 

Correct Answer: A

 

 

QUESTION 22

You are a database developer. You plan to design a database solution by using SQL Server 2008. You create a stored procedure that uses the TRY/CATCH syntax in a new database. When the stored procedure is executed, it logs information about each step in the TRY block into a table named dbo.ExecutionLog. When an error occurs, the stored procedure must perform the following tasks:

 

– Roll back the changes made to the target tables.

– Retain the log entries stored in the dbo.ExecutionLog table.

 

You need to ensure that the stored procedure performs the given tasks.

What should you do?

 

A.

1.Start a transaction in the TRY block.

2. After each step, insert log entries into the dbo.ExecutionLog table.

3. In the CATCH block, commit the transaction.

4. After the CATCH block, use data in the dbo.ExecutionLog table to reverse any changes made

to the target tables.

5. Commit the transaction if one exists.

B.

1. Start a transaction in the TRY block.

2. Before each step, define a transactional save point.

3. After each step, insert log entries into the dbo.ExecutionLog table.

4. In the CATCH block, roll back to the transactional save points.

5. After the CATCH block, commit the transaction.

C.

1. Define a temporary table before the TRY block by using the same columns as that of the

dbo.ExecutionLog table.

2. Start a transaction in the TRY block.

3. After each step, insert log entries into the temporary table.

4. In the CATCH block, roll back the transaction.

5. After the CATCH block, insert the rows from the temporary table into the dbo.ExecutionLog table.

6. Commit the transaction if one exists.

D.

1. Define a table variable before the TRY block by using the same columns as that of the

dbo.ExecutionLog table.

2. Start a transaction in the TRY block.

3. After each step, insert log entries into the table variable.

4. In the CATCH block, roll back the transaction.

5. After the CATCH block, insert the rows from the table variable into the dbo.ExecutionLog table.

6. Commit the transaction if one exists.

 

Correct Answer: D

 

 

QUESTION 23

You are a database developer. You plan to design a database solution by using SQL Server 2008. There are two schemas named Sales and Marketing. You are the owner of the Sales schema and the Marketing schema is owned by a user named MarketingUser. Users of the Marketing schema do not have permissions to access the Sales schema. You have permissions to create objects in all schemas in the database. The Sales schema has a table named Customers. You plan to create a stored procedure in the Marketing schema for the marketing team. The stored procedure will select data from the Customers table and will be owned by MarketingUser. You need to ensure that the marketing team is able to execute the stored procedure. What should you do?

 

A.

Create the procedure by using the EXECUTE AS SELF option.

B.

Create the procedure by using the EXECUTE AS CALLER option.

C.

Create the procedure by using the EXECUTE AS OWNER option.

D.

Create the procedure by using the EXECUTE AS USER=MarketingUser option.

 

Correct Answer: A

 

 

QUESTION 24

You are a database developer. You plan to create a database by using SQL Server 2008. A database contains a table named Sales. The Sales table contains customer order summary information. You create a stored procedure that uses a SELECT statement. At the moment of execution, the procedure must return a precise summation of the total sales for the current day. You need to use a query hint to prevent any data modification in the Sales table when the stored procedure is being executed.

Which query hint should you recommend?

 

A
.

READPAST

B.

HOLDLOCK

C.

TABLOCKX

D.

READCOMMITTED

 

Correct Answer: C

 

 

QUESTION 25

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database contains a table named Products. The database has two stored procedures named ModifyProduct and RetrieveProducts. ModifyProduct updates a single row in the Products table. RetrieveProducts returns all rows from the Products table. RetrieveProducts is used by a report. Users who run the report experience contention problems. You discover that RetrieveProducts is being blocked by ModifyProduct. The report must not include rows that are currently being modified. You need to ensure that the report is executed as quickly as possible. Which locking hint should you use in RetrieveProducts?

 

A.

NOLOCK

B.

NOWAIT

C.

READPAST

D.

READUNCOMMITTED

 

Correct Answer: C

 

 

QUESTION 26

You have a stored procedure that is used to set up maintenance tasks. The stored procedure executes every night. The stored procedure contains three critical data manipulation language (DML) statements that operate against a single table. You need to prevent users from modifying any data in the table while the stored procedure executes. Which locking hint should you use?

 

A.

NOLOCK

B.

READCOMMITTED

C.

REPEATABLEREAD

D.

TABLOCKX

 

Correct Answer: D

 

 

QUESTION 27

You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. An application contains two stored procedures. The tasks performed by the stored procedures are as shown in the following table.

 

70-451-demo-4

 

You discover that the procedures occasionally throw foreign key violation errors. IncludeDetails throws an error when it inserts records into the ProductInfo table. ImportNewProducts throws an error when it inserts records into the ProductCurrentPrice table. If an error occurs in the INSERT statement of ProductInfo, records inserted into ProductHeader and ProductCurrentPrice are committed. If an error occurs in the INSERT statement of ProductCurrentPrice, all transactions are rolled back. What should you do?

 

A.

1. Add a SET XACT_ABORT OFF statement in IncludeDetails.

2. Add a SET XACT_ABORT ON statement in ImportNewProducts.

B.

1. Add a SET XACT_ABORT ON statement in IncludeDetails.

2. Add a SET XACT_ABORT OFF statement in ImportNewProducts.

C.

1. Enclose all statements of IncludeDetails in a TRY/CATCH block.

2. Add a ROLLBACK TRANSACTION statement in the CATCH block.

D.

1. Enclose all statements of ImportNewProducts in a TRY/CATCH block.

2. Add a ROLLBACK TRANSACTION statement in the CATCH block.

 

Correct Answer: A

 

 

QUESTION 28

You are a database developer. You develop solutions by using SQL Server 2008 in an enterprise environment. You are creating a SQL Agent job that uses Transact-SQL to update data in two related databases on two different servers. You have the following requirements:

 

– The job can only execute once each evening.

– The databases on each server use the full-recovery model.

– Transaction log backups for the two databases occur at different times.

– The job uses transactions to ensure that in the event of an error, all updates are rolled back.

 

You need to ensure that when you restore a database on either server, the two databases are restored to a state that reflects the last time the job successfully executed. What should you do?

 

A.

Ensure both databases are altered using the NO_WAIT termination clause.

B.

Use the Windows Sync Manager to ensure that the databases can never be out of synchronization.

C.

Use saved transactions. When a database failure occurs, restore both databases by using a saved

transaction.

D.

Use marked transactions. When a database failure occurs, restore both databases by using a marked

transaction.

 

Correct Answer: D

 

 

QUESTION 29

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database
will contain a table that will store customer data as XML data. The data supports an application that cannot be altered. You plan to prevent the following types of errors in the XML data. NULL values in the Customer Name field Non-numeric values in the Customer Telephone field. Invalid values in the Gender field. You need to implement the plan without modifying the application. What should you do?

 

A.

Use the FileStream data type.

B.

Change the XML data type to Typed XML.

C.

Use the HierarchyID data type to validate data.

D.

Save the XML data in a standard table format. Specify the correct data types, constraints, and NOT

NULL parameters in the standard table.

 

Correct Answer: B

 

 

QUESTION 30

You are designing a database that contains a data definition language (DDL) trigger. The DDL trigger will provide the maximum amount of data available when any attempt is made to change the database schema. You need to design a table to meet the following requirements:

Accept the EVENTDATA information that is provided by the trigger Support the searching and retrieval of nodes and values Minimize developm
ent time Which data type should you use?

 

A.

nvarchar(max)

B.

varchar(max)

C.

varbinary

D.

XML

 

Correct Answer: D

 

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

Try Microsoft MCITP 70-451 Free Demo