Microsoft 70-451 Online Practice Questions and Exam Preparation

Microsoft 70-451 Online Questions & Answers

• Question 131:

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

  The database will contain a view. The view will contain a computed column that will be indexed. The computed column will use a user-defined scalar function. The function will perform complex string manipulations.

  You need to identify the appropriate function to use.

  What should you do?

  A. Use a nondeterministic Transact-SQL table-valued function.
  B. Use a deterministic Transact-SQL table-valued function.
  C. Use a nondeterministic common language runtime (CLR) scalar-valued function.
  D. Use a deterministic common language runtime (CLR) scalar-valued function.
  D. Use a deterministic common language runtime (CLR) scalar-valued function.

• Question 132:

  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:

  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
  C. REPEATABLE READ

• Question 133:

  You need to design a method for storing large XML-formatted data. The design must meet the following requirements: What should you do?

  A. Store the XML data by using the filestream data type.
  B. Store the XML data by using the nvarchar(max) data type.
  C. Create columns based on XML elements. Shred the XML data into the individual columns.
  D. Create columns based on Extensible Stylesheet Language Transformations (XSLT). Store the XML data by using the XML data type.
  C. Create columns based on XML elements. Shred the XML data into the individual columns.

• Question 134:

  You are creating a new update query.

  You need to ensure that the query terminates if a shared lock is encountered white the data is updating.

  What should you include in the query?

  A. WITH (NOWAIT)
  B. sp_who
  C. OPTION (MERGE JOIN)
  D. sp_lock
  A. WITH (NOWAIT)

• Question 135:

  You have a table that has 10 million rows. The table has a view that returns all of the rows.

  You discover performance degradation when you run an unfiltered view.

  You need to recommend a solution to replace the view. The solution must require that the returned data is filtered by a parameter.

  What should you use?

  A. an indexed view
  B. a scalar function
  C. a table-valued function
  D. a table-valued type
  C. a table-valued function

• Question 136:

  You are designing a table to store date and time information for an application. The application will be used by users in Europe, Asia, and North America.

  You need to add a column that stores the date and time information for all users. The column must contain information that can be used to identify the users' local time zone.

  What should you include in the design?

  A. a sysdatetimeoffset function
  B. a getutcdate function
  C. a datetime2 data type
  D. a datetimeoffset data type
  D. a datetimeoffset data type

• Question 137:

  You are designing a maintenance strategy for a database that contains several views. The views will be assigned custom permissions. You need to recommend a solution that will allow developers to modify the views without affecting the views' existing permissions. What should you recommend?

  A. Create a new view.
  B. Alter the existing view.
  C. Rename the existing view.
  D. Drop the existing view and then recreate the view.
  B. Alter the existing view.

• Question 138:

  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
  C. TABLOCKX

• Question 139:

  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
  D. TABLOCKX