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
  INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
• B. 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
• 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 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

Explanation: Only visible for PrepAwayPDF members. You can sign-up / login (it's free).

HOTSPOT You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition: You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard. You need to ensure that the following requirements are met:

The first row of the file contains the first row of data.
Each record is of the same length.
The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)
Hot Area:

Explanation:
--Burgos - NO
Reference: http://msdn.microsoft.com/en-us/library/ms178804.aspx Reference: http://msdn.microsoft.com/en-us/library/ms187828.aspx

You administer a single server that contains a Microsoft SQL Server 2012 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions. You need to ensure that the application login is unable to access other production databases. What should you do?

• A. Install a new default SQL Server instance on the server.
• B. Install a new named SQL Server instance on the server.
• C. Use the SQL Server default instance and enable Contained Databases.
• D. Use the SQL Server default instance and configure an affinity mask.

Explanation: Only visible for PrepAwayPDF members. You can sign-up / login (it's free).

You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named Node 1 and Node 2. Node 1 fails and the cluster fails over to Node 2. You need to replace Node 1 and add it to the cluster. 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.)
Build List and Reorder:

Explanation:
--Burgos - YES
Reference: http://technet.microsoft.com/en-us/library/ms181075.aspx

You develop a database for a travel application. You need to design tables and other database objects. You need to store media files in several tables. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently. What should you do?

• A. Use a user-defined table type.
• B. Use the CAST function.
• C. Use the VARBINARY data type.
• D. Use the FORMAT function.
• E. Use the DATE data type.
• F. Use the DATETIME2 data type.
• G. Use the DATETIMEOFFSET data type.
• H. Use an appropriate collation.
• I. Use the TODATETIMEOFFSET function.
• J. Use the DATETIME data type.

Explanation: Only visible for PrepAwayPDF members. You can sign-up / login (it's free).