Certshared
2017 Microsoft Official New Released 70-462 ♥♥
100% Free Download! 100% Pass Guaranteed!
http://www.certshared.com/exam/70-462/


we provide Realistic Microsoft 70 462 dumps exams which are the best for clearing exam 70 462 test, and to get certified by Microsoft Administering Microsoft SQL Server 2012 Databases. The 70 462 training kit pdf Questions & Answers covers all the knowledge points of the real exam 70 462 exam. Crack your Microsoft exam 70 462 Exam with latest dumps, guaranteed!

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


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


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


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

You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail. You also need to minimize the amount of storage used by the 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. . SQL Server that includes an application database configured to perform transactional replication 

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

D. . Two servers configured in different data centers 

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

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

F. . Two servers configured on the same subnet 

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

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:


Q50. You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database solution. 

You are tasked with designing a scale-out and high-availability SQL Server 2012 Online 

Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances. 

Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed. 

What should you include in your solution? 

A. You should include two servers configured in an Active-Active SQL Server 2012 Cluster 

B. You should include a primary SQL Server 2012 database that uses transactional replication to replicate data to a secondary database. 

C. You should include two servers configured in an Active-Passive SQL Server 2012 Cluster. 

D. You should include two servers in an Asynchronous-Commit Availability Mode Availability Group. 

E. You should include two servers in a Synchronous-Commit Availability Mode Availability Group 

Answer:


Q51. You administer a Microsoft SQL Server 2012 database. The database has a table named Customers owned by UserA and another table named Orders owned by UserB. You also have a stored procedure named GetCustomerOrderInfo owned by UserB. GetCustomerOrderInfo selects data from both tables. 

You create a new user named UserC. 

You need to ensure that UserC can call the GetCustomerOrderInfo stored procedure. You also need to assign only the minimum required permissions to UserC. 

Which permission or permissions should you assign to UserC? Choose all that apply. 

A. The Select permission on Customers 

B. The Execute permission on GetCustomerOrderInfo 

C. The Take Ownership permission on Customers 

D. The Control permission on GetCustomerOrderInfo 

E. The Take Ownership permission on Orders 

F. The Select permission on Orders 

Answer: AB 


Q52. You administer a Microsoft SQL Server 2012 instance. 

The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week. 

You plan to define a backup strategy for the database. You need to ensure that the following requirements are met: 

. No more than 5 minutes worth of transactions are lost. 

. Data can be recovered by using the minimum amount of administrative effort. 

What should you do? Choose all that apply. A. Configure the database to use the SIMPLE recovery model. 

B. Create a DIFFERENTIAL database backup every 4 hours. 

C. Create a LOG backup every 5 minutes. 

D. Configure the database to use the FULL recovery model. 

E. Create a FULL database backup every 24 hours. 

F. Create a DIFFERENTIAL database backup every 24 hours. 

Answer: BCDE 


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


Q54. You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA. 

You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table. 

You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. 

Which Transact-SQL statement should you use? 

A. GRANT DELETE ON Purchases. Suppliers TC ContosoUser 

B. CREATE PROCEDURE Purchases.PurgelnactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser 

C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser 

D. CREATE PROCEDURE Purchases. PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser 

Answer:


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


Q56. You administer a Microsoft SQL Server 2012 server. The MSSQLSERVER service uses a domain account named CONTOSO\\SQLService. 

You plan to configure Instant File Initialization. 

You need to ensure that Data File Autogrow operations use Instant File Initialization. 

What should you do? Choose all that apply. 

A. Restart the SQL Server Agent Service. 

B. Disable snapshot isolation. 

C. Restart the SQL Server Service. 

D. Add the CONTOSO\\SQLService account to the Perform Volume Maintenance Tasks local security policy. 

E. Add the CONTOSO\\SQLService account to the Server Operators fixed server role. 

F. Enable snapshot isolation. 

Answer: CD 


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


Q58. 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 and the Sales role is granted the Select permission on the Customers schema. 

You need to ensure that the Sales role, including UserA, is disallowed to select from the Regions table. 

Which Transact-SQL statement should you use? 

A. REVOKE SELECT ON Schema::Customers FROM UserA 

B. REVOKE SELECT ON Object::Regions FROM UserA 

C. EXEC sp_addrolemember 'Sales', 'UserA' 

D. DENY SELECT ON Schema::Customers FROM Sales 

E. EXEC sp_droprolemember 'Sales', 'UserA' 

F. REVOKE SELECT ON Schema::Customers FROM Sales 

G. DENY SELECT ON Object::Regions FROM UserA 

H. REVOKE SELECT ON Object::Regions FROM Sales 

I. DENY SELECT ON Schema::Customers FROM UserA 

J. DENY SELECT ON Object::Regions FROM Sales 

Answer:


Q59. 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 = GETDATE() 

C. SELECT COUNT(*) 

FROM Purchases 

WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, 

GETDATE(), 112) 

D. SELECT COUNT(*) 

FROM Purchases 

WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) 

AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) 

Answer: D


Q60. You administer a Microsoft SQL Server 2012 database. 

You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements: 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: