ms 70-457 exam (111 to 120)


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-457 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-457 Exam Dumps (PDF & VCE):
Available on: http://www.certleader.com/70-457-dumps.html


70-457 Product Description:
Exam Number/Code: 70-457 vce
Exam name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-457 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

70-457 examcollection

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 May 70-457 Study Guide Questions:

Q111. You generate a daily report according to the following query: 

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

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 


Q112. Your database contains two tables named DomesticSalesOrders and InternationalSalesOrders. Both tables contain more than 100 million rows. Each table has a Primary Key column named SalesOrderId. The data in the two tables is distinct from one another. 

Business users want a report that includes aggregate information about the total number of global sales and total sales amounts. 

You need to ensure that your query executes in the minimum possible time. Which query should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 

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

Reference: http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimalperformance-comparison/ 


Q113. You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. 

UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema. 

You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema. 

Which Transact-SQL statement should you use? 

A. DENY SELECT ON Object::Regions FROM UserA 

B. DENY SELECT ON Object::Regions FROM Sales 

C. REVOKE SELECT ON Schema::Customers FROM Sales 

D. REVOKE SELECT ON Schema::Customers FROM UserA 

E. REVOKE SELECT ON Object::Regions FROM Sales 

F. REVOKE SELECT ON Object::Regions FROM UserA 

G. DENY SELECT ON Schema::Customers FROM Sales 

H. DENY SELECT ON Schema::Customers FROM UserA 

I. EXEC sp_addrolemember 'Sales', 'UserA' 

J. EXEC sp droprolemember 'Sales', 'UserA' 

Answer: H 


70-457  simulations

Improved 70-457 prerequisites:

Q114. You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. 

You grant User1 access to execute the stored procedure. 

You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. 

What should you do? (Each correct answer presents a complete solution. Choose all that apply.) 

A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role. 

B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure. 

C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement. 

D. Grant the db_owner role on the database to User1. 

E. Grant the sysadmin role on the database to User1. 

Answer: B,C 

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


Q115. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. 

You need to prevent users from disabling server audits in Server01. 

What should you create? 

A. an Alert 

B. a Resource Pool 

C. an Extended Event session 

D. a Database Audit Specification 

E. a SQL Profiler Trace 

F. a Server Audit Specification 

G. a Policy 

Answer: G 


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


certleader.com

Precise study material for 70-457:

Q117. You administer a Microsoft SQL Server 2012 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size. 

The database is modified by users only from Monday through Friday from 09:00 hours to 

17:00 hours. Users modify more than 30 percent of the data in the database during the week. 

Backups are performed as shown in the following schedule: 

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete. 

The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times. 

When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly. 

You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time. 

What should you do on Saturday? 

A. Perform a differential backup at 08:59 hours. 

B. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours. 

C. Create a database snapshot at 08:59 hours. 

D. Record the LSN of the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours. 

E. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 17:01 hours. 

F. Create a marked transaction in the transaction log at 08:59 hours. Perform a transaction log backup at 08:59 hours. 

Answer: C 


Q118. You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table. 

You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions: 

. Always starts with 1. 

. Starts again from 1 after it reaches 100. 

Which Transact-SQL statement should you use? 

A. CREATE SEQUENCE CustomerSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALOE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

C. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence 

D. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100 CYCLE UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence 

Answer: B 

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


Q119. 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 = 0 

B. IF (XACT_STATE ( ) ) = 0 

C. IF (XACT_STATE ( ) ) = 1 

D. IF @@TRANCCUNT = l 

Answer: C 

Explanation: 

IF (XACT_STATE()) = 1 -- Test whether the transaction is committable. 

IF (XACT_STATE()) = -1 -- Test whether the transaction is uncommittable. 

@@TRANCOUNT count number of transactions opened. In this case ALWAYS BE at least 

1, because 

CATCH will be invoked only on error before COMMIT. 

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

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


Q120. You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two servers in the same data center that hosts your production database. 

You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs. You also need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention. 

Which configuration should you use? 

A. . Two servers configured in a Windows Failover Cluster in the same data center 

SQL Server configured as a clustered instance 

B. . SQL Server that includes an application database configured to perform transactional replication 

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

D. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

. One server configured as an Active Secondary 

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

F. . Two servers configured in different data centers 

. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode 

G. . SQL Server that includes an application database configured to perform snapshot replication 

H. . Two servers configured on the same subnet 

. SQL Server Availability Group configured in Synchronous-Commit Availability Mode 

Answer: H 

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



see more Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1