Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 321:

  You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

  A. SERIALIZABLE
  B. SNAPSHOT
  C. READ COMMITTED SNAPSHOT
  D. REPEATABLE READ
  C. READ COMMITTED SNAPSHOT

  ---

  1.

  "There is high contention between readers and writers"

  SNAPSHOT and RED COMMITTED SNAPSHOT will not apply share locks so minimize the contention.

  https://msdn.microsoft.com/en-us/library/ms173763.aspx

  2.

  "need to minimize the use of the tempdb space"

  RED COMMITTED SNAPSHOT uses less tempdb space.

  https://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx So the answer is RED COMMITTED SNAPSHOT

• Question 322:

  Which FOR XML options can you use to manually format the XML returned? (Choose all that apply.)

  A. FOR XML AUTO
  B. FOR XML EXPLICIT
  C. FOR XML RAW
  D. FOR XML PATH
  B. FOR XML EXPLICIT
  D. FOR XML PATH

  ---

  A. Incorrect: FOR XML AUTO automatically formats the XML retuned. B. Correct: FOR XML EXPLICIT allows you to manually format the XML returned. C. Incorrect: FOR XML RAW automatically formats the XML retuned. D. Correct: FOR XML PATH allows you to manually format the XML returned.

• Question 323:

  You use a Microsoft SQL Server 2012 database.

  You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.

  Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 324:

  Which of the following T-SQL statements can be used to cause branching within a stored procedure? (Choose all that apply.)

  A. WHILE
  B. BEGIN/END
  C. IF/ELSE
  D. GO
  A. WHILE
  C. IF/ELSE

• Question 325:

  What is the restriction in regard to the table specified as the target of an OUTPUT INTO clause? (Choose all that apply.)

  A. The table can only be a table variable.
  B. The table can only be a temporary table.
  C. The table cannot participate in either side of a foreign key relationship.
  D. The table cannot have triggers defined on it.
  C. The table cannot participate in either side of a foreign key relationship.
  D. The table cannot have triggers defined on it.

• Question 326:

  Which WHEN clauses are required in a MERGE statement at minimum?

  A. At minimum, the WHEN MATCHED and WHEN NOT MATCHED clauses are required.
  B. At minimum, only one clause is required, and it can be any of the WHEN clauses.
  C. At minimum, the WHEN MATCHED clause is required.
  D. At minimum, the WHEN NOT MATCHED clause is required.
  B. At minimum, only one clause is required, and it can be any of the WHEN clauses.

• Question 327:

  You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.

  

  Unless stated above, no columns in the Employee table reference other tables. Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a Foreign Key constraint that references a different table in the database?

  A. DateHired
  B. DepartmentID
  C. EmployeeID
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  B. DepartmentID

  ---

  Use the EmployeeID, which would be used as a primary key in the Employee table, when defining a foreign key constraint from another table in the database.

• Question 328:

  You work as a database developer at ABC.com. ABC has an in-house application named ABCApp3 that runs a Transact-SQL query against a SQL Server 2012 database.

  You want to run an execution plan against the query that will provide detailed information on missing indexes.

  How would you accomplish this task?

  A. You should make use of the READPAST hint in the queries.
  B. You should make use of the READCOMMITTED hint in the queries.
  C. You should make use of the SET SHOWPLAN_XML ON statement in the query.
  D. You should make use of the SET STATISTICS XML ON statement in the query.
  E. You should make use of the SET XACT_ABORT OFF statement in the query.
  F. You should make use of the SET CONTEXT_INFO statement in the query.
  C. You should make use of the SET SHOWPLAN_XML ON statement in the query.

• Question 329:

  You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements:

  Contain a new column named LineltemTotal that stores the product of ListPrice and Quantity for each row.

  The calculation for a line item total must not be run every time the table is queried.

  The code must NOT use any object delimiters.

  The solution must ensure that LineItemTotal is stored as the last column in the table.

  Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

  

  Check the answer below

  CREATE TABLE OrderDetails ( ListPrice money NOT NULL, Quantity int NOT NULL, LineItemTotal AS (ListPrice * Quantity) PERSISTED )

• Question 330:

  Which of the following functions would you consider using to generate surrogate keys? (Choose all that apply.)

  A. NEWID
  B. NEWSEQUENTIALID
  C. GETDATE
  D. CURRENT_TIMESTAMP
  A. NEWID
  B. NEWSEQUENTIALID