70-464 Exam
Top 10 cram 70-464 for IT specialist (71 to 80)
It is more faster and easier to pass the Microsoft 70-464 exam by using Refined Microsoft Developing Microsoft SQL Server 2012 Databases questuins and answers. Immediate access to the Improved 70-464 Exam and find the same core area 70-464 questions with professionally verified answers, then PASS your exam with a high score now.
Q71. You have a database that is accessed by 300 concurrent users.
You need to log all of the queries that become deadlocked. The solution must meet the following requirements:
. Provide a representation of the deadlock in XML format.
. Minimize the impact on the server.
What should you create?
A. A SQL Server Profiler trace
B. A script that enables trace flags
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
D. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
Answer: A
Explanation: Analyze Deadlocks with SQL Server Profiler Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio.
Q72. You need to identify the cause of the page split issues.
Which SQL Server feature should you use?
A. DBCC REINDEX
B. SQL Server Profiler
C. Extended Events
D. DBCC TRACEOFF
Answer: C
46. You need to design a solution that meets the refactoring requirements.
Which type of object should you include in the solution?
A. An indexed view
B. An aggregate function
C. A distributed view
D. A table-valued function
Answer: D
Q73. You have the following query on a disk-based table:
You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)
The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)
You need to reduce the amount of time it takes to complete the query. You must achieve this goal as quickly as possible.
What should you do?
A. Reorganize the index.
B. Update statistics.
C. Create an index on LastName.
D. Rebuild the index.
Answer: C
Q74. 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: C
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.
Q75. 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
Q76. 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/
Q77. 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
Q78. 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: C
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.
Q79. You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure.
Which statement should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q80. 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