[Apr 2016] sql server exam 70-462 questions

Exam Code: 70-462 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Administering Microsoft SQL Server 2012 Databases
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-462 Exam.

2016 Apr 70-462 Study Guide Questions:

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


Q2. You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster. 

An additional node named Node C has been added to the existing cluster. 

You need to ensure that the SQL Server instance can use all nodes of the cluster. 

What should you do? 

A. Run the New SQL Server stand-alone installation Wizard on Node C. 

B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C. 

C. Use Node B to install SQL Server on Node C. 

D. Use Node A to install SQL Server on Node C. 

Answer: B 


Q3. You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions: 


Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. 

Which Transact-SQL query do you use? 

A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(OrderAmount) DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1 

B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, 

RANK() OVER (PARTITION BY CustomerID ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk FROM Customer c INNER JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1 

C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderAmount DESC) AS Rnk FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID WHERE o.Rnk = 1 

D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN (SELECT CustomerID, ShippingCountry, COUNT(OrderAmount) DESC) AS OrderAmount FROM Orders GROUP BY CustomerID, ShippingCountry) AS o ON c.CustomerID = o.CustomerID ORDER BY OrderAmount DESC 

Answer: A


Q4. You administer a SQL 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 remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions. 

Which Transact-SQL statement should you use? 

A. DENY SELECT ON Object::Regions FROM UserA 

B. DENY SELECT ON Schema::Customers FROM UserA 

C. EXEC sp_addrolemember 'Sales', 'UserA' 

D. REVOKE SELECT ON Object::Regions FROM UserA 

E. REVOKE SELECT ON Object::Regions FROM Sales 

F. EXEC sp_droproiemember 'Sales', 'UserA' 

G. REVOKE SELECT ON Schema::Customers FROM UserA 

H. DENY SELECT ON Object::Regions FROM Sales 

I. DENY SELECT ON Schema::Customers FROM Sales 

J. REVOKE SELECT ON Schema::Customers FROM Sales 

Answer: D 


Q5. You administer two Microsoft SQL Server 2012 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services. 

You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as 

a pull subscriber. 

The distribution agent on ProdSrv2 regularly fails, displaying the following error message: 

"Cannot access the file. Operating system error code 5 (Access is denied.)." 

You need to configure the distribution agent by granting only the minimum required access to all accounts. 

What should you do? 

A. Configure the Subscriber to use the Local System account. 

B. Configure the SQL Server Agent service to run under the Local System account. 

Configure the Subscriber to use the SQL Server Agent service account. 

C. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1. 

D. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1. 

Answer: D 


70-462 testing engine

Abreast of the times 70-462 cd content:

Q6. You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. 

You write the following Transact-SQL query: 

INSERT INTO OrdersHistorical 

SELECT * FROM CompletedOrders 

You need to optimize transaction logging and locking for the statement. Which table hint should you use? 

A. HOLDLOCK 

B. ROWLOCK 

C. XLOCK 

D. UPDLOCK 

E. TABLOCK 

Answer: E 


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


Q8. You administer a Microsoft SQL Server 2012. 

A process that normally runs in less than 10 seconds has been running for more than an hour. 

You examine the application log and discover that the process is using session ID 60. 

You need to find out whether the process is being blocked. 

Which Transact-SQL statement should you use? 

A. EXEC sp_who 60 

B. SELECT * FROM sys.dm_exec_sessions WHERE sessionjd = 60 

C. EXEC sp_helpdb 60 

D. DBCC INPUTBUFFER (60) 

Answer: A 


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


Q10. You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table. 

What should you do? 

A. Reference a user-defined function within the computed column. 

B. Create a BEFORE trigger that maintains the state of the computed column. 

C. Add a default constraint to the computed column that implements hard-coded values. 

D. Add a default constraint to the computed column that implements hard-coded CASE statements. 

Answer: A


70-462 free download

Refined sql 70-462:

Q11. You develop a database for a travel application. You need to design tables and other database objects. You create a view that displays the dates and times of the airline schedules on a report. 

You need to display dates and times in several international formats. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

Answer: C


Q12. You administer a Microsoft SQL Server 2012 instance. 

You need to configure a new database to support FILETABLES. 

What should you do? Choose all that apply. 

A. Disable FILESTREAM on the Database. 

B. Enable FILESTREAM on the Server Instance. 

C. Configure the Database for Partial Containment. 

D. Create a non-empty FILESTREAM file group. 

E. Enable Contained Databases on the Server Instance. 

F. Set the FILESTREAM directory name on the Database. 

Answer: BDF 


Q13. You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. 

Some users must be prevented from deleting records in any of the tables in the Sales schema. 

You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. 

What should you do? 

A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role. 

B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role. 

C. Deny Delete permissions on each table in the Sales schema for each user. 

D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role. 

Answer: A 


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


Q15. You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction. 

You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index. 

You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process. 

Which Transact-SQL batch should you use? 

A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REORGANIZE 

B. ALTER INDEX ALL ON AccountTransaction REBUILD 

C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REBUILD 

D. CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction.AccountCode WITH DROP EXISTING 

Answer: A 



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

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

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