Free Microsoft 70-461 Exam Dumps Questions & Answers

You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

• A.  SET XACT_ABORT ON
• B.  SET TRANSACTION ISOLATION LEVEL SNAPSHOT
• C.  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• D.  SET XACT_ABORT OFF
• E.  SET IMPLICIT_TRANSACTIONS ON
• F.  SET IMPLICIT_TRANSACTIONS OFF

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?

• A.  Domain user
• B.  SQL user with login
• C.  SQL user with a custom SID
• D.  SQL user without login

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility.
On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?

• A.  ReportsToID
• B.  MiddleName
• C.  JobTitle
• D.  FirstName
• E.  DateHired
• F.  DepartmentID
• G.  LastName
• H.  EmployeeID
• I.  EmployeeNum

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.
The tables have the following definitions:

Users are able to use single INSERT statements or INSERT...SELECT statements into this view.
You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?

• A.  CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  INSTEAD OF INSERT
  AS
  BEGIN
  DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15) SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, @EmployeeNumber
  = EmployeeNumber
  FROM inserted
  INSERT INTO Person(Id, FirstName, LastName)
  VALUES(@ID, @FirstName, @LastName)
  INSERT INTO Employee(PersonID, EmployeeNumber)
  VALUES(@PersonID, @EmployeeNumber
  End
• B.  CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  INSTEAD OF INSERT
  AS
  BEGIN
  INSERT INTO Person(Id, FirstName, LastName)
  SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END
• C.  CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  FOR INSERT
  AS
  BEGIN
  INSERT INTO Person(Id, FirstName, LastName)
  SELECT Id, FirstName, LastName, FROM inserted
  INSERT INTO Employee(PersonId, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM inserted
  END
• D.  CREATE TRIGGER TrgVwEmployee
  ON VwEmployee
  INSTEAD OF INSERT
  AS
  BEGIN
  INSERT INTO Person(Id, FirstName, LastName)
  SELECT Id, FirstName, LastName FROM VwEmployee
  INSERT INTO Employee(PersonID, EmployeeNumber)
  SELECT Id, EmployeeNumber FROM VwEmployee
  End

You are the administrator for a heavily-used OLTP Microsoft SQL Server database.
You are troubleshooting performance issues seen when using stored procedures in the database. The database stores millions of orders across thousands of customers. Some of the customers have large numbers of orders, while others have only one order. You update the statistics and perform defragmentation of all tables and indexes, but two stored procedures still have issues when accessing data.
p_GetCustomer accepts @companyID as a parameter. From the results of profiling, you know that 90 percent of the calls use the @companyid value of 5, while the other 10 percent of calls are evenly distributed across another 10000 values. While viewing the execution plan, you discover that a non-clustered index seek is used.
p_GetShipDate accepts @orderID as a parameter and returns the ship date for that order. You discover that the execution plan is performing a scan on a non-clustered index that has orderID as the index key.
You need to add appropriate query hints to each stored procedure to improve the performance.
What should you do? To answer, drag the appropriate procedures to the correct hints. Each procedure 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: Optimize FOR..
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] ) Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.
Box 2: FORCESEEK
FORCESEEK [ (index_value(index_column_name [ ,... n ] )) ]
Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017