70-464 Exam Questions - Online Test

70-464 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

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!

Q1. You need to ensure that usp_AddXMLOrder can be used to validate the XML input from the retailers. 

Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.) 

A. @schema varbinary(100). 

B. @items varchar(max). 

C. @schema sysname. 

D. @items varbinary(max). 

E. @items xml. 

F. @schema xml. 

Answer: C,E 

Q2. Topic 8) 

Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1 contains three tables. 

The tables are configured as shown in the following table. 

You plan to create indexes for the tables. 

You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables. 

Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area. 

Answer: 

Q3. You review a query that runs slowly. The query accesses data in a table named Schema1.Table1. 

The following is the relevant portion of the execution plan for the query: You need to create the missing index. 

Which code segment should you execute? 

A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3 

B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) 

C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4) 

D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4) 

Answer: C 

Q4. Topic 8) 

You have a table named Customers that has a clustered index defined on the ID column. 

You write a script to create a stored procedure. 

You need to complete the script for the stored procedure. The solution must minimize the 

number of locks and deadlocks. 

What should you do? 

To answer, drag the appropriate option to the correct location in the answer area. (Answer 

choices may be used once, more than once, or not at all.) 

Answer: 

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

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

Q7. You execute the following code: The execution plan for the query is shown in the exhibit. (Click the Exhibit button.) 

You need to prevent the key lookup. What should you modify? 

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

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 

Q8. Topic 8) 

You administer a SQL Server 2014 instance. 

The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors. 

The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin]. 

A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin]. 

You need to set up the Resource Group and Workgroup Pools on the instance. 

You have the following requirements: 

. Both processes must never use more than 50 percent of the CPU at any one time. . The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10. . The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes. 

Develop the solution by selecting and arranging the required code blocks in the correct order. 

You may not need all of the code blocks. 

Answer: 

149. You are creating a table named Orders. 

You need to ensure that every time a new row is added to the Orders table, a table that is 

used for auditing is updated. 

What should you use? 

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

A. A Data Definition Language (DDL) trigger 

B. A DEFAULT constraint 

C. A CHECK constraint 

D. A FOREIGN KEY constraint 

E. A data manipulation language (DML) trigger 

Answer: E 

Explanation: 

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

Q9. You execute the following code. After populating the Employees table with 10,000 rows, you execute the following query: 

You need to reduce the amount of time it takes to execute the query. 

What should you do? 

A. Partition the table and use the JobTitle column for the partition scheme. 

B. Change SUBSTRING(JobTitle,l,l) = 'C to JobTitle LIKE 'c%’ 

C. Change SUBSTRING (JobTitle, 1,1] = 'c' to LEFT(JobTitle ,1) = 'c'. 

D. Replace IX_Employees with a clustered index. 

Answer: B 

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