70-462 item pool(31 to 45) for candidates: Apr 2016 Edition

70-462 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

Product Description:
Exam Number/Code: 70-462
Exam name: Administering Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Free Certification Real IT 70-462 Exam pdf Collection

Refined of 70-462 download materials and faq for Microsoft certification for customers, Real Success Guaranteed with Updated 70-462 pdf dumps vce Materials. 100% PASS Administering Microsoft SQL Server 2012 Databases exam Today!

2016 Apr 70-462 Study Guide Questions:

Q31. You administer a Microsoft SQL Server 2012 database. 

Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error. 

You need to identify the root cause of the issue by retrieving the error message. 

What should you do? 

A. Create an Extended Events session by using the sqlserver.error_reported event. 

B. Create a SQL Profiler session to capture all ErrorLog and EventLog events. 

C. Flag all stored procedures for recompilation by using sp_recompile. 

D. Execute sp_who. 

Answer: A 


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

You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server. 

What should you create? 

A. An Alert 

B. A Resource Pool 

C. An Extended Event session 

D. A Server Audit Specification 

E. A SQL Profiler Trace 

F. A Database Audit Specification 

G. A Policy 

Answer: C 


Q33. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) 


Which Transact-SQL query should you use? 

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers-CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW 

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS 

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO 

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO 

F. SELECT Name, Country, Crderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

Answer: F


Q34. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects. 

You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. 

Which Transact-SQL query should you use? 

A. SELECT StudentCode as Code, 

RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value 

FROM StudentMarks 

GROUP BY StudentCode 

B. SELECT Id, Name, Marks, 

DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank 

FROM StudentMarks 

C. SELECT StudentCode as Code, 

DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value 

FROM StudentMarks 

GROUP BY StudentCode 

D. SELECT StudentCode as Code, 

NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value 

FROM StudentMarks 

GROUP BY StudentCode 

E. SELECT StudentCode AS Code,Marks AS Value FROM ( 

SELECT StudentCode, Marks AS Marks, 

RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank 

FROM StudentMarks) tmp 

WHERE Rank = 1 

F. SELECT StudentCode AS Code,Marks AS Value FROM ( 

SELECT StudentCode, Marks AS Marks, 

RANK() OVER (PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank 

FROM StudentMarks) tmp 

WHERE Rank = 1 

G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER (PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1 

Answer: D


Q35. You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. 

You attempt to add the [Corpnet\User1] login to the database. However, you receive the following error message: 

"User already exists in current database." 

You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original permissions. You need to achieve this goal by using the minimum required permissions. 

Which Transact-SQL statement should you use? 

A. DROP USER [User1]; 

CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; 

ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1]; 

B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1]; 

C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1]; 

D. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1]; 

Answer: C 


70-462 exam question

Up to date 70-462 exam answers:

Q36. You administer all the deployments of Microsoft SQL Server 2012 in your company. 

You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off-loaded from the primary database to another server. You also need to be able to add indexes to the secondary database. 

Which configuration should you use? 

A. . Two servers configured in different data centers 

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

. One server configured as an Active Secondary 

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

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 Asynchronous-Commit Availability Mode 

E. . Two servers configured on the same subnet 

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

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

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

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

. SQL Server configured as a clustered instance 

Answer: F 


Q37. You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica. 

You have multiple queries that read data and produce reports from the database. 

You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica. 

What should you do? 

A. Set the Availability Mode property of HA/Server02 to Asynchronous commit. 

B. Set the Readable Secondary property of HA/Server02 to Read-intent only. 

C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections. 

D. Set the Availability Mode property of HA/Server01 to Asynchronous commit. 

Answer: B 


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


Q39. You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements: 

Takes an input variable Returns a table of values Cannot be referenced within a view 

Which object should you use? 

A. Scalar-valued function 

B. Inline function 

C. User-defined data type 

D. Stored procedure 

Answer: D


Q40. You administer a Microsoft SQL Server 2012 database. 

You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated permissions. 

You need to ensure that the job step can run using a different user account. 

What should you use? 

A. A notification 

B. A schedule 

C. A job category 

D. A proxy 

Answer: D 


70-462 practice question

Simulation 70-462 courses:

Q41. You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions: 


Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. 

Which Transact-SQL statement should you use? 

A. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END 

B. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted INSERT INTO Employee(PersonId, EmployeeNumber) SELECT Id, EmployeeNumber FROM inserted END 

C. CREATE TRIGGER TrgVwEmployee ON VwEmployee 

INSTEAD OF INSERT 

AS 

BEGIN 

DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), 

@PersonID INT, 

@EmployeeNumber NVARCHAR(15) 

SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName, 

@EmployeeNumber = 

EmployeeNumber 

FROM inserted 

INSERT INTO Person(Id, FirstName, LastName) 

VALUES(@ID, @FirstName, @LastName) 

INSERT INTO Employee(PersonID, EmployeeNumber) 

VALUES(@PersonID, @EmployeeNumber 

End 

D. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee INSERT INTO Employee(PersonID, EmployeeNumber) SELECT Id, EmployeeNumber FROM VwEmployee End 

Answer: B


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


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


Q44. 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. SERIALIZABLE 

B. SNAPSHOT 

C. READ COMMITTED SNAPSHOT 

D. REPEATABLE READ 

Answer: C 


Q45. You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders. 

Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD. 

You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation. 

Which Transact-SQL command should you use? 

A. ALTER INDEX IX_OrderDateON CustomerOrderDISABLE 

B. ALTER INDEX IX_OrderDateON CustomerOrderENABLE 

C. ALTER INDEX IX_OrderDateON CustomerOrderREORGANIZE 

D. ALTER INDEX IX OrderDateON CustomerOrderREBUILD 

Answer: C 



see more 70-462 - Administering Microsoft SQL Server 2012 Databases