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

All About 70-464 exams Jan 2018


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


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


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


Q3. You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName. You discover that usp_SelectEmployeesByName uses inefficient execution plans. 

You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used. 

What should you add at line 31 of StoredProcedures.sql? 

A. OPTION (ROBUST PLAN) 

B. OPTION (OPTIMIZE FOR UNKNOWN) 

C. OPTION (KEEP PLAN) 

D. OPTION (KEEPFIXED PLAN) 

Answer:

Explanation: 

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


Q4. You plan to create a new column in a table. The column must meet the following requirements: 

. Be able to store images that are larger than 1 MB each. 

. Be able to access the images from Microsoft .NET Framework applications. 

You need to recommend which data type must be used in the column. 

Which data type should you recommend? 

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

A. nvarchar 

B. varbinary 

C. image 

D. FileStream 

Answer:


Q5. You need to implement a solution that meets the site requirements. 

What should you implement? 

A. A non-indexed view on Server1 

B. A non-indexed view on Server2 

C. A distributed view on Server1 

D. A distributed view on Server2 

Answer:


Q6. You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2. 

Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table. 

Schema1.Udf1 was defined by using the following code: You need to write a query that will contain the following columns: 

... 

Country CountryID CustomerName 

The solution must meet the following requirements: 

.. 

Rows must be returned only if the function returns data. 

The amount of time it takes the query to execute must be minimized. 

Which query should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Q7. You have a Microsoft SQL Azure database that contains a table named Employees. 

You create a non-clustered index named EmployeeName on the name column. 

You write the following query to retrieve all of the employees that have a name that starts 

with the letters JOH: 

You discover that the query performs a table scan. 

You need to ensure that the query uses EmployeeName. 

What should you do? 

A. Recreate EmployeeName as a unique index 

B. Recreate EmployeeName as a clustered index 

C. Replace LEFT(name,3) = 'JOH' by using name like 'JOH%' 

D. Replace LEFT(name,3) = 'JOH' by using substring(name, 1, 3) = 'JOH' 

Answer:


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

Explanation: NOLOCK 

Is equivalent to READUNCOMMITTED. 

READUNCOMMITTED 

Specifies that dirty reads are allowed. 


Q9. You need to implement a solution that addresses the index monitoring requirements. 

What should you do? 

A. Schedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database. 

B. Create a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATION group. 

C. Create a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters. 

D. Schedule a SQL Server Profiler trace, and then save the trace data to a table in the database. 

Answer:


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


Q11. You have a Microsoft SQL Azure database. You have the following stored procedure: 

You discover that the stored procedure periodically fails to update Person.Contact. 

You need to ensure that Person.Contact is always updated when UpdateContact 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 12: 

WITH (UPDLOCK) 

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

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

WITH (UPDLOCK) 

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

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

Answer:

Explanation: * Overall, you should use UPDLOCK when you read a value that you plan to update later in the same transaction to prevent the value from changing. 

* UPDLOCK Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead. When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level. 


Q12. You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails. 

For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


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

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/ 


Q14. You need to add a new column named Confirmed to the Attendees table. 

The solution must meet the following requirements: 

. Have a default value of false. 

. Minimize the amount of disk space used. 

Which code block should you use? 

A. ALTER TABLE Attendees 

ADD Confirmed bit DEFAULT 0; 

B. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT '1' 

C. ALTER TABLE Attendees 

ADD Confirmed bit DEFAULT 1; 

D. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT ‘1’; 

Answer:

Explanation: 

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


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


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


Q17. You need to implement a solution that solves the performance issues of usp_GetOrdersAndItems. 

Which statements should you execute? 

A. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate, Amount) 

B. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate) INCLUDE(Amount) WHERE ShipDate IS NULL 

C. CREATE INDEX IX_Orders_Active ON Orders(DeliveryDate, Amount) WHERE ShipDate IS NULL 

D. CREATE INDEX IX_Orders_Active ON Orders(ShipDate, DeliveryDate) INCLUDE( Amount) 

Answer: