Microsoft 70-457 : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use?

• A. ALTER TABLE Orders ADD CONSTRAINT PK_Orders_CustomerID PRIMARY KEY (CustomerID)
• B. ALTER TABLE Orders ADD CONSTRAINT CK_Orders_CustomerID CHECK (CustomerID IN (SELECT CustomerId FROM Customer))
• C. ALTER TABLE Customer ADD OrderID INT NOT NULL;
  ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);
• D. ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES
  Orders (CustomerID)
• E. ALTER TABLE Orders ADD CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)

You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and
backup schedule are configured as shown in the following table:

One of the hard disk drives that stores the reporting database fails at 16:40 hours. You need to ensure that
the reporting database is restored. You also need to ensure that data loss is minimal.
What should you do?

• A. Perform a partial restore.
• B. Perform a point-in-time restore.
• C. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• D. Perform a page restore.
• E. Restore the latest full backup.
• F. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• G. Restore the latest full backup. Then, restore the latest differential backup.
• H. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.

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 DATETIME2 data type.
• C. Use the TODATETIMEOFFSET function.
• D. Use the DATETIME data type.
• E. Use the VARBINARY data type.
• F. Use the DATETIMEOFFSET data type.
• G. Use the CAST function.
• H. Use the DATE data type.
• I. Use the FORMAT function.
• J. Use an appropriate collation.

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition:

You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

• A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber)
  - - Create Audit Records
• B. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber)
  - - Create Audit Records
• C. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted)
  - - Create Audit Records
• D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber)
  - - Create Audit Records

You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
Returns a result set based on supplied parameters.
Enables the returned result set to perform a join with a table.
Which object should you use?

• A. Inline user-defined function
• B. Scalar user-defined function
• C. Table-valued user-defined function
• D. Stored procedure