Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 21:

  Which of the following is not an XML data type method?

  A. merge()
  B. nodes()
  C. exist()
  D. value()
  A. merge()

• Question 22:

  You work as a SQL Server 2012 database administrator at ABC.com. You have developing a database named WebAnDB that will be used by a web site analysis application. The WebAnDB database has a table named Visitors that stores

  date and time data in a column named Accessed.

  You must now develop a stored procedure that will insert data into the Accessed column. You want the stored procedure to store time zone data as well.

  How would you accomplish this?

  A. You should make use of a scalar user-defined function.
  B. You should make use of the SET CONTEXT_INFO statement in the stored procedure.
  C. You should make use of the DATETIMEOFFSET data type.
  D. You should make use of the TODATETIMEOFFSET function.
  E. You should make use of the SET FORCEPLAN ON statement in the stored procedure.
  F. You should make use of a cursor.
  D. You should make use of the TODATETIMEOFFSET function.

• Question 23:

  You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  I. Use the DATETIMEOFFSET data type.

  ---

  http://msdn.microsoft.com/en-us/library/ff848733.aspx http://msdn.microsoft.com/en-us/library/bb630289.aspx

• Question 24:

  You have several SQL Server queries.

  You plan to optimize the queries to improve performance.

  You run the queries in SQL Server Management Studio.

  You need to compare query runs to expose the indexing issues of the queries.

  Which three actions should you perform from SQL Server Management Studio? Each correct answer presents part of the solution.

  A. Enable the Debug option.
  B. Add the STATISTICS TIME execution setting to the query.
  C. Add the STATISTICS IO execution setting to the query.
  D. Add the STATISTICS PROFILE execution setting to the query.
  E. Enable the Include Actual Execution Plan option.
  B. Add the STATISTICS TIME execution setting to the query.
  C. Add the STATISTICS IO execution setting to the query.
  E. Enable the Include Actual Execution Plan option.

  ---

  E: An execution plan is theresult of the query optimizer's attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted. To generate the first execution plan, you can enable the Include

  Actual Execution Plan option.

  B: SET STATISTICS TIME displays the number of milliseconds required to parse, compile, and execute each statement.

  C: STATISTICS IO causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements. This is useful information for optimizing queries.

  The information include Scan count:

  Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.

  Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = .

  Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on anon-primary key column. This is done to check for duplicate values for the keyvalue that you are searching for. For example

  WHERE Clustered_Index_Key_Column = .

  Scan count is N when N is the number of different seek/scan started towards the leftor right side at the leaf level after locating a key value using the index key.

  Incorrect Answers:

  A: The Debug option is used the find programming errors, and is not used to increase performance.

  D: Graphical Plans are quick and easy to read but the detailed data for the plan is masked. Both Estimated and Actual execution plans can be viewed in graphical format. Text plans are a bit harder to read, but more information is immediately available. There are three text plan formats: SHOWPLAN_ALL: a reasonably complete set of data showing the Estimated execution plan for the query SHOWPLAN_TEXT: provides a very limited set of data for use with tools like osql.exe. It too only shows the Estimated execution plan STATISTICS PROFILE: similar to SHOWPLAN_ALL except it represents the data for the Actual execution plan

  References: https://www.simple-talk.com/sql/performance/execution-plan-basics/ https://msdn.microsoft.com/en-us/library/ms184361.aspx

• Question 25:

  What are the advantages of sp_executesql over the EXECUTE() command? (Choose all that apply.)

  A. sp_executesql can parameterize search arguments and help prevent SQL injection.
  B. sp_executesql uses Unicode strings.
  C. sp_executesql can return data through output parameters.
  A. sp_executesql can parameterize search arguments and help prevent SQL injection.
  C. sp_executesql can return data through output parameters.

  ---

  -

  Parameterization is the key advantage of sp_executesql over the EXEC() statement because it ensures that any injected code will only be seen as a string parameter value, and not as executable code.

  -

  The use of output parameters solves a serious limitation of the EXECUTE command. EXECUTE cannot return information to the calling session directly.

• Question 26:

  DRAG DROP

  You need to create a stored procedure to support the following: TRY/CATCH error handling Transaction management

  Which three statements should you include in the stored procedure in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

  Select and Place:

  

  

• Question 27:

  You're designing a new table that will hold information about medical records. Another table called documents contains a foreign key that references this table. To make sure information is cleaned up when a delete operation is performed, you've added an ON DELETE CASCADE statement to the foreign key relationship. What will happen when a row is deleted from the medical records table?

  A. Corresponding rows in the referenced table (documents) will be deleted
  B. All the values that make up the foreign key are set to NULL in the documents table
  C. All the values that comprise the foreign key are set to their default values in the documents table
  D. Rows are deleted from the medical records table but the documents table is unchanged
  A. Corresponding rows in the referenced table (documents) will be deleted

  ---

  Don't forget that ON DELETE statements are included as part of the foreign key relationship.

• Question 28:

  You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition. You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use?

  

  A. ALTER TABLE Orders ADD CONSTRAINT FX_Orders_CustomerID FOR EIGN KEY (CustomerId) REFERENCES Customer (CustomerId)
  B. ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOR EIGN KEY {Cu3tomerID; REFERENCES Orders (CustomerId)
  C. ALTER TABLE Orders ADD CONSTRAINT CK_Crders_CustomerID CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
  D. ALTER TABLE Customer ADD OrderId INT NOT NULL; ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOR EIGN KEY (CrderlD) REFERENCES Orders (CrderlD);
  E. ALTER TABLE Orders ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)
  A. ALTER TABLE Orders ADD CONSTRAINT FX_Orders_CustomerID FOR EIGN KEY (CustomerId) REFERENCES Customer (CustomerId)

  ---

  http://msdn.microsoft.com/en-us/library/ms189049.aspx

• Question 29:

  SIMULATION

  You need to execute an UPDATE statement that modifies a subset of rows in a table. The rows affected by the UPDATE statement must only be committed if the row count criteria is met.

  Part of the correct Transact-SQL has been provided in the answer are below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.

  

  

  Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  

  Check the answer below

  1. DECLARE @ExprectedRowCount int = 8

  2.

  3. BEGIN TRAN OrderUpdate

  4.

  5. UPDATE dbo.OrderHeader

  6. SET PromisedDate = DATEADD(dd, 5, PromisedDate)

  7. WHERE CustomerID = 8432

  8. AND Shipped =0

  9.

  10. If @@RowCount = @ExpectedRowCount

  11.

  12. COMMIT OrderUpdate

  13.

  14. else

  15.

  16.

  ROLLBACK OrderUpdate

  Explanation: On line 3 add: BEGIN TRAN On line 12 add: COMMIT On line 16 add: ROLLBACK

  ---

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql

• Question 30:

  When you fetch rows from a cursor, how do you know when there are no more rows to fetch?

  A. When the @@FETCH_STATUS function returns 0
  B. When the @@FETCH_STATUS function returns -1
  C. When the @@FETCH_STATUS function returns -2
  D. When the @@FETCH_STATUS function generates an error
  B. When the @@FETCH_STATUS function returns -1

  ---

  A. Incorrect: 0 means that the last fetch was successful. There could be more rows.

  B. Correct: -1 means that the row is beyond the result set.

  C. Incorrect: -2 means that the row fetched is missing. There still could be more rows.

  D. Incorrect: The function shouldn't generate any errors.