Up to the immediate present Microsoft 70-464 - An Overview 1 to 10

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-464 Exam Dumps (PDF & VCE):
Available on: https://www.certleader.com/70-464-dumps.html


70-464 Product Description:
Exam Number/Code: 70-464 vce
Exam name: Developing Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-464 Developing Microsoft SQL Server 2012 Databases

70-464 examcollection

It is impossible to pass Microsoft 70-464 exam without any help in the short term. Come to Testking soon and find the most advanced, correct and guaranteed Microsoft 70-464 practice questions. You will get a surprising result by our Most recent Developing Microsoft SQL Server 2012 Databases practice guides.

Q1. You have a SQL Server 2012 database named Database1. 

Database1 has a table named Customers. Customers contains more than 1 million rows. 

The database has a stored procedure that was created by using the following script: 

You need to ensure that up_customers returns rows when the following statement is executed: 

EXECUTE up_customers'1,2,3,4,5' 

What should you do? 

A. Update @CustcmerTypelist to use the int data type. 

B. Convert @CustomerTypeList to a table variable. 

C. Convert @CustomerTypeList to an XML variable. 

D. Update @CustomerTypeList to use the XML data type. 

Answer:

171. You have a SQL Azure database. 

You need to identify which keyword must be used to create a view that will be indexed. 

Which keyword should you identify? 

A. SCHEMABINDING 

B. VIEW_METADATA 

C. DISTINCT 

D. DEFAULT 

Answer:

Explanation: 

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


Q2. You have a Microsoft SQL Azure database named DBAzure1. 

You create a table in DBAzure1 by using the following script: 

You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated. 

What should you recommend creating? 

A. A CHECK constraint 

B. A filtered index 

C. A columnstore index 

D. A UNIQUE constraint 

Answer:


Q3. You are testing disaster recovery procedures. 

You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111. Level 16, State 3, Line 1 Cannot find server certificate with thumbprint ,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9\\ Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally." 

You need to ensure that you can restore DB1 to a different server. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

6. You need to modify InsertInvoice to comply with the application requirements. Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Q4. You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows. 

Proc1 updates data in Table1. Proc2 reads data from Table1. 

You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1. 

You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating. 

What should you change Proc1 to do? 

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

A. Update less than 4,000 rows simultaneously. 

B. Use the PAGLOCK table hint. 

C. Wait for Proc2 to complete. 

D. Use the ROWLOCK table hint. 

Answer:


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

What should you add to usp_SelectSpeakersByName? 

A. A table variable 

B. An OFFSET-FETCH clause 

C. The ROWNUMBER keyword 

D. A recursive common table expression 

Answer:

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/ 


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

Explanation: 

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


Q7. You need to create the object used by the parameter of usp_UpdateEmployeeName. 

Which code segment should you use? 

A. CREATE XML SCHEMA COLLECTION EmployeesInfo 

B. CREATE TYPE EmployeesInfo AS Table 

C. CREATE SCHEMA EmployeesInfo 

D. CREATE TABLE EmployeesInfo 

Answer:

Explanation: 

Example Usage of Table-Valued Parameters (Database Engine) http://msdn.microsoft.com/en-us/library/bb510489.aspx (Benefits of using Table-Valued Parameters) 

/* Create a table type. */ 

CREATE TYPE LocationTableType AS TABLE 

( LocationName VARCHAR(50) 

, CostRate INT ); 

GO 

/* Create a procedure to receive data for the table-valued parameter. */ 

CREATE PROCEDURE dbo. usp_InsertProductionLocation 

@TVP LocationTableType READONLY 

AS 

SET NOCOUNT ON 

INSERT INTO AdventureWorks2012.Production.Location 

(Name 

,CostRate 

,Availability 

,ModifiedDate) 

SELECT *, 0, GETDATE() 

FROM @TVP; 

GO 

Also: 

http://msdn.microsoft.com/en-us/library/ms175007.aspx(CREATE TYPE *tabletypename* 

AS 

TABLE) 

http://msdn.microsoft.com/en-us/library/ms175010.aspx(table data types) 

Wrong Answers: 

http://msdn.microsoft.com/en-us/library/ms174979.aspx(CREATE TABLE) 

http://msdn.microsoft.com/en-us/library/ms189462.aspx(CREATE SCHEMA) 

http://msdn.microsoft.com/en-us/library/ms176009.aspx(CREATE XML SCHEMA 

COLLECTION) 


Q8. Topic 8) 

You have a database that contains three tables. The tables are configured as shown in the following table. 

You have the following query: 

The execution plan for the query is shown in the exhibit. (Click the Exhibit button.) 

You need to create one index to minimize the amount of time it takes to execute the query. 

What should you do? 

To answer, drag the appropriate columns to the correct locations in the answer area. 

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

Answer: 


Q9. You need to modify the Orders table to store the XML data used by the retailers. Which statement should you execute? 

A. ALTER Orders 

ADD originalOrder XML (ValidateOrder); 

B. ALTER Orders 

ADD originalOrder XML; 

C. ALTER Orders 

ADD originalOrder varchar(max); 

D. ALTER Orders 

ADD originalOrder varbinary(max); 

Answer:


Q10. You discover that the usp_GetOrdersAndItems stored procedure takes a long time to complete while usp_AddOrder or usp_AddXMLOrder run. 

You need to ensure that usp_GetOrdersAndItems completes as quickly as possible. 

What should you do? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SERIALIZABLE. 

B. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON statement. 

C. Set the isolation level of the usp_AddOrder stored procedure to SERIALIZABLE. 

D. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SNAPSHOT. 

E. Set the isolation level of the usp_AddOrder stored procedure to SNAPSHOT. 

F. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION OFF statement. 

Answer: B,D 



see more free 70-464 exam dumps