Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 351:

  Which sentence best describes the difference between an inline table-valued UDF and a multistatement table-valued UDF?

  A. An inline table-valued UDF defines the schema of a table variable, with column names and data types, and inserts data into the table variable.
  B. An inline table-valued UDF defines the schema of a permanent table, with column names and data types, and then inserts data into that table.
  C. A multistatement table-valued UDF defines the schema of a table variable, with column names and data types, and inserts data into the table variable.
  D. A multistatement table-valued UDF defines the schema of a permanent table, with column names and data types, and then inserts data into that table.
  C. A multistatement table-valued UDF defines the schema of a table variable, with column names and data types, and inserts data into the table variable.

  ---

  An inline table-valued UDF does not define the schema of the table structure it returns.

• Question 352:

  Which of the following SET commands would prevent execution plan reusage?

  A. SET STATISTICS IO ON
  B. SET STATISTICS PROFILE ON
  C. SET CONCAT_NULL_YIELDS_NULL OFF
  D. SET STATISTICS IO OFF
  C. SET CONCAT_NULL_YIELDS_NULL OFF

  ---

  The SET CONCAT_NULL_YIELDS_NULL command has an influence on the result of a query, and therefore changing this option prevents plan reusage.

• Question 353:

  You administer a Microsoft Azure SQL Database instance.

  You are writing a new stored procedure that uses explicit transactions.

  You need to ensure that no other process can affect the objects being used by the stored procedure while it is running.

  Which Transact-SQL statement should you run?

  A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  B. SET IMPLICIT_TRANSACTIONS ON
  C. SET CURSOR_CLOSE_ON_COMMIT ON
  D. SET XACT_ABORT ON
  A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

  ---

  SERIALIZABLE specifies the following:

  Statements cannot read data that has been modified but not yet committed by other transactions.

  No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

• Question 354:

  You create an index to support the WHERE clause of a query. However, SQL Server does not use the index. What are the possible reasons? (Choose all that apply.)

  A. The arguments in the predicate are not searchable.
  B. SQL Server does not consider using an index to support the WHERE clause.
  C. The predicate is not selective enough.
  D. You are in the context of the tempdb database, and SQL Server does not use indexes in this database.
  A. The arguments in the predicate are not searchable.
  C. The predicate is not selective enough.

• Question 355:

  DRAG DROP

  A database contains tables as shown in the exhibit. (Click the Exhibit button.)

  Products that are discontinued are moved from the Products table to the DiscontinuedProducts table. Any orders for discontinued products are removed from the Orders table.

  You write the following SELECT statement to return all the discontinued products:

  SELECT ProductId FROM DiscontinuedProducts

  You need to extend the SELECT statement to include products who do not have any orders.

  Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Exhibit

  

  Select and Place:

  

  

• Question 356:

  You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that contains a database named ABCDB.

  The ABCDB database is used by an in-house application named ABCApp3 that queries a read- only table with a clustered index. ABC.com users report that ABCApp3 is functioning sluggishly.

  You suspect query the application uses is causing the problem. You analyze the query and discover that column referenced in the WHERE clause is not part of the clustered index. You also notice that the query returns five columns, as well

  as a COUNT (*) clause grouped on the five columns.

  How would you improve the efficiency of this query?

  A. You should replace the query with recursive stored procedure.
  B. You should replace the COUNT (*) clause with a persisted computed column.
  C. You should create nonclustered indexes on all columns used in the query.
  D. You should create a filtered index on the column used in the WHERE clause.
  E. You should add an INCLUDE clause to the clustered index.
  F. You should create a columnstore index on all columns used in the query.
  G. You should create a unique clustered index on the column used in the WHERE clause.
  F. You should create a columnstore index on all columns used in the query.

• Question 357:

  Which function returns a table with key phrases associated with the full-text indexed column?

  A. CONTAINSTABLE()
  B. FREETEXTTABLE()
  C. SEMANTICKEYPHRASETABLE()
  D. SEMANTICSIMILARITYTABLE()
  E. SEMANTICSIMILARITYDETAILSTABLE()
  C. SEMANTICKEYPHRASETABLE()

• Question 358:

  You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:

  -Returns a result set based on supplied parameters.

  - Enables the returned result set to perform a join with a table.

  Which object should you use?

  A. Inline user-defined function
  B. Stored procedure
  C. Table-valued user-defined function
  D. Scalar user-defined function
  C. Table-valued user-defined function

• Question 359:

  How can you find the text of the query executed by using DMOs?

  A. This info is provided in the sys.dm_exec_query_stats dynamic management view.
  B. By querying the sys.dm_exec_sql_text dynamic management function.
  C. The sys.dm_exec_query_plan dynamic management function returns the query text.
  D. You cannot find the query text through DMOs
  B. By querying the sys.dm_exec_sql_text dynamic management function.

• Question 360:

  What are the benefits of using the combination of statements CREATE TABLE and INSERT SELECT over SELECT INTO? (Choose all that apply.)

  A. Using the CREATE TABLE statement, you can control all aspects of the target table. Using SELECT INTO, you can't control some of the aspects, like the destination file group.
  B. The INSERT SELECT statement is faster than SELECT INTO.
  C. The SELECT INTO statement locks both data and metadata for the duration of the transaction. This means that until the transaction finishes, you can run into blocking related to both data and metadata. If you run the CREATE TABLE and INSERT SELECT statements in separate transactions, locks against metadata will be released quickly, reducing the probability for and duration of blocking related to metadata.
  D. Using the CREATE TABLE plus INSERT SELECT statements involves less coding than using SELECT INTO.
  A. Using the CREATE TABLE statement, you can control all aspects of the target table. Using SELECT INTO, you can't control some of the aspects, like the destination file group.
  C. The SELECT INTO statement locks both data and metadata for the duration of the transaction. This means that until the transaction finishes, you can run into blocking related to both data and metadata. If you run the CREATE TABLE and INSERT SELECT statements in separate transactions, locks against metadata will be released quickly, reducing the probability for and duration of blocking related to metadata.