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

Question No. 1

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:


Question No. 2

You need to implement a solution that addresses the page split issues. 

Which statement should you execute? 

A. ALTER INDEX IX_Orders_ShipDate ON Orders 

REBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON); 

B. ALTER INDEX IX_Orders_ShipDate ON Orders 

REBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON); 

C. ALTER INDEX IX_Orders_ShipDate ON Orders 

REBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON); 

D. ALTER INDEX IX_Orders_ShipDate ON Orders 

REBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON); 

Answer:


Question No. 3

While testing the CategoryFromType function, you discover that the function is returning 'Other'. 

You need to update CategoryFromType to return the category name. 

Which line of code should you modify in CategoryFromType.sql? 

A. 04 

B. 05 

C. 12 

D. 14 

Answer:


Question No. 4

You have a database named database1. 

Database developers report that there are many deadlocks. 

... 

You need to implement a solution to monitor the deadlocks. The solution must meet the following requirements: 

Support real-time monitoring. 

Be enabled and disabled easily. 

Support querying of the monitored data. 

What should you implement? 

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

A. Log errors by using trace flag 1222 

B. Log errors by using trace flag 1204 

C. A SQL Server Profiler template 

D. An Extended Events session 

Answer:

Explanation: 

http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/08/12/monitor-deadlock-in-sql-2012/ http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_d eadlocks_2d00_in_2d00_sql_2d00_server.aspx 


Question No. 5

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:

Explanation: 

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


70-464 exam question

Question No. 6

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:


Question No. 7

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

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

You need to create a function that will use a SELECT statement in ProductsByProductType.sql. 

Which code segment should you use to complete the function? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Explanation: 

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


Question No. 10

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:


70-464 download

Question No. 11

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. 


Question No. 12

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: 


Question No. 13

You need to create a script that automates the export of the XML data. The script must meet the integration requirements. 

What should you include in the script? 

A. The CREATE SERVER ROLE command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures. 

B. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures. 

C. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures. 

D. The CREATE SERVER ROLE command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures. 

Answer:


Question No. 14

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:


Question No. 15

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.