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. Include a SET FORCEPLAN ON statement before you run the query.
• C. Add a FORCESCAN hint to the Attach query.
• D. Enable the Optimize for ad hoc workloads option.
• E. Add a LOOP hint to the query.
• F. Include a SET STATISTICS PROFILE ON statement before you run the query.
• G. Add a FORCESEEK hint to the query.
• H. Add an INCLUDE clause to the index.
• I. Cover the unique clustered index with a columnstore index.
• J. Add a columnstore index to cover the query.

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. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
• B. 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.
• C. 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.
• D. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
• E. Run the sp_who command from a query window.

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?

• A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  COUNT(OrderAmount) DESC) AS OrderAmount
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  ORDER BY OrderAmount DESC
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  RANK() OVER (PARTITION BY CustomerID
  ORDER BY OrderAmount DESC) AS Rnk
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  WHERE o.Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID
  ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
  FROM Customer c
  INNER JOIN Orders o
  ON c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs
  WHERE Rnk = 1
• D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
  (SELECT CustomerID, ShippingCountry,
  RANK() OVER (PARTITION BY CustomerID
  ORDER BY COUNT(OrderAmount) DESC) AS Rnk
  FROM Orders
  GROUP BY CustomerID, ShippingCountry) AS o
  ON c.CustomerID = o.CustomerID
  WHERE o.Rnk = 1

You administer two Microsoft SQL Server 2012 servers. Each server resides in a different, untrusted domain. You plan to configure database mirroring. You need to be able to create database mirroring endpoints on both servers. What should you do?

• A. Use a database certificate.
• B. Use a server certificate.
• C. Configure the SQL Server service account to use Network Service.
• D. Configure the SQL Server service account to use Local System.