Online Access Free 70-457 Practice Test

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.
<CUSTOMERS Name="Customer A" Country="Australia"> <ORDERS OrderID="1" OrderDate="2001-01-01" Amount="3400.00" /> <ORDERS OrderID="2" OrderDate="2002-01-01" Amount="4300.00" />
</CUSTOMERS>
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 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')
• C.SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
• D.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')
• E.SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
• F.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
• G.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS
• H.SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

• A.Update statistics on SalesOrderId on both tables.
• B.Add a clustered index on SalesOrderId in SalesOrderHeader.
• C.Use a FORCESCAN hint in the query.
• D.Use a FORCESEEK hint in the query.

You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks. You need to prepare the AdventureWorks database for database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA. 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.)
Build List and Reorder:

Correct Answer:

Explanation:
--Burgos - YES
I don't think this question will ever come up. The MSDN link says it is being deprecated and to use Availability Groups instead: http://msdn.microsoft.com/en-us/library/ms189852.aspx
However, the answer is correct. http://msdn.microsoft.com/en-us/library/ms190941.aspx

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named DeleteJobCandidate. You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number. Which Transact-SQL statement should you use?

• A.DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;
  EXEC DeleteJobCandidate
  SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT;
  IF (@ErrorVar <> 0)
  PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) +
  N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
  GO
• B.DECLARE @ErrorVar INT; DECLARE @RowCountVar INT;
  EXEC DeleteJobCandidate
  SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT;
  IF (@ErrorVar <> 0)
  PRINT N'Error = ' + CAST(ERRORSTATE() AS NVARCHAR(8)) +
  N', Rows Deleted = ' + CAST(@@RowCountVar AS NVARCHAR(8));
  GO
• C.EXEC DeleteJobCandidate
  IF (ERROR_STATE() != 0)
  PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
  N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
  GO
• D.EXEC DeleteJobCandidate
  PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) +
  N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
  GO

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 minimum amount of data is lost. Which recovery model should the database use?

• A.CONTINUE_AFTER_ERROR
• B.STANDBY
• C.SKIP
• D.NO_CHECKSUM
• E.Differential
• F.COPY_ONLY
• G.CHECKSUM
• H.DBO_ONLY
• I.Transaction log
• J.FULL
• K.SIMPLE
• L.NORECOVERY
• M.BULK_LOGGED
• N.RESTART