Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 81:

  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.

  

  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:

  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 TRYblock. Add a ROLLBACK TRANSACTION TR1 statement inside the new CATCH block.
  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 TRYblock. Add a ROLLBACK TRANSACTION TR1 statement inside the new CATCH block.

• Question 82:

  You have a table that contains a string column. Most of the rows have the same value. You need to create a multistep stored procedure that meets the following requirements: What should you use?

  A. sp_recompile
  B. WITH RECOMPILE
  C. sp_configure
  D. a RECOMPILE query hint
  D. a RECOMPILE query hint

• Question 83:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  A table in a database will store large image files (20-50 MB in size).

  You have the following business requirements:

  Which strategy should you use?

  A. Use an image data type.
  B. Use the varbinary(max) data type.
  C. Use the varbinary(max) data type along with the FILESTREAM attribute.
  D. Store the image file in a file system. Use a varchar data type to store the file location in the database.
  C. Use the varbinary(max) data type along with the FILESTREAM attribute.

• Question 84:

  You have a database that has 20 tables.

  The tables are not configured to have any referential integrity. All tables have primary keys. Each table contains over 5 million rows. Ten percent of the rows contain outdated information.

  You need to design a maintenance process to delete all outdated rows. The solution must meet the following requirements:

  What should the design include?

  A. a WHILE loop that contains a single DELETE statement
  B. multiple TRUNCATE TABLE statements
  C. a single transaction that contains multiple DELETE statements
  D. an IF statement that contains a single DELETE statement
  A. a WHILE loop that contains a single DELETE statement

• Question 85:

  You are designing a table that will contain the columns in the following table.

  

  You need to add a column that will display the sum of the Subtotal, TaxAmt, and FreightAmt columns. The solution must minimize the amount of storage space required to store the table. What should you create?

  A. a column that uses the numeric data type
  B. a computed column
  C. a persisted computed column
  D. a column that uses the money data type
  B. a computed column

• Question 86:

  You have an instance of SQL Server 2008 that has xp_cmdshell enabled. You need to design a stored procedure that meets the following requirements: What should you include in the design?

  A. Grant users permission to execute xp_cmdshell.
  B. Grant users permission to execute sp_configure.
  C. Create a procedure that uses EXECUTE AS OWNER. Call xp_cmdshell in the procedure. Grant users permission to execute the procedure.
  D. Create a procedure that uses EXECUTE AS CALLER. Call xp_cmdshell in the procedure. Grant users permission to execute the procedure.
  C. Create a procedure that uses EXECUTE AS OWNER. Call xp_cmdshell in the procedure. Grant users permission to execute the procedure.

• Question 87:

  You are a database solutions architect. Your company plans to develop a solution by using a SQL Server 2008 instance.

  You design a new database that contains a table to store Microsoft Office documents.

  You have the following business requirements:

  You need to use an appropriate data type to store the documents.

  Which data type should you use?

  A. varchar(max)
  B. nvarchar(max)
  C. varbinary(max)
  D. varbinary(max) by using the FILESTREAM attribute
  C. varbinary(max)

• Question 88:

  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.
  B. Change the XML data type to Typed XML.

• Question 89:

  You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a common language runtime (CLR) user-defined scalar function. The function will return an integer value.

  You need to ensure that the computed columns that use the result from this function can be indexed.

  What should you do?

  A. 1. Ensure that the logic of the function returns the same value for the same input values and the same database state.2. Ensure that the IsDeterministic property is set to True.
  B. 1. Ensure that the logic of the function returns a different value for the same input values and the same database state.2. Ensure that the IsDeterministic property is set to True.
  C. C. 1. Ensure that the logic of the function returns the same value for the same input values and the same database state.2. Ensure that the IsDeterministic property is set to False.
  D. D. 1.稥nsure that the logic of the function returns a different value for the same input values and the same database state.2. Ensure that the IsDeterministic property is set to False.
  A. 1. Ensure that the logic of the function returns the same value for the same input values and the same database state.2. Ensure that the IsDeterministic property is set to True.

• Question 90:

  You are a database developer. You plan to design a database solution by using SQL Server 2008.

  Account managers in your company store order data in a database. Your company requires a list of customers for each account manager. The list must be sorted in the descending order of the order amount.

  You create a query that generates the list at the end of each month. You need to ensure that the query executes as quickly as possible.

  What should you do?

  A. Create a cursor that returns each account manager, and then sort the order data by order amount.
  B. Use a SELECT statement that uses the OVER clause to rank the customers by order amount for each account manager.
  C. Create a correlated subquery to return the order amount for each account manager. Sort the results first by account manager and then by order amount.
  D. Create a table-valued function that returns the order amount for a specific account manager, and then create a query by using the CROSS APPLY clause to list each account manager. Sort the results first by account manager and then by order amount.
  B. Use a SELECT statement that uses the OVER clause to rank the customers by order amount for each account manager.