New Updated Microsoft MCSE 70-464 Real Exam Questions and Answers Download 101-110

Ensurepass

QUESTION 101

DRAG DROP

You plan to create a custom aggregation function named Function1.

 

You plan to deploy Function1 to SQL Server 2012.

 

You need to ensure that Function1 can access a web service. The solution must minimize the number of changes made to the database.

 

You create a Microsoft .NET Framework class that contains the definition of Function1.

 

You upload a certificate to SQL Server.

 

What three tasks should you perform next?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

 

clip_image002

 

Correct Answer:

clip_image004

 

 

QUESTION 102

You have a database that uses the following management views:

 

clip_image006Sys.dm_os_volume_stats

clip_image006[1]Sys.dm_db_partition_stats

clip_image006[2]Sys.dm_db_file_space_usage

clip_image006[3]Sys.fulltext_indexes

 

You plan to migrate the database to Microsoft SQL Azure.

 

You need to identify which view can be used in SQL Azure.

 

Which view should you identify?

 

A.

sys.fulltext_indexes

B.

sys.dm_db_file_space_usage

C.

sys.dm_os_volume_stats

D.

sys.dm_db_partition_stats

 

Correct Answer: D

 

 

QUESTION 103

You have a SQL Server 2012 database named Database1.

 

Database1 has a table named Customers. Customers contains more than 1 million rows.

 

The database has a stored procedure that was created by using the following script:

 

clip_image007

 

You need to ensure that up_customers returns rows when the following statement is executed:

 

EXECUTE up_customers’1,2,3,4,5′;

 

What should you do?

 

A.

Update @CustcmerTypelist to use the int data type.

B.

Convert @CustomerTypeList to a table variable.

C.

Convert @CustomerTypeList to an XML variable.

D.

Update @CustomerTypeList to use the XML data type.

 

Correct Answer: B

 

 

QUESTION 104

DRAG DROP

You have a SQL Server 2012 database named database1. Users report that queries that usually take less than one second to execute, take more than 30 seconds to execute. You need to view the server resource consumption when the queries are executed. What should you do?

 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

 

clip_image009

 

Correct Answer:

clip_image011

 

 

QUESTION 105

DRAG DROP

You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes. A full backup of Database1 is performed every day. You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future. Which code segment should you execute?

 

To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

 

clip_image013

 

Correct Answer:

clip_image015

 

 

QUESTION 106

You need to identify which long running transactions use an index. Which dynamic management view should you use?

 

A.

sys.dm_exec_query_optimizer_info

B.

sys.dm_exec_connections

C.

sys.dm_exec_query_stats

D.

sys.dm_exec_sessions

 

Correct Answer: A

 

 

QUESTION 107

You create a table named Customers by using the following code segment:

 

clip_image016

 

You create a non-clustered index named IX_Name on the name column.

 

You write the following query to retrieve all of the customers that have a name that starts with the letters SMI:

 

clip_image017

 

You discover that the query performs a table scan.

 

You need to ensure that the query uses the index.

 

What should you do?

 

A.

Replace LEFT(name,3) = ‘smi’ by using name like ‘smi%’

B.

Replace LEFT(name,3) = ‘smi’ by using substring(name,l,3) = ‘smi’

C.

Recreate IX_Name as a unique index

D.

Recreate IX Name as a clustered index

 

Correct Answer: A

 

 

QUESTION 108

You plan to execute the following code:

 

clip_image018

 

You need to identify how many rows will be in dbo.Table1 after you execute the code.

 

How many rows should you identify?

 

A.

0

B.

1

C.

2

D.

3

 

Correct Answer: A

 

 

QUESTION 109

You plan to design an application that temporarily stores data in a SQL Azure database. You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session. Which type of objects should you identify?

 

A.

Common table expressions (CTEs)

B.

Temporary stored procedures

C.

Temporary tables

D.

Table variables

 

Correct Answer: C

Explanation:

http://msdn.microsoft.com/en-us/library/ms175972.aspx http://msdn.microsoft.com/en-us/library/ms189084.aspx http://msdn.microsoft.com/en-us/library/ms175010.aspx http://msdn.microsoft.com/en-us/library/bb510489.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx http://zacksfiasco.com/post/2010/01/21/SQL-Server-Temporary-Stored-Procedures.aspx

 

 

QUESTION 110

DRAG DROP

You have a table named Customers that has a clustered index defined on the ID column. You write a script to create a stored procedure. You need to complete the script for the stored procedure. The solution must minimize the number of locks and deadlocks. What should you do?

 

To answer, drag the appropriate option to the correct location in the answer area. (Answer choices may be used once, more than once, or not at all.)

clip_image020

 

Correct Answer:

clip_image022

 

Instant Access to Download Latest Complete Collection of Microsoft MCSE 70-464 Real Exam

Try Microsoft MCSE 70-464 Free Demo