Online Access Free 70-461 Exam Questions

You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)

The procedure can be called within other transactions.
You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails.
Which code segment should you add to line 14?

• A.IF (XACT_STATE ( ) ) = 1
• B.IF (XACT_STATE ( ) ) = 0
• C.IF @@TRANCOUNT = l
• D.IF @@TRANCOUNT = 0

You administer a SQL Server database that tracks sales that are made by sales persons. The database contains a table that is defined by the following Transact-SQL statements:

You have the following requirements:
* accept a datetime value for the query month
* return a list of salespeople IDs who have sales in the query month or before the query month
* compare sales with sales quota for salespeople who have a sales quota
* display year-to-date sales for salespeople that do not have a sales quota How should you complete the stored procedure? 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.

Correct Answer:

Explanation:
Box 1: IIF ..
The IIF logical function returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.
Syntax: IIF ( boolean_expression, true_value, false_value )
Box 2: [LastSzale] < EOMONTH(@queryMonth)
The EOMONTH function returns the last day of the month containing a specified date, with an optional offset.
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

• A.Change the Merge agent on the problem subscribers to run continuously.
• B.Set the Merge agent on the problem subscribers to use the slow link agent profile.
• C.Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
• D.Set the Remote Connection Timeout on the Publisher to 0.

You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A.CREATE VIEW sales.uv_CustomerFullNames
  WITH SCHEMABINDING
  AS SELECT
  FirstName,
  LastName
  FROM Sales.Customers
• B.CREATE VIEW sales.uv_CustomerFullNames
  WITH SCHEMABINDING
  AS SELECT
  FirstName,
  FROM Sales.Customers

You develop a database for a travel application. You need to design tables and other database objects.
You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?

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