Want to Pass 70-462 Exam In Next HOURS? Download Now →
January 12, 2018

Point Checklist: 70 462 pdf


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


Q11. You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. 

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use? 

A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY, CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL); 

B. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerName varchar(255) NOT NULL); 

C. CREATE TABLE Customer 

(SourceID int NOT NULL PRIMARY KEY CLUSTERED, 

CustomerID int NOT NULL UNIQUE, 

CustomerName varchar(255) NOT NULL); 

D. CREATE TABLE Customer 

(SourceID int NOT NULL, 

CustomerID int NOT NULL, 

CustomerName varchar(255) NOT NULL, 

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 

(SourceID, CustomerID)); 

Answer: D


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


Q13. You administer a Microsoft SQL Server 2012 database. 

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!' 

CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate' 

BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:\\TDE_Certificate.cer' 

WITH PRIVATE KEY (FILE = 'D:\\TDE_Certificate.key', ENCRYPTION BY PASSWORD = 

'MyPassword1!'); 

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; 

ALTER DATABASE Orders SET ENCRYPTION ON; 

You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location. 

A hardware failure occurs and so a new server must be installed and configured. 

After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database. 

You need to be able to restore the database. 

Which Transact-SQL statement should you use before attempting the restore? 

A. ALTER DATABASE Master SET ENCRYPTION OFF; 

B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\\TDE_Certificate.cer' 

WITH PRIVATE KEY (FILE = 'D:\\TDE_Certificate.key', DECRYPTION BY PASSWORD = 

'MyPassword1!'); 

C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate' 

USE Orders; 

CREATE DATABASE ENCRYPTION KEY 

WITH ALGORITHM = AES_256 

ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate; 

D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\\TDE_Certificate.cer' 

Answer:


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

E. a Database Audit Specification 

F. a SQL Profiler Trace 

G. a Server Audit Specification 

Answer:


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


Q16. You administer a Microsoft SQL Server 2012 database. 

You provide temporary securityadmin access to User1 to the database server. 

You need to know if User1 adds logins to securityadmin. 

Which server-level audit action group should you use? 

A. SERVER_STATE_CHANGE_GROUP 

B. SERVER_PRINCIPAL_IMPERSONATION_GROUP 

C. SUCCESSFUL_LOGIN_GROUP 

D. SERVER_ROLE_MEMBER_CHANGE_GROUP 

Answer:


Q17. You administer a single server that contains a Microsoft SQL Server 2012 default instance. 

You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions. 

You need to ensure that the application login is unable to access other production databases. 

What should you do? 

A. Use the SQL Server default instance and configure an affinity mask. 

B. Install a new named SQL Server instance on the server. 

C. Use the SQL Server default instance and enable Contained Databases. 

D. Install a new default SQL Server instance on the server. 

Answer:


Q18. You administer a Microsoft SQL Server 2012 server. One of the databases on the server supports a highly active OLTP application. 

Users report abnormally long wait times when they submit data into the application. 

You need to identify which queries are taking longer than 1 second to run over an extended period of time. 

What should you do? 

A. use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000. 

B. Use sp_configure to set a value for blocked process threshold. Create an extended event session. 

C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step. 

D. Run the sp_who command from a query window. 

E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log. 

Answer:


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


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