Apr 2016 updated: books for exam 70-457

Exam Code: 70-457 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-457 Exam.

2016 Apr 70-457 Study Guide Questions:

Q1. You administer a Microsoft SQL Server 2012 server. 

When transaction logs grow, SQL Server must send an email message to the database administrators. 

You need to configure SQL Server to send the email messages. 

What should you configure? 

A. SQL Mail 

B. An Extended Events session 

C. Alerts and operators in SQL Server Agent 

D. Policies under Policy-Based Management 

Answer: C 


Q2. 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. STANDBY 

B. Differential 

C. FULL 

D. CHECKSUM 

E. BULK_LOGGED 

F. CONTINUE_AFTER_ERROR 

G. SIMPLE 

H. DBO_ONLY 

I. COPY_ONLY 

J. SKIP 

K. RESTART 

L. Transaction log 

M. NO_CHECKSUM 

N. NORECOVERY 

Answer: F 

Reference: http://msdn.microsoft.com/en-us/library/ms186865.aspx 

Reference: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backuprestorebase.continueaftererror.aspx 


Q3. DRAG DROP 

You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012. 

The development team develops a new application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application. 

You need to migrate the database without affecting the production environment. 

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.) 

Answer: 


Q4. You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages. 

You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile. 

You need to ensure that AgentPortalUser can send email messages. 

What should you do? 

A. In the Database Mail Configuration Wizard, configure the Database Mail profile as a private profile for the AgentPortalUser account. 

B. Disable the guest user in the msdb database. 

C. Use the sysmail_help_profileaccount_sp stored procedure to add accounts to the Database Mail profile. 

D. In the Database Mail Configuration Wizard, create an email account for each recipient's email address in the Database Mail profile. 

Answer: A 

Explanation: 

Http://msdn.microsoft.com/en-us/library/ms189635.aspx 


Q5. You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.) 

You write the following Transact-SQL query: 

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. 

You need to improve the performance of the query. What should you do? 

A. Use a FORCESCAN hint in the query. 

B. Add a clustered index on SalesOrderId in SalesOrderHeader. 

C. Use a FORCESEEK hint in the query. 

D. Update statistics on SalesOrderId on both tables. 

Answer: D 

Explanation: http://msdn.microsoft.com/en-us/library/ms187348.aspx 


70-457 braindumps

Avant-garde how many questions in exam 70-457:

Q6. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. 

Which Transact-SQL statement should you include at the beginning of the stored procedure? 

A. SET XACT_ABORT ON 

B. SET ARITHABORT ON 

C. TRY 

D. BEGIN 

E. SET ARITHABORT OFF 

F. SET XACT_ABORT OFF 

Answer: A 

Reference: http://msdn.microsoft.com/en-us/library/ms190306.aspx 

Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx 


Q7. You administer a Microsoft SQL Server 2012 database that includes a table named 

Products. The Products table has columns named Productld, ProductName, and CreatedDateTime. 

The table contains a unique constraint on the combination of ProductName and CreatedDateTime. 

You need to modify the Products table to meet the following requirements: 

. Remove all duplicates of the Products table based on the ProductName column. . Retain only the newest Products row. 

Which Transact-SQL query should you use? 

A. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON 

B. ProductName = cte.ProductName AND p.CreatedDateTime > cte.CreatedDateTime 

C. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON cte.ProductName = p.ProductName AND cte.CreatedDateTime > p.CreatedDateTime 

D. WITH CTEDupRecords AS ( SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName ) DELETE p FROM Products p JOIN CTEDupRecords cte ON 

E. ProductName = cte.ProductName 

F. WITH CTEDupRecords AS ( SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM Products GROUP BY ProductName HAVING COUNT(*) > 1 ) DELETE p FROM Products p JOIN CTEDupRecords cte ON 

G. ProductName = cte.ProductName 

Answer: B 


Q8. 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. an Alert 

B. a Server Audit Specification 

C. an Extended Event session 

D. a Resource Pool 

E. a Policy 

F. a SQL Profiler Trace 

G. a Database Audit Specification 

Answer: A 


Q9. You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. 

There is high contention between readers and writers on several tables used by your transaction. 

You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. 

Which isolation level should you use? 

A. Create a user-defined type on the master database. 

B. Create a user-defined data type on the model database. 

C. Create a user-defined type on the model database. 

D. Create a user-defined data type on the master database. 

Answer: C 


Q10. You develop a Microsoft SQL Server 2012 server database that supports an application. The application contains a table that has the following definition: 

CREATE TABLE Inventory 

(ItemID int NOT NULL PRIMARY KEY, 

ItemsInStore int NOT NULL, 

ItemsInWarehouse int NOT NULL) 

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row. 

Which Transact-SQL statement should you use? 

A. ALTER TABLE Inventory 

ADD TotalItems AS ItemsInStore + ItemsInWarehouse 

B. ALTER TABLE Inventory 

ADD ItemsInStore - ItemsInWarehouse = TotalItems 

C. ALTER TABLE Inventory 

ADD TotalItems = ItemsInStore + ItemsInWarehouse 

D. ALTER TABLE Inventory 

ADD TotalItems AS SUM(ItemsInStore, ItemslnWarehouse); 

Answer: A 

Explanation: 

Http://technet.microsoft.com/en-us/library/ms190273.aspx 


70-457 practice exam

Pinpoint sql server certification 70-457:

Q11. You support a database structure shown in the exhibit. (Click the Exhibit button.) 

You need to write a query that displays the following details: 

Total sales made by sales people, year, city, and country 

Sub totals only at the city level and country level 

A grand total of the sales amount 

Which Transact-SQL query should you use? 

A. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson 

ON Sale.SalesPersonID = SalesPerson.SalesPersonID 

GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, 

SaleDate)), (Country, City), (Country), ()) 

B. SELECT SalesPerson.Name, Country, City, 

DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total 

FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)) 

C. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country) 

D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country) 

Answer: A 

Reference: http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/ 

Reference: http://msdn.microsoft.com/en-us/library/ms177673.aspx 


Q12. DRAG DROP 

You administer a Microsoft SQL Server 2012 server that has multiple databases. 

You need to ensure that users are unable to create stored procedures that begin with sp_. 

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.) 

Answer: 


Q13. DRAG DROP 

You use Microsoft SQL Server 2012 to develop a database application. 

You create a table by using the following definition: 

CREATE TA3LE Prices 

Priceld int IDENTITY(1,1) PRIMARY KEY, 

ActualPrice NUMERIC(16,9), 

PredictedPrice NUMERIC(16,9) 

You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. 

Which three 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.) 

Answer: 


Q14. DRAG DROP 

You administer a Microsoft SQL Server database that is used by an application. 

Users of the application report performance issues. 

You need to choose the appropriate tool for performance-tuning of SQL Server databases. 

Which tool or tools should you use? (To answer, drag the appropriate tool or tools to their corresponding task or tasks in the answer area. Each tool may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.) 

Answer: 


Q15. DRAG DROP 

You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions. 

Which five 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.) 

Answer: 


Q16. 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 previous year. 

Which Transact-SQL query should you use? 

A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits 

D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits 

Answer: C 

Explanation: 

http://msdn.microsoft.com/en-us/library/hh231256.aspx http://msdn.microsoft.com/en-us/library/hh213125.aspx 



see more http://www.certshared.com/exam/70-457/

Microsoft 70-457 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-457-dumps/

P.S. New 70-457 dumps PDF: http://www.4easydumps.com/70-457-dumps-download.html