Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 71:

  You generate a daily report according to the following query:

  

  The Sales.ufn.GetLastOrderDate user-defined function (UDF) is defined as follows:

  

  You need to improve the performance of the query. What should you do?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

  ---

  We use a Common Table Expression (TBE) to optimize the query with a predicate pushdown.

  Predicate pushdown is an optimization that applies conditions (predicates) as early as possible

• Question 72:

  You use Microsoft SQL Server 2012 to develop a database application.

  You create a stored procedure named DeleteJobCandidate.

  You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number.

  Which Transact-SQL statement should you use?

  A. DECLARE @ErrorVar INT; DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar 0) PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO
  B. DECLARE @ErrorVar INT; DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT; IF (@ErrorVar 0) PRINT N'Error = ' + CAST(ERROR_STATE() AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO
  C. EXEC DeleteJobCandidate IF (ERROR_STATE() != 0) PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO
  D. EXEC DeleteJobCandidate PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO
  A. DECLARE @ErrorVar INT; DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar 0) PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO

  ---

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

• Question 73:

  You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:

  -Takes an input variable.

  -

  Returns a table of values Cannot be referenced within a view. Which object should you use?

  A. Scalar-valued function
  B. Inline function
  C. User-defined data type
  D. Stored procedure
  D. Stored procedure

• Question 74:

  You are the database developer at ABC.com. ABC.com has a SQL Server 2012 database infrastructure that has a database named ComDB with tables named Partners and Events. These tables were created using the following Transact-

  SQL code:

  CREATE TABLE [dbo].[Partners]

  (

  [CompanyID] [int] NOT NULL PRIMARY KEY,

  [CompanyName] [varchar] (150) NOT NULL,

  [Location] [varchar] (150) NOT NULL,

  [ContactName] [varchar] (150) NOT NULL,

  [Email] [varchar] (150) NOT NULL,

  [Phone] [varchar] (10) NOT NULL

  )

  CREATE TABLE [dbo].[Events]

  (

  [EventID] [int] NOT NULL PRIMARY KEY,

  [CompanyID] [int] NOT NULL,

  [EventDescription] [nvarchar] (MAX),

  [EventDate] [nvarchar] (50) NOT NULL,

  [EventCordinator] [nvarchar] (150) NOT NULL

  )

  You add a foreign key relationship between the two tables on the CompanyID column.

  You need to develop a stored procedure named sp_coEvents that retrieves CompanyName for all partners and the EventDate for all events that they have coordinated.

  To answer, type the correct code in the answer area.

  A. Check the answer below
  A. Check the answer below

  ---

  CREATE PROCEDURE sp_coEvent AS SELECT CompanyName, EventDate FROM Partners JOIN Events ON Partners.CompanyID = Events.CompanyID

• Question 75:

  Your database contains a table named Customer that has columns named CustomerID and Name. You want to write a query that retrieves data from the Customer table sorted by Name listing 20 rows at a time. You need to view rows 41 through 60. Which Transact-SQL query should you create?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

• Question 76:

  You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit.

  

  You write the following Transact-SQL query:

  

  You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

  A. Use a FORCESCAN hint in the query.
  B. Add a clustered index on SalesOrderId in SalesOrderHeader.
  C. Use a FORCESEEK hint in the query.
  D. Update statistics on SalesOrderId on both tables.
  D. Update statistics on SalesOrderId on both tables.

  ---

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

• Question 77:

  What is the main advantage of batch mode processing?

  A. It lowers the disk I/O.
  B. It speeds up network transfer.
  C. It lowers the CPU burden.
  D. It uses less memory.
  C. It lowers the CPU burden.

• Question 78:

  DRAG DROP

  You are a developer for a Microsoft SQL Server database. You need to write a stored procedure that performs several operations in the most efficient way possible.

  Which operator or operators should you use? To answer, drag the appropriate operators to the correct operations. Each operator 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: UNION ALL

  UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

  UNION ALL Incorporates all rows into the results. This includes duplicates. If ALL is not specified, duplicate rows are removed.

  Box 2: INTERSECT

  INTERSECT returns distinct rows that are output by both the left and right input queries operator.

  Box 3: INNER JOIN

  The INNER JOIN keyword selects records that have matching values in both tables.

  Box 4: MERGE

  Merge performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences

  found in the other table.

  Box 5: FULL OUTER JOIN

  The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

  Note: FULL OUTER JOIN can potentially return very large result-sets!

  Incorrect Answers:

  EXCEPT returns distinct rows from the left input query that aren't output by the right input query.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-2017

• Question 79:

  Which of the following is applicable to a DELETE statement? (Choose all that apply.)

  A. The statement writes more to the transaction log than TRUNCATE.
  B. The statement resets an IDENTITY property.
  C. The statement is disallowed when a foreign key points to the target table.
  D. The statement is disallowed when an indexed view based on the target table exists.
  A. The statement writes more to the transaction log than TRUNCATE.

• Question 80:

  You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to invoke a table-valued function for each row returned by a query. Which Transact-SQL operator should you use?

  A. CROSS JOIN
  B. UNPIVOT
  C. PIVOT
  D. CROSS APPLY
  D. CROSS APPLY

  ---

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