Microsoft 070-457 Exam Syllabus Topics:

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

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,
LEAD(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, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS
NextProfit
FROM Profits
D) SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS
NextProfit
FROM Profits

2. 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 COLUMNS_CHANGED (HomeNumber, MobileNumber)
- - Create Audit Records
B) CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted)
- - Create Audit Records
C) CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber)
- - Create Audit Records
D) CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber)
- - Create Audit Records

3. You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. You need to configure the SQL Server environment to support these packages. What should you do?

A) Create SSIS configuration files for the packages.
B) Install Master Data services.
C) Install Data Quality Services.
D) Create an Integration Services catalog.

4. You administer a single Microsoft SQL Server instance on a two-node failover cluster that has nodes named Node A and Node B.
The instance is currently running on Node A.
You want to patch both Node A and Node B by using the most recent SQL Server Service Pack. You need to ensure that the following requirements are met:
Both nodes receive the update.
Downtime is minimized.
No data is lost.
Which three 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:

5. You administer a Microsoft SQL Server 2012 server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService. You plan to configure Instant File Initialization. You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all that apply.

A) Disable snapshot isolation.
B) Add the CONTOSO\SQLService account to the Server Operators fixed server role.
C) Restart the SQL Server Agent Service.
D) Enable snapshot isolation.
E) Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
F) Restart the SQL Server Service.

Solutions: