Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 131:

  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 ARITHABORT ON
  C. TRY
  D. BEGIN
  E. SET ARITHABORT OFF
  F. SET XACT_ABORT OFF
  A. SET XACT_ABORT ON

• Question 132:

  You administer three Microsoft SQL Server 2008 R2 instances.

  Database mirroring is configured in High-Safety mode with Automatic Failover between the following three servers:

  SQL1 is the Principal server.

  SQL2 is the mirror server.

  SQL3 is the witness server.

  You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that downtime is minimized during the upgrade.

  Which six 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 133:

  You administer a Microsoft SQL Server 2012 instance that contains a database of confidential data.

  You need to encrypt the database files at the page level. You also need to encrypt the transaction log files.

  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:

  

  

• Question 134:

  You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). The financial database has the following characteristics:

  A data file of 2 terabytes is located on a dedicated LUN (drive D). A transaction log of 10 GB is located on a dedicated LUN (drive E).

  Drive D has 1 terabyte of free disk space.

  Drive E has 5 GB of free disk space.

  The database is continually modified by users during business hours from Monday through Friday between

  09:00 hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

  These data load operations must occur in the minimum amount of time.

  A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that the backup size is as small as possible.

  Which backup should you perform every two hours?

  A. BULK_LOGGED
  B. NO_CHECKSUM
  C. FULL
  D. RESTART
  E. CHECKSUM
  F. STANDBY
  G. DBO.ONLY
  H. NORECOVERY
  I. SIMPLE
  J. Transaction log
  J. Transaction log

• Question 135:

  You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version.

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

• Question 136:

  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 TRANSACTION ISOLATION LEVEL SERIALIZA3LE
  B. SET XACT_ABORT OFF
  C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
  D. SET IMPLICIT_TRANSACTIONS ON
  E. SET XACT_ABORT ON
  F. SET IMPLICIT TRANSACTIONS OFF
  E. SET XACT_ABORT ON

• Question 137:

  You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

  

  You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

  

  Which Transact-SQL query should you use?

  A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
  B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
  C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
  D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerXd - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId- 1 FOR XML AUTO
  F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS
  G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')
  D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerXd - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

• Question 138:

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

  What should you do? Choose all that apply.

  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',
  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',
  C. Execute the Reconfigure command.

• Question 139:

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

  When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required detail.

  The following error message is an example error message:

  "The job failed. The Job was invoked by User CONTOSO\ServiceAccount. The last step to run was step 1 (Subplan_1)."

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

  What should you do?

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

• Question 140:

  You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.

  

  You need to improve the performance of the view by persisting data to disk. What should you do?

  A. Create an INSTEAD OF trigger on the view.
  B. Create an AFTER trigger on the view.
  C. Modify the view to use the WITH VIEW_METADATA clause.
  D. Create a clustered index on the view.
  D. Create a clustered index on the view.