Want to Pass 70-464 Exam In Next HOURS? Download Now →
April 2, 2018

How Does Exambible Microsoft 70-464 practice Work?

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

Q86. You need to provide referential integrity between the Sessions table and Speakers table. Which code segment should you add at line 47 of Tables.sql? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 



http://msdn.microsoft.com/en-us/library/ms189049.aspx http://msdn.microsoft.com/en-us/library/ms179610.aspx http://msdn.microsoft.com/en-us/library/ff878370.aspx 

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


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. 


Q88. Developers report that usp_UpdateSessionRoom periodically returns error 3960. 

You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows. 

What should you configure in Procedures.sql? 

A. Replace line 46 with the following code: 


B. Replace line 46 with the following code: 


C. Move the SELECT statement at line 49 to line 57. 

D. Move the SET statement at line 46 to line 53. 


Topic 3, Scenario 3 

Application Information 

You have two servers named SQL1 and SQL2. SQL1 has SQL Server 2012 Enterprise installed. SQL2 has SQL Server 2008 Standard installed. 

You have an application that is used to manage employees and office space. Users report that the application has many errors and is very slow. 

You are updating the application to resolve the issues. You plan to create a new database on SQL1 to support the application. The script that you plan to use to create the tables for the new database is shown in Tables.sql. The script that you plan to use to create the stored procedures for the new database is shown in StoredProcedures.sql. The script that you plan to use to create the indexes for the new database is shown in Indexes.sql. A database named DB2 resides on SQL2. DB2 has a table named EmployeeAudit that will audit changes to a table named Employees. 

A stored procedure named usp_UpdateEmployeeName will be executed only by other stored procedures. The stored procedures executing usp_UpdateEmp!oyeeName will always handle transactions. 

A stored procedure named usp_SelectEmployeesByName will be used to retrieve the names of employees. Usp_SelectEmployeesByName can read uncommitted data. 

A stored procedure named usp_GetFutureOfficeAssignments will be used to retrieve office assignments that will occur in the future. 


Indexes.sql Tables.sql 

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






Q90. You plan to create a stored procedure that inserts data from an XML file to the OrderDetails table. The following is the signature of the stored procedure: 

The following is the XSD file used to create the ValidateOrder schema collection: 

You develop a code segment that retrieves the number of items and loops through each item. Each time the loop runs, a variable named @itemNumber is incremented. 

You need to develop a code segment that retrieves the product ID of each item number in the loop. 

Which code segment should you develop? 

A. SET @productID = @items.value'/Root/Product/productID', int) 

B. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/@productID', int) 

C. SET @productID = @items.value'/Root/Product['+ @itemNumber+ ']/productID', int) 

D. SET @productID = @items.value'/Root/Product/@productID', int) 


Topic 7, Fourth Coffee 


Corporate Information 

Fourth Coffee is global restaurant chain. There are more than 5,000 locations worldwide. 

Physical Locations 

Currently a server at each location hosts a SQL Server 2012 instance. Each instance contains a database called StoreTransactions that stores all transactions from point of sale and uploads summary batches nightly. 

Each server belongs to the COFFECORP domain. Local computer accounts access the StoreTransactions database at each store using sysadmin and datareaderwriter roles. 

Planned changes 

Fourth Coffee has three major initiatives: 

The IT department must consolidate the point of sales database infrastructure. 

The marketing department plans to launch a mobile application for micropayments. 

The finance department wants to deploy an internal tool that will help detect fraud. 

Initially, the mobile application will allow customers to make micropayments to buy coffee and other items on the company web site. These micropayments may be sent as gifts to other users and redeemed within an hour of ownership transfer. Later versions will generate profiles based on customer activity that will push texts and ads generated by an analytics application. 

When the consolidation is finished and the mobile application is in production, the micropayments and point of sale transactions will use the same database. 


Existing Environment 

Existing Application Environment 

Some stores have been using several pilot versions of the micropayment application. Each version currently is in a database that is independent from the point of sales systems. Some versions have been used in field tests at local stores, and others are hosted at corporate servers. All pilot versions were developed by using SQL Server 2012. 

Existing Support Infrastructure 

The proposed database for consolidating micropayments and transactions is called CoffeeTransactions. The database is hosted on a SQL Server 2014 Enterprise Edition instance and has the following file structures: 

Business Requirements 

General Application Solution Requirements 

The database infrastructure must support a phased global rollout of the micropayment application and consolidation. 

The consolidated micropayment and point of sales database will be into a CoffeeTransactions database. The infrastructure also will include a new CoffeeAnalytics database for reporting on content from CoffeeTransactions. 

Mobile applications will interact most frequently with the micropayment database for the 

following activities: . Retrieving the current status of a micropayment; . Modifying the status of the current micropayment; and . Canceling the micropayment. 

The mobile application will need to meet the following requirements: . Communicate with web services that assign a new user to a micropayment by using a stored procedure named usp_AssignUser. . Update the location of the user by using a stored procedure named usp_AddMobileLocation. 

The fraud detection service will need to meet the following requirements: . Query the current open micropayments for users who own multiple micropayments by using a stored procedure named usp.LookupConcurrentUsers. . Persist the current user locations by using a stored procedure named usp_MobileLocationSnapshot. . Look at the status of micropayments and mark micropayments for internal investigations. . Move micropayments to dbo.POSException table by using a stored procedure named ups_DetectSuspiciousActivity. . Detect micropayments that are flagged with a StatusId value that is greater than 3 and that occurred within the last minute. 

The CoffeeAnalytics database will combine imports of the POSTransaction and MobileLocation tables to create a UserActivity table for reports on the trends in activity. Queries against the UserActivity table will include aggregated calculations on all columns that are not used in filters or groupings. 

Micropayments need to be updated and queried for only a week after their creation by the mobile application or fraud detection services. 


The most critical performance requirement is keeping the response time for any queries of 

the POSTransaction table predictable and fast. 

Web service queries will take a higher priority in performance tuning decisions over the 

fraud detection agent queries. 


Queries of the user of a micropayment cannot return while the micropayment is being updated, but can show different users during different stages of the transaction. 

The fraud detection service frequently will run queries over the micropayments that occur over different time periods that range between 30 seconds and ten minutes. 

The POSTransaction table must have its structure optimized for hundreds of thousands of active micropayments that are updated frequently. 

All changes to the POSTransaction table will require testing in order to confirm the expected throughput that will support the first year's performance requirements. 

Updates of a user's location can tolerate some data loss. 

Initial testing has determined that the POSTransaction and POSException tables will be migrated to an in-memory optimized table. 


In order to minimize disruption at local stores during consolidation, nightly processes will restore the databases to a staging server at corporate headquarters. 

Technical Requirements 


The sensitive nature of financial transactions in the store databases requires certification of the COFFECORP\\Auditors group at corporate that will perform audits of the data. Members of the COFFECORP\\Auditors group cannot have sysadmin or datawriter access to the database. Compliance requires that the data stewards have access to any restored StoreTransactions database without changing any security settings at a database level. 

Nightly batch processes are run by the services account in the COFFECORP\\StoreAgent group and need to be able to restore and verify the schema of the store databases match. 

No Windows group should have more access to store databases than is necessary. 


You need to anticipate when POSTransaction table will need index maintenance. 

When the daily maintenance finishes, micropayments that are one week old must be available for queries in UserActivity table but will be queried most frequently within their first week and will require support for in-memory queries for data within first week. 

The maintenance of the UserActivity table must allow frequent maintenance on the day's most recent activities with minimal impact on the use of disk space and the resources available to queries. The processes that add data to the UserActivity table must be able to update data from any time period, even while maintenance is running. 

The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance. 

All micropayments queries must include the most permissive isolation level available for the maximum throughput. 

In the event of unexpected results, all stored procedures must provide error messages in text message to the calling web service. 

Any modifications to stored procedures will require the minimal amount of schema changes necessary to increase the performance. 


Stress testing of the mobile application on the proposed CoffeeTransactions database uncovered performance bottlenecks. The sys.dm_os_wait_stats Dynamic Management View (DMV) shows high wait_time values for WRTTELOG and PAGEIOLATCHJJP wait types when updating the MobileLocation table. 

Updates to the MobileLocation table must have minimal impact on physical resources. 

Supporting Infrastructure 

The stored procedure usp_LookupConcurrentUsers has the current implementation: 

The current stored procedure for persisting a user location is defined in the following code: 

The current stored procedure for managing micropayments needing investigation is defined in the following code: 

The current table, before implementing any performance enhancements, is defined as follows: 

Q91. Topic 8) 

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


Q92. Topic 7) 

You need to optimize the index and table structures for POSTransaction. 

Which task should you use with each maintenance step? To answer, drag the appropriate tasks to the correct maintenance steps. Each task may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. 


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

A. Update less than 4,000 rows simultaneously. 

B. Use the PAGLOCK table hint. 

C. Wait for Proc2 to complete. 

D. Use the ROWLOCK table hint. 


Q94. You execute usp_TestSpeakers. 

You discover that usp_SelectSpeakersByName uses inefficient execution plans. 

You need to update usp_SelectSpeakersByName to ensure that the most efficient 

execution plan is used. 

What should you add at line 30 of Procedures.sql? 




D. OPTION (OPTIMIZE FOR (@LastName= 'Anderson')) 




Q95. You need to modify the Orders table to store the XML data used by the retailers. Which statement should you execute? 

A. ALTER Orders 

ADD originalOrder XML (ValidateOrder); 

B. ALTER Orders 

ADD originalOrder XML; 

C. ALTER Orders 

ADD originalOrder varchar(max); 

D. ALTER Orders 

ADD originalOrder varbinary(max); 


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


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


Q98. You run the following code: 

You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>. 

What should you implement? 

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

A. A user-defined data type 

B. An XML index 

C. A Data Definition Language (DDL) trigger 

D. A data manipulation language (DML) trigger 

E. An XML schema collection 




Q99. You need to ensure that if any of the statements in usp_UpdateSpeakerName return an error message, all of the changes executed by usp_UpdateSpeakerName are not committed to the database. 

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

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

Answer: B,D 

Q100. You are planning the ManufacturingSteps table. 

You need to define the ProductID column in the CREATE TABLE statement. 

Which code segment should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 



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


Q101. You are testing disaster recovery procedures. 

You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111. Level 16, State 3, Line 1 Cannot find server certificate with thumbprint ,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9\\ Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally." 

You need to ensure that you can restore DB1 to a different server. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 


6. You need to modify InsertInvoice to comply with the application requirements. Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 


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


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.