microsoft 70 462 (1 to 10)

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

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

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

Q2. You administer two Microsoft SQL Server 2012 servers. Each server resides in a different, untrusted domain. 

You plan to configure database mirroring. 

You need to be able to create database mirroring endpoints on both servers. 

What should you do? 

A. Configure the SQL Server service account to use Network Service. 

B. Use a server certificate. 

C. Use a database certificate. 

D. Configure the SQL Server service account to use Local System. 

Answer: B 

Q3. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column. 

You need to increase the values in the Price column of all products in the Products table by 5 percent. 

You also need to log the changes to the ProductsPriceLog table. 

Which Transact-SQL query should you use? 

A. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

B. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, inserted.Price, deleted.Price 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

C. UPDATE Products SET Price = Price * 1.05 

OUTPUT inserted.ProductCode, deleted.Price, inserted.Price * 

INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice) 

D. UPDATE Products SET Price = Price * 1.05 

INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice; 

SELECT ProductCode, Price, Price * 1.05 FROM Products 

Answer: A

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

Q5. You are the administrator of a Microsoft SQL Server 2012 server. 

Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications 

You need to dynamically limit resource consumption. 

What should you do? 

A. Set up Service Broker to ensure that applications are not allowed to consume more than the specified amount of resources. 

B. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor. 

C. Configure Extended Events to monitor and restrict resource limits allowed by each application type. 

D. Create a new Plan Guide with a Scope Type of sql and define the resource limits for each application. 

Answer: A 

Q6. You administer a Microsoft SQL Server 2012 database named Orders. Orders contains a table named OrderShip that is defined as follows: 

Performance on this procedure is slow. 

You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources. 

Which Transact-SQL statement should you use? 

A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL 

B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULL 

C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode) 

D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL 

Answer: B 

Q7. You are a database administrator for a Microsoft SQL Server 2012 instance. 

You need to ensure that data can be replicated from a production server to two reporting servers in real time. You also need to ensure that data on the reporting server is always accessible. 

Which solution should you use? 

A. Availability Groups 

B. Extended Events 

C. Snapshot Replication 

D. Policy Based Management 

Answer: A 

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

Q9. You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012. 

You create an Availability Group defined by the following schema. (Line numbers are included for reference only.) 

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions: 

Production transactions should be minimally affected. 

The secondary server should allow reporting queries to be performed. 

If the primary server goes offline, the secondary server should not automatically take over. 

Which Transact-SQL statement should you insert at line 06? 

A. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 

FAILOVER_MODE = MANUAL 

SECONDARY_ROLE ( 

ALLOW_CONNECTIONS = READ_ONLY, 

READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') 

PRIMARY_ROLE ( 

ALLOW_CONNECTIONS = READ_WRITE, 

READ_ONLY_ROUTING_LIST = NONE) 

B. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 

FAILOVER_MODE = MANUAL 

SECONDARY_ROLE ( 

ALLOW_CONNECTIONS = READ_ONLY, 

READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') 

C. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 

FAILOVER_MODE = MANUAL 

SECONDARY_ROLE ( 

ALLOW_CONNECTIONS = READ_ONLY, 

READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') 

D. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 

FAILOVER_MODE = MANUAL 

SECONDARY_ROLE ( 

ALLOW_CONNECTIONS = YES, 

READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') 

Answer: D 

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

You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server. 

What should you create? 

A. an Extended Event session 

B. a Policy 

C. a Database Audit Specification 

D. an Alert 

E. a Server Audit Specification 

F. a SQL Profiler Trace 

G. a Resource Pool 

Answer: F