Certification vendors revise their blueprints without much warning, and stale material is worse than none at all. 2Pass4sure reviews and updates its 070-457 practice questions on a continuous basis, with 365 days of free updates, so the 172 items you study stay aligned with the live Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 exam.

Microsoft 070-457 Exam Overview:

Microsoft 070-457 Exam Syllabus Topics:

Microsoft 070-457 Exam FAQ — What Candidates Ask Most

Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:

Question 1

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its preceding year. Which Transact-SQL query should you use?

A. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits
B. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits
C. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits
D. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits

Question 2

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index. The query returns the following six columns: * One column in its WHERE clause contained in a non-clustered index * Four additional columns * One COUNT (*) column based on a grouping of the four additional columns.
You need to optimize the statement. What should you do?

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

Question 3

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

A. Cursor
B. Trigger
C. An UPDATE statement that includes CASE
D. The foreach SQLCLR statement
E. a recursive stored procedure

Question 4

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between
09:00
hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. These data load operations must occur in the minimum amount of time. A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

A. COPY_ONLY
B. CHECKSUM
C. NO_CHECKSUM
D. Differential
E. Transaction log
F. STANDBY
G. BULK_LOGGED
H. SKIP
I. CONTINUE_AFTER_ERROR
J. SIMPLE
K. RESTART
L. DBO_ONLY
M. NORECOVERY
N. FULL

Question 5

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to be notified immediately when fatal errors occur on Server01. What should you create?

A. a Database Audit Specification
B. a Resource Pool
C. an Alert
D. a Policy
E. a Server Audit Specification
F. an Extended Event session
G. a SQL Profiler Trace

Solutions: