[Actual] 70-464 Microsoft exam cost 121-135 (Apr 2016)

★ 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

Exam Code: 70-464 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Developing Microsoft SQL Server 2012 Databases
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-464 Exam.

2016 Apr 70-464 Study Guide Questions:

Q121. You have a SQL Server 2012 database that contains a table named Users. The Users table contains usernames and passwords. 

You need to ensure that all new records have a password. 

Which code segment should you use? 

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


Q122. You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2. 

Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table. 

Schema1.Udf1 was defined by using the following code: You need to write a query that will contain the following columns: 

... 

Country CountryID CustomerName 

The solution must meet the following requirements: 

.. 

Rows must be returned only if the function returns data. 

The amount of time it takes the query to execute must be minimized. 

Which query should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: B 


Q123. Your network contains a server that has SQL Server 2014 installed. You create a table by using the following script: 

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

What should you recommend creating? 

A. A UNIQUE constraint 

B. A filtered index 

C. A columnstore index 

D. A CHECK constraint 

Answer: A 


Q124. Topic 8) 

You have a SQL Server 2012 database named database1. 

Users report that queries that usually take less than one second to execute, take more than 

30 seconds to execute. 

You need to view the server resource consumption when the queries are executed. 

What should you do? 

To answer, move the appropriate actions from the list of actions to the answer area and 

arrange them in the correct order. 

Answer: 


Q125. 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: B 

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) 


70-464 free exam questions

Avant-garde 70-464 free exam:

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


Q127. Which data type should you use for CustomerID? 

A. varchar(11) 

B. bigint 

C. nvarchar(11) 

D. char(11) 

Answer: D 

Explanation: 

Invoices.xml All customer IDs are 11 digits. The first three digits of a customer ID represent the customer's country. The remaining eight digits are the customer's account number. int: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) (just 10 digits max) bigint: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) http://msdn.microsoft.com/en-us/library/ms176089.aspx http://msdn.microsoft.com/en-us/library/ms187745.aspx 


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


Q129. You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName. 

What should you include in the recommendation? 

A. The Distributed Transaction Coordinator (DTC) 

B. Transactional replication 

C. Change data capture 

D. Change tracking 

Answer: A 


Q130. Topic 8) 

You plan to create a custom aggregation function named Function1. 

You plan to deploy Function1 to SQL Server 2012. 

You need to ensure that Function1 can access a web service. The solution must minimize 

the number of changes made to the database. 

You create a Microsoft .NET Framework class that contains the definition of Function1. 

You upload a certificate to SQL Server. 

What three tasks should you perform next? 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. 

Answer: 


70-464 question

100% Guarantee 70-464 training tools:

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


Q132. You have a Microsoft SQL Azure database. 

You have the following stored procedure: 

You discover that the stored procedure periodically fails to update HR.Employees. 

You need to ensure that HR.Employees is always updated when up_employees 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 05: 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

B. Add the following line of code to line 13: 

WITH (UPDLOCK) 

C. Add the following line of code to line 05: 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

D. Add the following line of code to line 08: 

WITH (UPDLOCK) 

Answer: D 


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


Q134. You need to implement a solution that resolves the salary query issue. Which statement should you execute on DB1? 

A. Option A B. Option B 

C. Option C 

D. Option D 

Answer: A 


Q135. Topic 8) 

You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named FG1, FG2, FG3, and FG4. You execute the following code: 

Two million rows are added to dbo.Sales. 

You need to move the data from the first partition to a new table named SalesHistory and, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months. 

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: 



see more Developing Microsoft SQL Server 2012 Databases