[Regenerate] sql 70-464

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

2016 Jun 70-464 Study Guide Questions:

Q76. Topic 8) 

You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type. 

You have a certificate named Cert1. 

You need to replace Column1 with a new encrypted column that uses two-way encryption. 

Which code segment should you execute before you remove Column1? 

To answer, move the appropriate code segments from the list of code segments to the 

answer area and arrange them in the correct order. 

Answer: 


Q77. You plan to migrate an instance of SQL Server 2008 to a new installation of SQL Server 2012. 

You need to migrate alerts and e-mail notifications. 

Which system stored procedures should you use? (Each correct answer presents part of the solution. Choose all that apply.) 

A. sp_syspolicy_create_job 

B. sp_add_operator 

C. sp_audit_write 

D. sp_add_alert 

Answer: B,C 

Explanation: B: sp_add_operator 

Creates an operator (notification recipient) for use with alerts and jobs. 

C: sp_audit_write Adds a user-defined audit event to the USER_DEFINED_AUDIT_GROUP. If USER_DEFINED_AUDIT_GROUP is not enabled, sp_audit_write is ignored. 


Q78. You have an application that uses a view to access data from multiple tables. 

You need to ensure that you can insert rows into the underlying tables by using the view. 

What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Define the view by using the SCHEMABINDING option. 

C. Define the view by using the CHECK option. 

D. Materialize the view. 

Answer: C 

Explanation: 

http://msdn.microsoft.com/en-us/library/ms180800.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx 


Q79. You need to add a new column named Confirmed to the Attendees table. 

The solution must meet the following requirements: 

. Have a default value of false. 

. Minimize the amount of disk space used. 

Which code block should you use? 

A. ALTER TABLE Attendees 

ADD Confirmed bit DEFAULT 0; 

B. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT '1' 

C. ALTER TABLE Attendees 

ADD Confirmed bit DEFAULT 1; 

D. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT ‘1’; 

Answer: A 

Explanation: 

http://msdn.microsoft.com/en-us/library/ms177603.aspx 


Q80. You need to implement a change to usp_ExportOpenings that meets the integration requirements. 

What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?) 

A. To the end of line 04, add [Opening]. 

B. To the end of line 05, add [Opening! title]. 

C. To line 10, add FOR XML RAW. 

D. To line 10, add FOR XMLEXPLICIT. 

E. To line 10, add FOR XML AUTO. 

F. To the end of line 04, add [Opening!ELEMENT]. 

G. To the end of line 06, add [Opening!salary!ELEMENT]. 

H. To the end of line 05, add [Opening!title!ELEMENT]. 

I. To the end of line 06, add [Opening! salary]. 

Answer: A,B,E,I 

Explanation: 

The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics. 


70-464  exam prep

Improved vtc - microsoft sql server 2012 development (exam 70-464) course:

Q81. Topic 8) 

You create a disk-based table that contains the following script: 

You need to prevent duplicate values in the SKU field. 

Which five code segments should you use? 

To answer, move the appropriate code segments from the list of code segments to the 

answer area and arrange them in the correct order. 

Answer: 


Q82. You need to encapsulate a T-SQL script into a reusable user-defined object. 

The object must meet the following requirements: 

.

Permit insertions into a table variable. 

.

Support structured exception handling. 

.

Prevent changes to the definition of referenced objects. 

.

Support the use of the APPLY operator on the output of the object. 

Which type of object should you use? 

A. An inline table-valued function 

B. A stored procedure 

C. A scalar user-defined function 

D. A multi-statement table-valued function 

Answer: C 


Q83. You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID. 

Which line of code should you modify in CountryFromID.sql? 

A. 04 

B. 05 

C. 06 

D. 19 

Answer: D 

Explanation: 

http://msdn.microsoft.com/en-us/library/ms186755.aspx http://msdn.microsoft.com/en-us/library/ms191320.aspx 


Q84. You plan to create a new column in a table. The column must meet the following requirements: 

. Be able to store images that are larger than 1 MB each. 

. Be able to access the images from Microsoft .NET Framework applications. 

You need to recommend which data type must be used in the column. 

Which data type should you recommend? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. nvarchar 

B. varbinary 

C. image 

D. FileStream 

Answer: D 


Q85. Topic 8) 

You create a table that contains the following script: 

You need to prevent duplicate values in the EmployeeID field. 

Which five code segments should you use? 

To answer, move the appropriate code segments from the list of code segments to the 

answer area and arrange them in the correct order. 

Answer: 


2passeasy.com

Actual passed 70-464:

Q86. You need to modify Production.ProductDetails_Insert to comply with the application requirements. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 

Explanation: 

http://msdn.microsoft.com/en-us/library/bb669102.aspx 


Q87. You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously. 

You need to minimize the execution time of usp.SelectSpeakersByName. The solution must not affect the performance of the other stored procedures. 

What should you update? 

A. Usp_UpdateSpeakerName to use the NOLOCK query hint 

B. Usp_UpdateSpeakerName to use snapshot isolation 

C. Usp_SelectSpeakersByName to use the NOLOCK query hint 

D. Usp_SelectSpeakersByName to use snapshot isolation 

Answer: C 

Explanation: NOLOCK 

Is equivalent to READUNCOMMITTED. 

READUNCOMMITTED 

Specifies that dirty reads are allowed. 


Q88. You need to modify the stored procedure usp_LookupConcurrentUsers. What should you do? 

A. Use the summary table as an in-memory optimized table with a non-hash clustered index. 

B. Use the summary table as an in-memory optimized table with a non-hash nonclustered index. 

C. Use a type variable instead of the summary table. 

D. Add a clustered index to the summary table. 

Answer: A 


Q89. ... 

You have a SQL Server 2012 instance named SQL\Instance1. Instance1 contains a database named Database1. 

You need to recommend an index defragmentation solution for an index named ContentIndex. ContentIndex must meet the following requirements: 

Remain online during the defragmentation. 

Update distribution statistics. 

Perform defragmentation as quickly as possible. 

Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer. 

A. DBCC DBREINDEX 

B. REORGANIZE 

C. REBUILD 

D. DBCC INDEXDEFRAG 

Answer: B 


Q90. Topic 7) 

You need to implement a new version of usp_AddMobileLocation. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks. 

Answer: 



see more http://www.2passeasy.com/exam/70-464/