Instantly download 070-457 training test engine

Microsoft 070-457 Exam Overview:

Microsoft 070-457 Exam Syllabus Topics:

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

1. 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:

2. Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

A) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
C) SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
D) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()

3. You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

A) Use the Data Quality Client to configure the application.
B) Start the SQL Server Integration Services Service.
C) Use the Master Data Services Configuration Manager to configure the application.
D) Start the SQL Server Browser Service.

4. You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks:
Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports. You need to improve the performance of the reports.
What should you do?

A) Convert the view into an indexed view.
B) Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.
C) Convert the view into a Common Table Expression (CTE).
D) Convert the view into a table-valued function.

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

Solutions: