How to pass Microsoft 70-464 Real Exam in 24 Hours [practice test 46-60]

Question No. 46

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. 


Question No. 47

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:

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. 


Question No. 48

Topic 8) 

You are planning two stored procedures named SProc1 and SProc2. 

You identify the following requirements: 

. SProc1 must return a table. 

. SProc2 must return a scalar value. You need to identify which option must be implemented for each stored procedure to return the desired data. 

Which options should you identify? 

To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.) 

Answer: 


Question No. 49

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 

Answer:

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 

Answer:


Question No. 50

You need to implement security for the restore and audit process. What should you do? 

A. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. 

B. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. 

C. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. 

D. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. 

Answer:


Question No. 51

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:

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. 


Question No. 52

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs. 

What should you do? 

A. Execute sp_help usp_GetOrdersByProduct\ 

B. Add WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct. 

C. Add WITH RECOMPILE to line 64 in usp.GetOrdersByProduct. 

D. Execute sp_recompile usp.GetOrdersByProduct'. 

Answer:


Question No. 53

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: 


Question No. 54

You are creating a table to support an application that will cache data outside of SQL Server. 

The application will detect whether cached values were changed before it updates the 

values. 

You need to create the table, and then verify that you can insert a row into the table. 

Which code segment should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms182776.aspx http://msdn.microsoft.com/en-us/library/ms187942.aspx http://msdn.microsoft.com/en-us/library/ms190348.aspx 


Question No. 55

You need to prepare the database to use the .NET Framework ProcessProducts component. 

Which code segments should you execute? (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 

G. Option G 

Answer: A,C,D,E 

Explanation: 

http://msdn.microsoft.com/en-us/library/ms131048.aspx http://msdn.microsoft.com/en-us/library/ms131052.aspx http://msdn.microsoft.com/en-us/library/ms189524.aspx http://msdn.microsoft.com/en-us/library/ms345106.aspx http://msdn.microsoft.com/en-us/library/ms131107.aspx 


Question No. 56

Topic 8) 

You run the following code segment: 

After you add 10,000 rows to Customers, you discover that the index is fragmented. 

You need to defragment the index in the least amount of time. 

Which code segment should you execute? 

To answer, drag the appropriate value to the correct location in the code segment in the 

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

Answer: 


Question No. 57

You administer an instance of SQL Server 2014. 

You are tasked with tuning a common set of queries. You have the results of several test executions, along with query plans. The schema and the data for all database object(s) used remain unchanged between executions. The QueryTime column is defined as a computed column that uses the GETDATE() system function. The query plans and results are shown below: You need to make an initial diagnosis of the situation, based solely on this input 

Which two statements can you make about the performance characteristics of this query? Each correct answer presents a complete solution. Choose two. 

A. The queries would perform better if the index named AccountNumber included the Name and QueryTime column. 

B. The queries would perform worse if the index named AccountNumber included the NameColumn. 

C. The queries would perform better if the index named AccountNumber included the Name column. 

D. The object Account is a table, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount. 

E. The object Account is an indexed view, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount. 

F. The object Account is a view, joining the Account-AccountNumber and Account.PKAccount objects together. 

Answer: B,D 


Question No. 58

You are evaluating the table design. 

You need to recommend a change to Tables.sql that reduces the amount of time it takes for usp_AttendeesReport to execute. 

What should you add at line 14 of Tables.sql? 

A. FullName nvarchar(100) NOT NULL CONSTRAINT DF_FuIlName DEFAULT (dbo.CreateFullName (FirstName, LastName)), 

B. FullName AS (FirstName +‘ ’+ LastName), 

C. FullName nvarchar(100) NOT NULL DEFAULT (dbo.CreateFullName (FirstName, LastName)). 

D. FullName AS (FirstName +‘ ’+ LastName) PERSISTED, 

Answer:

Explanation: 

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


Question No. 59

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. 

Answer: 


Question No. 60

You need to implement a solution that addresses the upload requirements. Which code segment should you use to implement the Conversions assembly? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Microsoft 70-464 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-464-dumps/

P.S. New 70-464 dumps PDF: http://www.4easydumps.com/70-464-dumps-download.html