Top Latest 70-464 book Reviews!

Proper study guides for Abreast of the times Microsoft Developing Microsoft SQL Server 2012 Databases certified begins with Microsoft 70-464 preparation products which designed to deliver the Tested 70-464 questions by making you pass the 70-464 test at your first time. Try the free 70-464 demo right now.

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

Q2. Which code segment should you use to define the ProductDetails column? 

A. ProductDetails xml (DOCUMENT Production.ProductDetailsSchema) NULL 

B. ProductDetails xml NULL 

C. ProductDetails xml (CONTENT Production.ProductDetailsSchema) NULL 

D. ProductDetails varchar(MAX) NULL 

Answer: D 

Q3. You need to modify usp_SelectEmployeesByName to support server-side paging. The solution must minimize the amount of development effort required. 

What should you add to usp_SelectEmployeesByName? 

A. A table variable 

B. The ROWNUMBER keyword 

C. An OFFSET-FETCH clause 

D. A recursive common table expression 

Answer: C 

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/ 

Q4. 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: D 

Q5. 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: A 

Q6. You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU). 

You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits. 

The solution must minimize the amount of development effort required. 

What should you include in the recommendation? 

A. A FOREIGN KEY constraint 

B. A trigger 

C. A user-defined data type 

D. A CHECK constraint 

Answer: C 

Q7. You create a table named Customers by using the following code segment: 

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

You write the following query to retrieve all of the customers that have a name that starts with the letters SMI: 

You discover that the query performs a table scan. You need to ensure that the query uses the index. What should you do? 

A. Replace LEFT(name,3) = 'smi' by using name like 'smi%' 

B. Replace LEFT(name,3) = 'smi' by using substring(name,l,3) = 'smi' 

C. Recreate IX_Name as a unique index 

D. Recreate IX Name as a clustered index 

Answer: A 

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

Q9. You need to modify the usp_DetectSuspiciousActivity stored procedure. 

Which two actions should you perform? Each correct answer presents part of the solution. Choose two. 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

F. Option F 

Answer: D,E 

Explanation: 

Note: 

* Move micropayments to dbo.POSException table by using a stored procedure named ups_DetectSuspiciousActivity. 

Q10. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table. 

What should you use? 

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

A. A data manipulation language (DML) trigger 

B. A DEFAULT constraint 

C. A Data Definition Language (DDL) trigger 

D. A CHECK constraint 

E. A FOREIGN KEY constraint 

Answer: D 

Explanation: 

http://www.techrepublic.com/blog/programming-and-development/comparing-sql-serverconstraints-and-dmltriggers/402 http://msdn.microsoft.com/en-us/library/ms178110.aspx 

Q11. 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: D 

Explanation: 

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

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

A. OPTION (FORCESCAN) 

B. OPTION (FORCESEEK) 

C. OPTION (OPTIMIZE FOR UNKNOWN) 

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

Answer: C 

Explanation: 

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

Q13. 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 status code. 

You need to identify which options must be implemented to meet each stored procedure requirement. 

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: 

Q14. Topic 8) 

You plan to deploy two stored procedures named SP1 and SP2 that read data from the database. 

Your company identifies the following requirements for each stored procedure: 

. SP1 must allow dirty reads. 

. SP2 must place range locks on the data to ensure read consistency. 

You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks. 

Which isolation level should you identify? 

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

Answer: 

Q15. Topic 8) 

You have a SQL Server 2012 database named Database1. Database1 has a data file named 

database1_data.mdf and a transaction log file named database1_Log.ldf. Database1_Data.mdf is 

1.5 GB. 

Database1_Log.ldf is 1.5 terabytes. A full backup of Database1 is performed every day. 

You need to reduce the size of the log file. The solution must ensure that you can perform 

transaction log backups in the future. Which code segment should you execute? 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: 

Q16. You have a Microsoft SQL Azure database that contains a table named Customers. 

You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function. 

You need to create a query that returns a list of all the Customer names and the purchase dates. 

The solution must return only customers that have purchased an item during the last 12 months. 

What should you add to the query? 

A. OUTER JOIN 

B. CROSS JOIN 

C. CROSS APPLY 

D. OUTER APPLY 

Answer: C 

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