Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 211:

  Which of the following is NOT a valid recovery model for an SQL 2012 database?

  A. ArchiveLog
  B. Simple
  C. Bulk-logged
  D. Full
  A. ArchiveLog

  ---

  References: https://msdn.microsoft.com/en-us/library/ms189275%28v=sql.110%29.aspx

• Question 212:

  Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.

  Which Transact-SQL query should you use?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  D. OptionD

  ---

  Reference: http://technet.microsoft.com/en-us/library/ms181034.aspx

• Question 213:

  You administer a Microsoft SQL Server instance that has several SQL Server Agent jobs configured.

  When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated.

  The following error message is an example of the truncated error message:

  "Executed as user CONTOSO\ServiceAccount....0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserveD. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code

  0x00000000 Source Log Import Activity Descript... The package execution fA. .. The step failed. "

  You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.

  What should you do?

  A. Expand agent logging to include information from all events.
  B. Disable the Limit size of job history log feature.
  C. Configure event forwarding.
  D. Configure output files.
  D. Configure output files.

• Question 214:

  Which of the following is a valid data source which you can import into SQL Server 2012?

  A. Oracle data file
  B. .DOC file
  C. Access 2010
  D. Oracle export file
  C. Access 2010

  ---

  References: https://msdn.microsoft.com/en-us/library/ms141209%28v=sql.110%29.aspxv

• Question 215:

  You develop a Microsoft SQL Server database that contains a heap named OrdersHistorical.

  You write the following Transact-SQL query: INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders

  You need to optimize transaction logging and locking for the statement.

  Which table hint should you use?

  A. HOLDLOCK
  B. ROWLOCK
  C. XLOCK
  D. UPDLOCK
  E. TABLOCK
  E. TABLOCK

  ---

  Reference: http://technet.microsoft.com/en-us/library/ms189857.aspx http://msdn.microsoft.com/en-us/library/ms187373.aspx

• Question 216:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database. The database contains two tables named Table A and Table B.

  You perform an import and export operation by using the following steps in sequence, as shown in the exhibit. (Click the Exhibit button.)

  

  Load data from a third-party database to Table

  A. Insert a subset of records from Table A to Table B. Export the data from Table B to a flat file.

  You also create a format file for the data export.

  You need to choose the correct command or tool for each task. You need to ensure that each task is performed efficiently.

  Which command(s) or tool(s) should you use? (To answer, drag the appropriate command(s) or tool(s) to their corresponding task or tasks in the answer area. Answer choices may be used once, more than once, or not at all.

  Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

• Question 217:

  You administer a Microsoft SQL Server default instance.

  The instance is hosted by a server that has a local firewall configured. The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server.

  You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections.

  Which three actions should you perform? Each correct answer presents part of the solution.

  A. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
  B. Execute the following Transact-SQL command: sp_configure 'remote admin connections',1
  C. Execute the Reconfigure command.
  D. Execute the following Transact-SQL command: sp_configure 'remote access', 1
  E. Restart the SQL Server Agent Service.
  F. Enable inbound connections on TCP port 135 in the Windows Firewall on the server.
  A. Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
  B. Execute the following Transact-SQL command: sp_configure 'remote admin connections',1
  C. Execute the Reconfigure command.

  ---

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

• Question 218:

  HOTSPOT

  You have a server named SQL1 that hosts a reporting database named REPORTING. REPORTING contains data that is also stored in a production database.

  You discover many blocking locks in REPORTING. The blocked queries are SELECT statements. The queries that hold the blocking locks are INSERT statements that wait on the WRITELOG wait type.

  You need to prevent the INSERT statements from creating the blocking locks. The solution must also minimize the amount of time the INSERT statements wait on the WRITELOG wait type.

  Which two database settings should you modify? To answer, select the appropriate settings in the answer area.

  Hot Area:

  

  

  ---

  Box 1: Allow snapshot isolation

  In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

  The SNAPSHOT isolation level.

  Box 2: Delayed Durability

  Starting with SQL Server 2014 the DELAYED_DURABILITY is added as a new option for transaction commits with one single aim: a tradeoff of transaction durability for better performance. Using this option in SQL Server where excessive

  WRITELOG wait type is present could be significant.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

  https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

• Question 219:

  You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while

  still providing access to the required data.

  You need to ensure that the following requirements are met:

  Future modifications to the table definition will not affect the applications' ability to access data.

  The new object can accommodate data retrieval and data modification.

  You need to achieve this goal by using the minimum amount of changes to the applications. What should you create for each application?

  A. Synonyms
  B. Common table expressions
  C. Views
  D. Temporary tables
  C. Views

  ---

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

• Question 220:

  DRAG DROP

  You administer a Microsoft SQL Server 2012 database.

  Your database is experiencing deadlock issues.

  You need to be able to monitor deadlocks.

  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: