Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 121:

  You administer a single Microsoft SQL Server instance on a two-node failover cluster that has nodes named Node A and Node B. The instance is currently running on Node A. You want to patch both Node A and Node B by using the most recent SQL Server Service Pack.

  You need to ensure that the following requirements are met: Both nodes receive the update.

  Downtime is minimized. No data is lost.

  Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 122:

  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.)

  

  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. DateHired
  B. DepartmentID
  C. EmployeeID
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  C. EmployeeID

• Question 123:

  You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores. You discover performance issues when large amounts of data are written to tables under heavy system load.

  You need to limit the number of cores that handle I/O.

  What should you configure?

  A. Processor affinity
  B. Lightweight pooling
  C. Max worker threads
  D. I/O affinity
  D. I/O affinity

• Question 124:

  You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.

  You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum

  permissions required.

  What should you do? (Each correct answer presents a complete solution. Choose all that apply.)

  A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.
  B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
  C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
  D. Grant the db_owner role on the database to User1.
  E. Grant the sysadmin role on the database to User1.
  B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
  C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.

• Question 125:

  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. 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.
  I. Include a SET FORCEPLAN ON statement before you run the query.

• Question 126:

  You administer a Microsoft SQL Server 2012 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

• Question 127:

  You administer a Microsoft SQL Server database that supports a shopping application. You need to retrieve a list of customers who live in territories that do not have a sales person. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

  A. SELECT CustomerID FROM Customer WHERE TerritoryID SOME(SELECT TerritoryID FROM Salesperson)
  B. SELECT CustomerID FROM Customer WHERE TerritoryID ALL(SELECT TerritoryID FROM Salesperson)
  C. SELECT CustomerID FROM Customer WHERE TerritoryID ANY(SELECT TerritoryID FROM Salesperson)
  D. SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
  B. SELECT CustomerID FROM Customer WHERE TerritoryID ALL(SELECT TerritoryID FROM Salesperson)
  D. SELECT CustomerID FROM Customer WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)

• Question 128:

  You administer a Microsoft SQL Server 2012 server. One of the databases on the server supports a highly active OLTP application.

  Users report abnormally long wait times when they submit data into the application. You need to identify which queries are taking longer than 1 second to run over an extended period of time.

  What should you do?

  A. Use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000.
  B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
  C. Run the sp_who command from a query window.
  D. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
  E. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.
  A. Use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000.

• Question 129:

  You have a view that was created by using the following code:

  

  You need to create an inline table-valued function named Sales.fn_OrdersByTerritory.

  Sales.fn_OrdersByTerritory must meet the following requirements:

  *Accept the @T integer parameter.

  *Use one-part names to reference columns.

  *Filter the query results sorted by SalesTerritoryID.

  *Return the columns in the same order as the order used in OrdersByTerritoryView.

  Which code segment should you use? To answer, type the correct code in the answer area.

  Check the answer in explanation.

  CREATE FUNCTION Sales.fn_OrdersByTerritory (@T int) RETURNS TABLE AS RETURN ( SELECT OrderID,OrderDate,SalesTerrirotyID,TotalDue FROM Sales.OrdersByTerritory WHERE SalesTerritoryID = @T )

• Question 130:

  You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server database.

  When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server.

  You need to ensure that the SQL Server Agent job can execute the SSIS package.

  Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place: