Braindump2go Updates Microsoft 70-451 Exam Dumps Questions, adds some new changed questions from Microsoft Official Exam Center. Want to know 2015 70-451 Exam test points? Download the following free Braindump2go Latest Exam Questions Today!
Vendor: Microsoft
Exam Code: 70-451
Exam Name: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Keywords: 70-451 Exam Dumps,70-451 Practice Tests,70-451 Practice Exams,70-451 Exam Questions,70-451 PDF,70-451 VCE Free,70-451 Book,70-451 E-Book,70-451 Study Guide,70-451 Braindump,70-451 Prep Guide
70-451 PDF Dumps & 70-451 VCE Dumps (147q) Full Version Download: http://www.braindump2go.com/70-451.html
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
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.
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.
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
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
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
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.
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.
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.
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.
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 development time
Which data type should you use?
A. nvarchar(max)
B. varchar(max)
C. varbinary
D. XML
Answer: D
Braindump2go Offers PDF & VCE Dumps Download for New Released Microsoft 70-451 Exam! 100% Exam Success Guaranteed OR Full Money Back Instantly!
http://www.braindump2go.com/70-451.html (147Q&As)