15 tips on How to 70-464 Test Like a Badass [46 to 60]

70-464 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

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

Free Certification Real IT 70-464 Exam pdf Collection

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:

Q46. Topic 8) 

You administer a SQL Server 2014 instance. 

The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors. 

The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin]. 

A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin]. 

You need to set up the Resource Group and Workgroup Pools on the instance. 

You have the following requirements: 

. Both processes must never use more than 50 percent of the CPU at any one time. . The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10. . The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes. 

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: 

149. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a table that is 

used for auditing is updated. 

What should you use? 

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

A. A Data Definition Language (DDL) trigger 

B. A DEFAULT constraint 

C. A CHECK constraint 

D. A FOREIGN KEY constraint 

E. A data manipulation language (DML) trigger 

Answer: E 

Explanation: 

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


Q47. You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required. 

What should you add to usp_SelectSpeakersByName? 

A. A table variable 

B. An OFFSET-FETCH clause 

C. The ROWNUMBER keyword 

D. A recursive common table expression 

Answer: B 

Explanation: 

http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-serverpaging-methods/ http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/ 


Q48. You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated. 

What should you add to usp_GetOrdersAndItems? 

A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03. 

B. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03. 

C. Add (UPDLOCK) to the end of line 06. 

D. Add (READPAST) to the end of line 06. 

Answer: D 

55. You need to implement a solution that addresses the bulk insert requirements. 

What should you add to line 08 in usp_ImportOrderDetails? 

A. LASTROW=0. 

B. BATCHSIZE=0. 

C. BATCHSIZE=1000. 

D. LASTROW = 1000. 

Answer: C 


Q49. You have a SQL Server 2012 database named Database1. You execute the following code: 

You insert 3 million rows into Sales. 

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

What should you do? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 


Q50. You use SQL Server 2012 to store data used by an e-commerce application. 

You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site. 

You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data. 

Which transaction isolation level should you use in sp1? 

A. Serializable 

B. Snapshot 

C. Repeatable read 

D. Read committed 

Answer: B 


70-464

Leading 70-464 test preparation:

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


Q52. Topic 8) 

You have two existing tables, one named COUNTRY and the other named STATES. The tables are defined as follows: 

You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table. 

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: 


Q53. Which data type should you use for ProductType? 

A. varchar(11) 

B. nvarchar(11) 

C. char(11) 

D. bigint 

Answer: C 

Topic 5, Litware, Inc 

Overview 

General Overview 

You are a database developer for a company named Litware, Inc. Litware has a main office in Miami. 

Litware has a job posting web application named WebApp1. WebApp1 uses a database named DB1. DB1 is hosted on a server named Server1. The database design of DB1 is shown in the exhibit. (Click the Exhibit button.) 

WebApp1 allows a user to log on as a job poster or a job seeker. Candidates can search for job openings based on keywords, apply to an opening, view their application, and load their resume in Microsoft Word format. Companies can add a job opening, view the list of candidates who applied to an opening, and mark an application as denied. 

Users and Roles 

DB1 has five database users named Company, CompanyWeb, Candidate, CandidateWeb, and Administrator. 

DB1 has three user-defined database roles. The roles are configured as shown in the following table. 

Keyword Search 

The keyword searches for the job openings are performed by using the following stored procedure named usp_GetOpenings: 

Opening Update 

Updates to the Openings table are performed by using the following stored procedure named usp_UpdateOpening: 

Problems and Reported Issues 

Concurrency Problems 

You discover that deadlocks frequently occur. 

You identify that a stored procedure named usp_AcceptCandidate and a stored procedure named usp_UpdateCandidate generate deadlocks. The following is the code for usp_AcceptCandidate: 

Salary Query Issues 

Users report that when they perform a search for job openings without specifying a minimum salary, only job openings that specify a minimum salary are displayed. 

Log File Growth Issues 

The current log file for DB1 grows constantly. The log file fails to shrink even when the daily SQL Server Agent Shrink Database task runs. 

Performance Issues 

You discover that a stored procedure named usp_ExportOpenings takes a long time to run and executes a table scan when it runs. 

You also discover that the usp_GetOpenings stored procedure takes a long time to run and that the non-clustered index on the Description column is not being used. 

Page Split Issues 

On DB1, many page splits per second spike every few minutes. 

Requirements 

Security and Application Requirements 

Litware identifies the following security and application requirements: . Only the Administrator, Company, and CompanyWeb database users must be able to execute the usp_UpdateOpening stored procedure. . Changes made to the database must not affect WebApp1. 

Locking Requirements 

Litware identifies the following locking requirements: . The usp_GetOpenings stored procedure must not be blocked by the usp_UpdateOpening stored procedure. . If a row is locked in the Openings table, usp_GetOpenings must retrieve the latest 

version of the row, even if the row was not committed yet. 

Integration Requirements 

Litware exports its job openings to an external company as XML data. The XML data uses the following format: 

A stored procedure named usp_ExportOpenings will be used to generate the XML data. The following is the code for usp_ExportOpenings: 

The stored procedure will be executed by a SQL Server Integration Services (SSIS) package named Package1. 

The XML data will be written to a secured folder named Folder1. Only a dedicated Active Directory account named Account1 is assigned the permissions to read from or write to Folder1. 

Refactoring Requirements 

Litware identifies the following refactoring requirements: . New code must be written by reusing the following query: 

. The results from the query must be able to be joined to other queries. 

Upload Requirements 

Litware requires users to upload their job experience in a Word file by using WebApp1. WebApp1 will send the Word file to DB1 as a stream of bytes. DB1 will then convert the Word file to text before the contents of the Word file is saved to the Candidates table. 

A database developer creates an assembly named Conversions that contains the following: 

. A class named Convert in the SqlConversions namespace 

. A method named ConvertToText in the Convert class that converts Word files to 

text 

The ConvertToText method accepts a stream of bytes and returns text. The method is used in the following stored procedure: 

Job Application Requirements 

A candidate can only apply to each job opening once. 

Data Recovery Requirements 

All changes to the database are performed by using stored procedures. WebApp1 generates a unique transaction ID for every stored procedure call that the application makes to the database. 

If a server fails, you must be able to restore data to a specified transaction. 


Q54. Topic 7) 

You need to redesign the system to meet the scalability requirements of the application. 

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: 

76. You need to modify the stored procedure usp_LookupConcurrentUsers. 

What should you do? 

A. Add a clustered index to the summary table. 

B. Add a nonclustered index to the summary table. 

C. Add a clustered columnstore index to the summary table. 

D. Use a table variable instead of the summary table. 

Answer: A 

Explanation: Scenario: Query the current open micropayments for users who own multiple micropayments by using a stored procedure named usp.LookupConcurrentUsers 

Topic 8, Mix Questions 


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


70-464-exam

Validated 70-464 torrent:

Q56. You have an index for a table in a SQL Azure database. The database is used for Online 

Transaction Processing (OLTP). 

You discover that the index consumes more physical disk space than necessary. 

You need to minimize the amount of disk space that the index consumes. 

What should you set from the index options? 

A. STATISTICS_NORECOMPUTE = OFF 

B. FILLFACTOR = 80 

C. FILLFACTOR = 0 

D. STATISTICS_NORECOMPUTE = ON 

Answer: C 

Explanation: 

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


Q57. You have database objects that were created by using the following script: 

The dbo.Customers table has 1 million rows. 

You discover that usp_GetCustomersByDate takes a long time to complete. 

The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit 

button.) 

You need to ensure that usp_GetCustomersByDate completes as quickly as possible. What should you do? 

A. Modify the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint. 

B. Execute the sp_recompile 'dbo.GetCustomersByDate' statement. 

C. Execute the ALTER INDEXIX_Customers_CreationDate WITH REBUILD statement. 

D. Modify the stored procedure to include the OPTIMIZE FOR('1/1/2008') query hint. 

Answer: A 


Q58. You have a Microsoft SQL Azure database. 

You have the following stored procedure: 

You discover that the stored procedure periodically fails to update HR.Employees. 

You need to ensure that HR.Employees is always updated when up_employees executes. 

The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held. 

What should you do? 

A. Add the following line of code to line 05: 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

B. Add the following line of code to line 13: 

WITH (UPDLOCK) 

C. Add the following line of code to line 05: 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

D. Add the following line of code to line 08: 

WITH (UPDLOCK) 

Answer: D 


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


Q60. You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated. 

What should you add to usp.GetOrdersAndItems? 

A. Add WITH (NOLOCK) to the end of line 47. 

B. Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44. 

C. Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44. 

D. Add WITH (READPAST) to the end of line 47. 

Answer: B 



see more http://www.pdfcollection.net/70-464-pdf.html
http://www.exambible.com/70-464-exam/