Exambible offers free demo for 70-464 exam. "Developing Microsoft SQL Server 2012 Databases", also known as 70-464 exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70-464 exam, will help you answer those questions. The 70-464 Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft 70-464 exams and revised by experts!
Q17. 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: B
Q18. 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/
Q19. 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: C
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
Q20. You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx http://msdn.microsoft.com/en-us/library/ms179610.aspx
http://msdn.microsoft.com/en-us/library/ff878370.aspx
Q21. 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
Q22. You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance.
The application generates all T-SQL statements dynamically and does not use stored procedures.
You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?
A. Optimize for Ad hoc Workloads
B. Enable Contained Databases
C. Allow Triggers to Fire Others
D. Scan for Startup Procs
Answer: A
Q23. You create a view by using the following code:
Several months after you create the view, users report that the view has started to return
unexpected results.
You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results.
Which code segment should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q24. 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