Get Full Version of the Exam
http://www.EnsurePass.com/70-464.html

Question No.97

You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.

Proc1 updates data in Table1. Proc2 reads data from Table1.

You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1.

You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating.

What should you change Proc1 to do?

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

1. Update less than 4,000 rows simultaneously.

2. Use the PAGLOCK table hint.

3. Wait for Proc2 to complete.

4. Use the ROWLOCK table hint.

Correct Answer: A

Question No.98

You need to implement a solution that meets the data recovery requirements. You update each stored procedure to accept a parameter named @transactionID. What should you add next to the beginning of each stored procedure?

1. SAVE TRANSACTION WITH MARK @transactionID

2. ROLLBACK DISTRIBUTED TRANSACTION @transactionID

3. BEGIN TRANSACTION WITH MARK @transactionID

4. COMMIT TRANSACTION @transactionID

Correct Answer: C

Question No.99

You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU). You need to recommend a solution to ensure that each SKU starts with the letters quot;ADVquot; and is followed by 10 digits. The solution must minimize the amount of development effort required. What should you include in the recommendation?

1. A FOREIGN KEY constraint

2. A trigger

3. A user-defined data type

4. A CHECK constraint

Correct Answer: C

Question No.100

You have a database that contains a table named Department. Department contains the names and locations of each department. You have a table-valued function named ProjectList() that

returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function. You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects. What should you add to the query?

1. OUTER APPLY

2. OUTER JOIN

3. CROSS JOIN

4. CROSS APPLY

Correct Answer: D

Question No.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.

Correct Answer:

Question No.102

You have a database that uses the following management views:

Sys.dm_os_volume_stats Sys.dm_db_partition_stats Sys.dm_db_file_space_usage 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?

1. sys.fulltext_indexes

2. sys.dm_db_file_space_usage

3. sys.dm_os_volume_stats

4. sys.dm_db_partition_stats

Correct Answer: D

Question No.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:

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

EXECUTE up_customers#39;1,2,3,4,5#39;; What should you do?

1. Update @CustcmerTypelist to use the int data type.

2. Convert @CustomerTypeList to a table variable.

3. Convert @CustomerTypeList to an XML variable.

4. Update @CustomerTypeList to use the XML data type.

Correct Answer: B

Question No.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.

Correct Answer:

Question No.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.

Correct Answer:

Question No.106

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

1. sys.dm_exec_query_optimizer_info

2. sys.dm_exec_connections

3. sys.dm_exec_query_stats

4. sys.dm_exec_sessions

   Correct Answer: A

   Get Full Version of the Exam
   70-464 Dumps
   70-464 VCE and PDF