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 a large amount of memory is consumed by single-use dynamic queries. You need to reduce procedure cache usage from these statements without creating any additional indexes. What should you do?

• A. Add a FORCESCAN hint to the Attach query.
• B. Add a LOOP hint to the query.
• C. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
• D. Cover the unique clustered index with a columnstore index.
• E. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
• F. Add a HASH hint to the query.
• G. Enable the optimize for ad hoc workloads option.
• H. Include a SET STATISTICS PROFILE ON statement before you run the query.
• I. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
• J. Add an INCLUDE clause to the index.
• K. Add a columnstore index to cover the query.
• L. Include a SET FORCEPLAN ON statement before you run the query.
• M. Add a FORCESEEK hint to the query.
• N. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

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:
<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00"
Name="Customer A" Country="Australia" />
<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00"
Name="Customer A" Country="Australia" />
Which Transact-SQL query should you use?

• A. 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')
• 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.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
• E. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
• F. 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')
• G. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
• H. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use?select

• A. SELECT
  IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False')
  AS BadCast
• B. BEGIN TRY
  SELECT convert(decimal(36,9), @var) AS Value, 'True' AS BadCast
  END TRY
  BEGIN CATCH
  SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast
  END CATCH
• C. SELECT
  CASE
  WHEN convert(decimal(36,9), @var) IS NULL
  THEN 'True'
  ELSE 'False'
  END
  AS BadCast
• D. TRY( SELECT convert(decimal(36,9), @var) SELECT 'True' AS BadCast
  )
  CATCH(
  SELECT 'False' AS BadCast
  )

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You need to create a query that calculates the total sales of each OrderId from the Sales.Details table. The solution must meet the following requirements:
Use one-part names to reference columns.
Sort the order of the results from OrderId.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderId column and the calculated TotalSales column.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A. SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details ORDER BY OrderID
• B. SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details GROUP BY OrderID ORDER BY OrderID

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

• A. a recursive stored procedure
• B. Trigger
• C. The foreach SQLCLR statement
• D. Cursor
• E. An UPDATE statement that includes CASE