Study with 070-457 most valid questions & verified answers

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail. You also need to minimize the amount of storage used by the database. Which configuration should you use?

• A. * Two servers configured in different data centers * SQL Server Availability Group configured in Synchronous-Commit Availability Mode * One server configured as an Active Secondary
• B. * Two servers configured in the same data center * A primary server configured to perform log-shipping every 10 minutes * A backup server configured as a warm standby
• C. * Two servers configured in different data centers * SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• D. * Two servers configured on the same subnet * SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• E. * Two servers configured in the same data center * SQL Server Availability Group configured in Asynchronous-Commit Availability Mode * One server configured as an Active Secondary
• F. * SQL Server that includes an application database configured to perform transactional replication
• G. * Two servers configured in a Windows Failover Cluster in the same data center * SQL Server configured as a clustered instance
• H. * SQL Server that includes an application database configured to perform snapshot replication

You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you a create a unique constraint?

• A. LastName
• B. MiddleName
• C. JobTitle
• D. DateHired
• E. DepartmentID
• F. ReportsToID
• G. EmployeeNum
• H. FirstName
• I. EmployeelD

You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar(max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do?

• A. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
• B. Change the Merge agent on the problem subscribers to run continuously.
• C. Set the Merge agent on the problem subscribers to use the slow link agent profile.
• D. Set the Remote Connection Timeout on the Publisher to 0.

Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. You need to ensure that your query executes in the minimum possible time. Which query should you use?

• A. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM (
  SELECT SalesOrderId, SalesAmount
  FROM DomesticSalesOrders
  UNION ALL
  SELECT SalesOrderId, SalesAmount
  FROM InternationalSalesOrders
  ) AS p
• B. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders
• C. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM DomesticSalesOrders UNION ALL SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount FROM InternationalSalesOrders
• D. SELECT COUNT(*) AS NumberOfSales, SUM(SalesAmount) AS TotalSalesAmount
  FROM (
  SELECT SalesOrderId, SalesAmount
  FROM DomesticSalesOrders
  UNION
  SELECT SalesOrderId, SalesAmount
  FROM InternationalSalesOrders
  ) AS p

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. You use a third table named Transactions that has columns named TransactionId, AccountNumber, Amount, and TransactionDate. You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. Which Transact-SQL statement should you use?

• A. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
  IF EXISTS (
  SELECT AccountNumber FROM inserted EXCEPT
  (SELECT AccountNumber FROM LoanAccounts
  UNION SELECT AccountNumber FROM SavingAccounts))
  BEGIN
  ROLLBACK TRAN
  END
  END
• B. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
  IF EXISTS (
  SELECT AccountNumber FROM inserted EXCEPT
  (SELECT AccountNumber FROM LoanAccounts
  UNION SELECT AccountNumber FROM SavingAccounts))
  BEGIN
  ROLLBACK TRAN
  END
  END
• C. CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
  INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
• D. CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
  INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END