Choose 70-457 most accurate study material

You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso database has a table named EmployeeSalary in a schema named HumanResources. You need to create a script that writes audit events into the application log whenever data in the EmployeeSalary table is modified. Which Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:

Explanation:
--Burgos - YES (but with different's blocks of commands)
Reference: http://msdn.microsoft.com/en-us/library/cc280386.aspx Reference: http://msdn.microsoft.com/en-us/library/cc280448.aspx Reference: http://msdn.microsoft.com/en-us/library/cc280404.aspx

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do?

• A. Add a LOOP hint to the query.
• B. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
• C. Cover the unique clustered index with a columnstore index.
• D. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
• E. Add a FORCESCAN hint to the Attach query.
• F. Add a HASH hint to the query.
• G. Include a SET FORCEPLAN ON statement before you run the query.
• H. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
• I. Add a FORCESEEK hint to the query.
• J. Add an INCLUDE clause to the index.
• K. Enable the optimize for ad hoc workloads option.
• L. Add a columnstore index to cover the query.
• M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
• N. Include a SET STATISTICS PROFILE ON statement before you run the query.

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data. BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:

Explanation:
--Burgos - NO
Verified answer as correct. Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.
Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx

You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)

The procedure can be called within other transactions. You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails. Which code segment should you add to line 14?

• A. IF @@TRANCOUNT = 1
• B. IF (XACT_STATE ( ) ) = 0
• C. IF (XACT_STATE ( ) ) = 1
• D. IF @@TRANCOUNT = 0

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

You administer all the deployments of Microsoft SQL Server 2012 in your company. You need to ensure that data changes are sent to a non-SQL Server database server in near real time. You also need to ensure that data on the primary server is unaffected. Which configuration should you use?

• A. SQL Server that includes an application database configured to perform snapshot replication
• B. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
• C. Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
• D. 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
• E. SQL Server that includes an application database configured to perform transactional replication
• F. 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
• G. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
• H. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode

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