Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 201:

  Which of the following are T-SQL regular identifiers? (Choose all that apply.)

  A. categoryname
  B. category name
  C. category$name
  D. category_name
  A. categoryname
  D. category_name

• Question 202:

  You need to create a stored procedure that enters values into multiple tables. The solution must ensure that if a single insert fails, none of the values are inserted into the tables.

  How should you complete the stored procedure? To answer, drag the appropriate values to the correct locations. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view

  content.

  Select and Place:

  

  

  ---

  Box 1: BEGIN TRY

  Box 2:SAVE TRANSACTION

  Box 3: ROLLBACK

  Box 4: COMMIT TRANSACTION

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

• Question 203:

  You administer a Microsoft SQL Server database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.

  You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

  A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
  B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
  C. ALTER INDEX ALL ON OrderDetail REBUILD
  D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
  B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

  ---

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

• Question 204:

  What is the advantage of using THROW in a CATCH block?

  A. THROW in a CATCH block does not require parameters and so is easier to write.
  B. THROW re-throws the original error so that the original error can be handled.
  C. THROW causes an error severity of level 16 automatically.
  D. The statement before a THROW requires a semicolon.
  B. THROW re-throws the original error so that the original error can be handled.

  ---

  A. Incorrect: Although it is true that THROW does not take parameters in a CATCH block, that is not necessarily an advantage.

  B. Correct: The THROW statement in a CATCH block can re-throw an error and thereby allow you to report on an error in the TRY block without having to have stored any prior information. This makes it possible to do all error handling in the CATCH block.

• Question 205:

  How can you turn off nested triggers on a SQL Server instance by using T-SQL?

  A. Use the sp_configure stored procedure followed by 'nested triggers' and 'OFF'.
  B. Use the sp_configure stored procedure followed by 'nested triggers' and 0.
  C. Use the sp_configure stored procedure followed by 'nested triggers' and 'OFF',followed by the RECONFIGURE statement.
  D. Use the sp_configure stored procedure followed by 'nested triggers' and 0,followed by the RECONFIGURE statement.
  D. Use the sp_configure stored procedure followed by 'nested triggers' and 0,followed by the RECONFIGURE statement.

• Question 206:

  What are the advantages of using a sequence object instead of IDENTITY? (Choose all that apply.)

  A. The IDENTITY property doesn't guarantee that there won't be gaps and the sequence object does.
  B. The IDENTITY property cannot be added to or removed from an existing column; a DEFAULT constraint with a NEXT VALUE FOR function can be added to or removed from an existing column.
  C. A new identity value cannot be generated before issuing an INSERT statement, whereas a sequence value can.
  D. You cannot provide your own value when inserting a row into a table with an IDENTITY column without special permissions. You can specify your own value for a column that normally gets its values from a sequence object.
  B. The IDENTITY property cannot be added to or removed from an existing column; a DEFAULT constraint with a NEXT VALUE FOR function can be added to or removed from an existing column.
  C. A new identity value cannot be generated before issuing an INSERT statement, whereas a sequence value can.
  D. You cannot provide your own value when inserting a row into a table with an IDENTITY column without special permissions. You can specify your own value for a column that normally gets its values from a sequence object.

• Question 207:

  You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button).

  

  You need to create a query for a report. The query must meet the following requirements:

  1. NOT use object delimiters.

  2. Use the first initial of the table as an alias.

  3. Return the most recent order date for each customer.

  4. Retrieve the last name of the person who placed the order.

  The solution must support the ANSI SQL-99 standard.

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

  

  Check the answer below

  SELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate FROM Customers AS C INNER JOIN Orders AS O ON C.CustomerID=O.CustomerID GROUP BY C.Lastname ORDER BY MAX (O.OrderDate) DESC

• Question 208:

  You administer a Microsoft SQL Server instance that will support several databases.

  You need to ensure that the organization serial numbers can be stored in every new database created. You also need to ensure that the serial numbers are six alphanumeric characters and have the same structure across every database.

  What should you do?

  A. Create a user-defined data type on the master database.
  B. Create a user-defined type on the master database.
  C. Create a user-defined data type on the model database.
  D. Create a user-defined type on the model database.
  D. Create a user-defined type on the model database.

  ---

  A user defined data type in SQL Server is only accessible in a single database, but one trick with new databases is to create the objects in the model database, so as new databases are created the user defined data types, rules, defaults, etc.

  will automatically be available.

  References: https://www.mssqltips.com/sqlservertip/1628/sql-server-user-defined-data-types-rules-and-defaults/

• Question 209:

  DRAG DROP

  You administer a Microsoft SQL Server database that contains a table named Customer defined by the following Transact-SQL statement:

  

  The SalesRep column contains the SQL Login name of the user designated as the customer's sales rep.

  You need to create at trigger that meets the following requirements:

  A customer's CreditLimit can only be changed by the customer's SalesRep.

  CreditLimit cannot be increased by more than 50 percent in any single update.

  If an UPDATE statement causes either of these business rules to be violated, the entire UPDATE statement should be rolled back.

  In addition, the trigger must handle single-row and multi-row update statements and should execute in the most efficient manner possible.

  How should you complete the trigger? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar

  between panes or scroll to view content.

  Select and Place:

  

  

  ---

  Box 1:

  The COLUMNS_UPDATED function returns a varbinary bit pattern indicating the inserted or updated columns of a table or view. Use COLUMNS_UPDATED anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test

  whether the trigger should execute certain actions.

  Box 2, Box 3:

  An Example of Using the inserted Table in a Trigger to Enforce Business Rules

  The following example creates a DML trigger. This trigger checks to make sure the credit rating for the vendor is good when an attempt is made to insert a new purchase order into the PurchaseOrderHeader table. To obtain the credit rating of

  the vendor corresponding to the purchase order that was just inserted, the Vendor table must be referenced and joined with the inserted table. If the credit rating is too low, a message is displayed and the insertion does not execute.

  CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader

  AFTER INSERT

  AS

  IF EXISTS (SELECT *

  FROM Purchasing.PurchaseOrderHeader p

  JOIN inserted AS i

  ON p.PurchaseOrderID = i.PurchaseOrderID

  JOIN Purchasing.Vendor AS v

  ON v.BusinessEntityID = p.VendorID

  WHERE v.CreditRating = 5

  )

  BEGIN

  RAISERROR ('A vendor''s credit rating is too low to accept new

  purchase orders.', 16, 1);

  ROLLBACK TRANSACTION;

  RETURN

  END;

  GO

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-2017

• Question 210:

  You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index. The query returns the following six columns:

  - One column in its WHERE clause contained in a non-clustered index.

  -Four additional columns.

  -

  One COUNT (*) column based on a grouping of the four additional columns.

  You need to optimize the statement.

  What should you do?

  A. Add a HASH hint to the query.
  B. Add a LOOP hint to the query.
  C. Add a FORCESEEK hint to the query.
  D. Add an INCLUDE clause to the index.
  E. Add a FORCESCAN hint to the Attach query.
  F. Add a columnstore index to cover the query.
  G. Enable the optimize for ad hoc workloads option.
  H. Cover the unique clustered index with a columnstore index.
  I. Include a SET FORCEPLAN ON statement before you run the query.
  J. Include a SET STATISTICS PROFILE ON statement before you run the query.
  F. Add a columnstore index to cover the query.