Apr 2016 updated: Pass4sure Microsoft 70-462 training 91-105

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:

Q91. You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores. 

You discover performance issues when large amounts of data are written to tables under heavy system load. 

You need to limit the number of cores that handle I/O. 

What should you configure? 

A. Processor affinity 

B. Lightweight pooling 

C. Max worker threads 

D. I/O affinity 

Answer: D 


Q92. You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. 

You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: 

. Does not return an error 

. Closes all opened transactions 

Which Transact-SQL statement should you use? 

A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH 

B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH 

C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH 

D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH 

Answer: D 


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


Q94. You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. 

You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. 

You need to reduce fragmentation. You need to achieve this goal without taking the index offline. 

Which Transact-SQL batch should you use? 

A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING 

B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE 

C. ALTER INDEX ALL ON OrderDetail REBUILD 

D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD 

Answer: B 


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


70-462  download

Most up-to-date 70-462 test engine:

Q96. You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several production databases have been deployed. 

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

You need to ensure that the login for the ticketing application cannot access other production databases. 

What should you do? 

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

B. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role. 

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

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

Answer: C 


Q97. You plan to install a Microsoft SQL Server 2012 instance. 

The instance will support a database that has the following requirements: 

Store Excel workbooks on the file system. 

Access the workbooks through Transact-SQL. 

Include the workbooks in database backups. 

During installation, you need to ensure that the requirements will be met. 

Which feature should you use? 

A. Excel Services 

B. FILESTREAM 

C. SQL Server Integration Services (SSIS) 

D. OpenXML 

Answer: B 


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


Q99. You administer a Microsoft SQL Server 2012 instance. 

You need to stop a blocking process that has an SPID of 64 without stopping other processes 

What should you do? 

A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64 

B. Restart the SQL Server service. 

C. Execute the following Transact-SQL statement: KILL 64 

D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64' 

Answer: C 


Q100. You administer a Microsoft SQL Server 2012 database. 

The database contains a customer table created by using the following definition: 


You need to ensure that the minimum amount of disk space is used to store the data in the customer table. 

What should you do? 

A. Implement row-level compression. 

B. Implement page-level compression. 

C. Convert all indexes to Column Store indexes. 

D. Implement Unicode compression. 

Answer: B 


certleader.com

Real 70-462 free demo:

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


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


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


Q104. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. 

You need to ensure that the following requirements are met: 

. Future modifications to the table definition will not affect the applications' ability to access data. 

. The new object can accommodate data retrieval and data modification. 

You need to achieve this goal by using the minimum amount of changes to the applications. 

What should you create for each application? 

A. Synonyms 

B. Common table expressions 

C. Views 

D. Temporary tables 

Answer: C 


Q105. You administer a Microsoft SQL Server 2012 database. You want to make a full backup of the database to a file on disk. 

In doing so, you need to output the progress of the backup. 

Which backup option should you use? 

A. STATS 

B. COMPRESSION 

C. CHECKSUM 

D. IN IT 

Answer: A 



see more 70-462 dumps