Apr 2016 updated: Exambible Microsoft 70-464 answers 16-30

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-464 Exam Dumps (PDF & VCE):
Available on: https://www.certleader.com/70-464-dumps.html


70-464 Product Description:
Exam Number/Code: 70-464 vce
Exam name: Developing Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-464 Developing Microsoft SQL Server 2012 Databases

70-464 examcollection

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 Apr 70-464 Study Guide Questions:

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


Q17. You review a query that runs slowly. The query accesses data in a table named Schema1.Table1. 

The following is the relevant portion of the execution plan for the query: You need to create the missing index. 

Which code segment should you execute? 

A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3 

B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) 

C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4) 

D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4) 

Answer: C 


Q18. Topic 8) 

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. 

Answer: 


Q19. Topic 8) 

You have a SQL Server 2014 database. 

You plan to create a stored procedure that will retrieve the following information: 

. The XML content of the query plans that is stored in memory . The number of times each query plan is used 

You need to identify which dynamic management objects must be used to retrieve the required information for the stored procedure. 

Which dynamic management objects should you identify? 

To answer, drag the appropriate dynamic management object to the correct requirement in the answer area. 

Answer: 


Q20. You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table. 

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2: 

"Msg 916, Level 14, State 1, Line 1 The server principal "Account1" is not able to access the database "DB1" under the current security context." 

You verify that there is a server login for Account1 on Server2. 

You need to ensure that Account1 can connect to DB1. 

What should you do? 

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

A. Update the SID for Account1 on DB1. 

B. Add Account1 to the db_datareader role. 

C. Create a new database user on DB1. 

D. Implement Windows authentication. 

Answer: B 


70-464 free question

Abreast of the times 70-464 free practice exam:

Q21. You use SQL Server 2012 to maintain the data used by the applications at your company. 

You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.) 

... 

You need to ensure that Table1 contains a column named UserName. The UserName column will: 

Store string values in any language. 

Accept a maximum of 200 characters. 

Be case-insensitive and accent-insensitive. 

Which code segment should you add at line 03? 

A. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL, 

B. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL, 

C. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL, 

D. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL, 

Answer: D 

161. You plan to modify a procedure that contains hundreds of lines of code. 

The modification must support the following guidelines: 

. Use only tables that are not persistent in the database. 

. Minimize the amount of time required to execute and recompile procedures. 

You need to identify which type of table must be used to support the planned modification. 

Which type of table should you identify? 

A. A system table 

B. A partitioned table 

C. A table variable 

D. A temporary table 

Answer: C 


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

Answer: A 


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


Q24. You execute the following code: 

You create a nonclustered index named IX_CustomerName on the CustomerName column. 

You execute the following query: 

You need to reduce the amount of time it takes to execute the query. 

What should you do? 

A. Partition the table and use the CustomerName column for the partition scheme. 

B. Replace IX_CustomerName with a clustered index. 

C. Replace LEFT(CustomerName ,1) = 'a' with CustomerName LIKE 'a%'. 

D. Replace LEFT(CustomerName ,1) = 'a' with SUBSTRING(CustomerName ,1,1) - 'a'. 

Answer: C 

Explanation: 

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


Q25. You need to ensure that usp_AddXMLOrder can be used to validate the XML input from the retailers. 

Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.) 

A. @schema varbinary(100). 

B. @items varchar(max). 

C. @schema sysname. 

D. @items varbinary(max). 

E. @items xml. 

F. @schema xml. 

Answer: C,E 


70-464 bootcamp

High quality 70-464 vce:

Q26. You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users. 

You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements: 

. Provide a representation of the deadlock in XML format. 

. Minimize the impact on the server. 

What should you create? 

A. A SQL Server Profiler trace 

B. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views 

C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views 

D. A script that enables trace flags 

Answer: A 


Q27. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table. 

What should you use? 

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

A. A data manipulation language (DML) trigger 

B. A DEFAULT constraint 

C. A Data Definition Language (DDL) trigger 

D. A CHECK constraint 

E. A FOREIGN KEY constraint 

Answer: D 

Explanation: 

http://www.techrepublic.com/blog/programming-and-development/comparing-sql-serverconstraints-and-dmltriggers/402 http://msdn.microsoft.com/en-us/library/ms178110.aspx 


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


Q29. Topic 8) 

You execute the following code: 

You discover that the Customers table was created in the dbo schema. 

You need to create a code segment to move the table to another schema named 

Schema2. 

What should you create? 

To answer, drag the appropriate code segments to the correct location in the answer area. 

 (Answer choices may be used once, more than once, or not at all.) 

Answer: 


Q30. 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? 

A. OUTER APPLY 

B. OUTER JOIN 

C. CROSS JOIN 

D. CROSS APPLY 

Answer: D 



see more 70-464 dumps