Want to Pass 70-464 Exam In Next HOURS? Download Now →
March 29, 2016

How to pass Microsoft 70-464 Real Exam in 24 Hours [free question 31-45]


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


Practical of 70-464 free practice exam materials and guidance for Microsoft certification for IT candidates, Real Success Guaranteed with Updated 70-464 pdf dumps vce Materials. 100% PASS Today!

2016 Mar 70-464 Study Guide Questions:

Q31. You have a database hosted on SQL Azure. 

You are developing a script to create a view that will be used to update the data in a table. 

The following is the relevant portion of the script. (Line numbers are included for reference 

only.) 

You need to ensure that the view can update the data in the table, except for the data in Column1. 

Which code segment should you add at line 06? 

A. WITH CHECK OPTION 

B. WITH VIEW_METADATA 

C. WITH ENCRYPTION 

D. WITH SCHEMABINDING 

Answer: A 

Explanation: 

The question concerning the view that has a clause "WHERE Column1 = 'City1' is wrong. 

That's not what the CHECK option is made for. Actually you will be able to update ONLY 

the rows satisfied by that WHERE clause, that is, only the rows with the Column1 being 

'City1'. 

None of the answers are valid from that question. You need a trigger to achieve that. 

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


Q32. Topic 8) 

You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar (16) data type. 

You have a certificate named Cert1. 

You need to replace Column1 with a new encrypted column named Column2 that uses one-way hashing. 

Which code segment should you execute before you remove Column1? 

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: 


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


Q34. You plan to modify a stored procedure to use temporary data. The stored procedure must meet the following requirements: 

. Favor physical memory when physical memory is available. 

. Be able to roll back changes to the temporary data. 

You need to recommend which object to add to the stored procedure. 

Which T-SQL command should you recommend? 

A. CREATE TABLE ##Table... 

B. CREATE TABLE Table... 

C. CREATE VIEW Table... 

D. CREATE PARTITION SCHEME Table... 

E. DECLARE TABLE @ Table... 

Answer: A 

Explanation: Temporary Tables You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name) 


Q35. While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan rather than a seek. 

You need to minimize the amount of time it takes to execute usp_GetFutureSessions. 

What should you do? (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 

F. Option F 

Answer: B,E 

Explanation: Future delivery dates. 


70-464 brain dumps

Update 70-464 rapidshare:

Q36. Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains a database name DB1 and a table named Customers. 

You add an additional server named SQL2 that runs SQL Server 2012. 

You need to create a distributed partitioned view. The solution must minimize the amount of network traffic. 

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

A. Add SQL2 as a Distributor. 

B. Add the Customers table to SQL2. 

C. Add SQL2 as a linked server. 

D. Create the view on SQL1. 

E. Remove the Customers table from SQL1. 

F. Create the view on SQL2. 

Answer: B,C,D,F 


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

Answer: A 


Q38. You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data. 

Each order has a sales total that can only be discovered by querying multiple tables. 

You need to ensure that the value of the sales total is returned by executing a query on Orders. 

What should you create? 

A. A calculated column that uses a scalar function 

B. A trigger that uses a table-valued function 

C. A calculated column that uses a table-valued function 

D. A trigger that uses a ranking function 

Answer: C 

Explanation: A table-valued parameter is scoped to the stored procedure, function, or 

dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type 

has scope like any other local variable that is created by using a DECLARE statement. You 

can declare table-valued variables within dynamic Transact-SQL statements and pass 

these variables as table-valued parameters to stored procedures and functions. 

Table-valued parameters offer more flexibility and in some cases better performance than 

temporary tables or other ways to pass a list of parameters. 

Incorrect: 

Not A: A scalar function would only be able to use other columns from the same table. 


Q39. You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP). 

You discover that many page splits occur when records are inserted or updated in the table. 

You need to minimize the number of page splits. 

What should you set from the index options? 

A. FILLFACTOR = 0 

B. STATISTICS_NORECOMPUTE = OFF 

C. STATISTICS_NORECOMPUTE = ON 

D. FILLFACTOR = 80 

Answer: D 

Explanation: 

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


Q40. You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure. 

Which statement should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 


70-464 exam cram

Precise 70-464 answers:

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


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

A. SAVE TRANSACTION WITH MARK @transactionID 

B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID 

C. BEGIN TRANSACTION WITH MARK @transactionID 

D. COMMIT TRANSACTION @transactionID 

Answer: C 


Q43. You are testing disaster recovery procedures. 

When you attempt to restore ProductsDB to another server, you receive the following error message: "Msg 33111, Level 16, State 3, Line 5 Cannot find server certificate with thumbprint ' 

0x9D876A3468B911ElBA4CFCBF4724019B\ 

Msg 3013, Level 16, State 1, Line 5 

RESTORE DATABASE is terminating abnormally." 

You need to ensure that you can restore ProductsDB to another server. 

Which code segment should you execute on the other server? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: D 


Q44. You plan to migrate an instance of SQL Server 2008 to a new installation of SQL Server 2012. 

You need to migrate alerts and e-mail notifications. 

Which system stored procedures should you use? (Each correct answer presents part of the solution. Choose all that apply.) 

A. sp_syspolicy_create_job 

B. sp_add_operator 

C. sp_audit_write 

D. sp_add_alert 

Answer: B,C 

Explanation: B: sp_add_operator 

Creates an operator (notification recipient) for use with alerts and jobs. 

C: sp_audit_write Adds a user-defined audit event to the USER_DEFINED_AUDIT_GROUP. If USER_DEFINED_AUDIT_GROUP is not enabled, sp_audit_write is ignored. 


Q45. You need to recommend a solution that meets the concurrency problems. 

What should you include in the recommendation? 

A. Modify the stored procedures to use the SERIALIZABLE isolation level. 

B. Modify the order in which usp_AcceptCandidate accesses the Applications table and the Candidates table. 

C. Modify the order in which usp_UpdateCandidate accesses the Applications table and the Candidates table. 

D. Modify the stored procedures to use the REPEATABLE READ isolation level. 

Answer: C 

Topic 6, Coho Winery 

Overview 

You are a database developer for a company named Coho Winery. Coho Winery has an office in London. 

Coho Winery has an application that is used to process purchase orders from customers and retailers in 10 different countries. 

The application uses a web front end to process orders from the Internet. The web front end adds orders to a database named Sales. The Sales database is managed by a server named Server1. 

An empty copy of the Sales database is created on a server named Server2 in the London office. The database will store sales data for customers in Europe. 

A new version of the application is being developed. In the new version, orders will be placed either by using the existing web front end or by loading an XML file. 

Once a week, you receive two files that contain the purchase orders and the order details of orders from offshore facilities. 

You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored procedure to copy the offshore facility orders to the Sales database. 

The Sales database contains a table named Orders that has more than 20 million rows. 

Database Definitions Database and Tables 

The following scripts are used to create the database and its tables: 

Stored Procedures 

The following are the definitions of the stored procedures used in the database: 

Indexes 

The following indexes are part of the Sales database: 

Data Import 

The XML files will contain the list of items in each order. Each retailer will have its own XML schema and will be able to use different types of encoding. Each XML schema will use a default namespace. The default namespaces are not guaranteed to be unique. 

For testing purposes, you receive an XSD file from a customer. 

For testing purposes, you also create an XML schema collection named ValidateOrder. ValidateOrder contains schemas for all of the retailers. 

The new version of the application must validate the XML file, parse the data, and store the parsed data along with the original XML file in the database. The original XML file must be stored without losing any data. 

Reported Issues 

Performance Issues 

You notice the following for the usp_GetOrdersAndItems stored procedure: 

The stored procedure takes a long time to complete. 

Less than two percent of the rows in the Orders table are retrieved by 

usp_GetOrdersAndItems. 

A full table scan runs when the stored procedure executes. 

The amount of disk space used and the amount of time required to insert data are 

very high. 

You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the stored procedure is executed. 

Page Split Issues 

Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index. 

Requirements Site Requirements 

Users located in North America must be able to view sales data for customers in North America and Europe in a single report. The solution must minimize the amount of traffic over the WAN link between the offices. 

.. .. 

Bulk Insert Requirements 

The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete. The stored procedure runs daily. If the stored procedure fails, you must ensure that the stored procedure restarts from the last successful set of rows. 

Index Monitoring Requirements 

The usage of indexes in the Sales database must be monitored continuously. Monitored data must be maintained if a server restarts. The monitoring solution must minimize the usage of memory resources and processing resources. 



see more 70-464 dumps