Microsoft 070-462 : Administering Microsoft SQL Server 2012/2014 Databases

You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured.
When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated.
The following error message is an example of the truncated error message:
"Executed as user CONTOSO\ServiceAccount....0.4035.00 for 64-bit Copyright (C) Microsoft Corp
1984-2011. All rights reserveD. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution fA.
.. The step failed. "
You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.
What should you do?

• A. Expand agent logging to include information from all events.
• B. Disable the Limit size of job history log feature.
• C. Configure output files.
• D. Configure event forwarding.

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement.

Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view.
Which Transact-SQL statement should you use?

• A. CREATE TRIGGER TrgVwEmployeeON VwEmployeeFOR INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName, FROM insertedINSERT INTO Employee(PersonId, EmployeeNumber)SELECT Id, EmployeeNumber FROM insertedEND
• B. CREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName FROM VwEmployeeINSERT INTO Employee(PersonID, EmployeeNumber)SELECT Id, EmployeeNumber FROM VwEmployeeEND
• C. CREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGININSERT INTO Person(Id, FirstName, LastName)SELECT Id, FirstName, LastName, FROM insertedINSERT INTO Employee(PersonId, EmployeeNumber)SELECT Id, EmployeeNumber FROM insertedEND
• D. CREATE TRIGGER TrgVwEmployeeON VwEmployeeINSTEAD OF INSERTASBEGINDECLARE
  @ID INT, @FirstName NVARCHAR(25),@LastName NVARCHAR(25), @PersonID
  INT,@EmployeeNumber NVARCHAR(15)SELECT @ID = ID, @FirstName = FirstName,@LastName
  = LastName, @EmployeeNumber = EmployeeNumberFROM insertedINSERT INTO Person(Id, FirstName, LastName)VALUES(@ID, @FirstName, @LastName)INSERT INTO Employee(PersonID, EmployeeNumber)VALUES(@PersonID, @EmployeeNumberEND

You administer a Microsoft SQL Server database instance that uses transparent database encryption.
You plan to move the database from the current server to a new server by using Backup and Restore.
You need to ensure that the database can be restored to the new server. You also need to ensure that the database remains encrypted at all times.
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.

You have a large partitioned fact table in a data warehouse. The table is stored as a clustered index.
You need to modify the indexing solution to minimize the amount of disk space required to store the table.
What should you do?

• A. Enable row compression for the clustered index.
• B. Implement a nonclustered columnstore index.
• C. Implement a clustered columnstore index.
• D. Enable page compression for the clustered index.

You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report.
You need to display dates and times in several international formats.
What should you do?

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