70-464 pdf : May 2016 Edition

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 May 70-464 Study Guide Questions:

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


Q2. You have a SQL Server 2012 database named Database1. You execute the following code: 

You insert 3 million rows into Sales. 

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

What should you do? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 


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


Q4. Topic 8) 

You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data. 

Salary must meet the following requirements: 

. Contain numeric data. 

. Contain only encrypted data that remains encrypted in memory. You need to identify which encryption type and data type must be used for Salary. 

Which encryption type and data type should you identity? 

To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area. 

Answer: 

181. You have a table named Table1 that stores customer data. 

Each customer has a credit limit that can only be discovered by querying multiple tables. 

You need to ensure that the value of the credit limit is returned by executing a query on 

Table1. 

What should you create? 

A. A trigger that uses a ranking function 

B. A trigger that uses a table-valued function 

C. A calculated column that uses a table-valued function 

D. A calculated column that uses a scalar function 

Answer: C 


Q5. Topic 8) 

You create a table that contains the following script: 

You need to prevent duplicate values in the EmployeeID field. 

Which five code segments should you use? 

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: 


70-464 test question

Up to the immediate present 70-464 study guide:

Q6. You have a database named database1. 

Database developers report that there are many deadlocks. 

... 

You need to implement a solution to monitor the deadlocks. The solution must meet the following requirements: 

Support real-time monitoring. 

Be enabled and disabled easily. 

Support querying of the monitored data. 

What should you implement? 

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

A. Log errors by using trace flag 1222 

B. Log errors by using trace flag 1204 

C. A SQL Server Profiler template 

D. An Extended Events session 

Answer: D 

Explanation: 

http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/08/12/monitor-deadlock-in-sql-2012/ http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_d eadlocks_2d00_in_2d00_sql_2d00_server.aspx 


Q7. You need to implement a solution that meets the security requirements. Which statement should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 


Q8. You need to implement a solution that addresses the index monitoring requirements. 

What should you do? 

A. Schedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database. 

B. Create a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATION group. 

C. Create a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters. 

D. Schedule a SQL Server Profiler trace, and then save the trace data to a table in the database. 

Answer: A 


Q9. You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID. 

Which line of code should you modify in CountryFromID.sql? 

A. 04 

B. 05 

C. 06 

D. 19 

Answer: D 

Explanation: 

http://msdn.microsoft.com/en-us/library/ms186755.aspx http://msdn.microsoft.com/en-us/library/ms191320.aspx 


Q10. You need to add a new column named Confirmed to the Employees table. The solution must meet the following requirements: 

. Have a default value of TRUE. 

. Minimize the amount of disk space used. 

Which code segment should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: D 


70-464 practice

Tested ms 70-464:

Q11. You have a text file that contains an XML Schema Definition (XSD). 

You have a table named Schema1.Table1. 

You have a stored procedure named Schema1.Proc1 that accepts an XML parameter 

named Param1. 

You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that 

apply.) 

A. Define an XML column in Table1 by using an XML schema collection. 

B. Create an XML schema collection in the database from the text file. 

C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection. 

D. use the modify method to insert the XML schema into each row of the XML column in Table1. 

Answer: A,B,D 

Explanation: Explanation/Reference: http://msdn.microsoft.com/en-us/library/bb510420.aspx http://msdn.microsoft.com/en-us/library/ms187856.aspx http://msdn.microsoft.com/en-us/library/ms176009.aspx http://msdn.microsoft.com/en-us/library/hh403385.aspx http://msdn.microsoft.com/en-us/library/ms184277.aspx 


Q12. You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails. 

For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 


Q13. You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a 

slow local disk. 

You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores. 

The stored procedure will use temporary storage defined as follows: 

The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data. 

For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure. 

What should you do? 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: A 

Explanation: * You must specify a value for the BUCKET_COUNT parameter when you create the memory-optimized table. In most cases the bucket count should be between 1 and 2 times the number of distinct values in the index key. If the index key contains a lot of duplicate values, on average there are more than 10 rows for each index key value, use a nonclustered index instead You may not always be able to predict how many values a particular index key may have or will have. Performance should be acceptable if the BUCKET_COUNT value is within 5 times of the actual number of key values. 


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


Q15. You need to provide referential integrity between the Offices table and Employees table. 

Which code segment or segments should you add at line 27 of Tables.sql? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C,D 

Explanation: 

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

Topic 4, Scenario 4 

Application Information 

You are a database administrator for a manufacturing company. 

You have an application that stores product data. The data will be converted to technical diagrams for the manufacturing process. 

The product details are stored in XML format. Each XML must contain only one product that has a root element named Product. A schema named Production.ProductSchema has been created for the products xml. 

You develop a Microsoft .NET Framework assembly named ProcessProducts.dll that will be used to convert the XML files to diagrams. The diagrams will be stored in the database as images. ProcessProducts.dll contains one class named ProcessProduct that has a method name of Convert(). ProcessProducts.dll was created by using a source code file named ProcessProduct.cs. 

All of the files are located in C:\Products\. 

The application has several performance and security issues. 

You will create a new database named ProductsDB on a new server that has SQL Server 

2012 installed. ProductsDB will support the application. 

The following graphic shows the planned tables for ProductsDB: 

You will also add a sequence named Production.ProductID_Seq. 

You plan to create two certificates named DBCert and ProductsCert. You will create ProductsCert in master. You will create DBCert in ProductsDB. 

You have an application that executes dynamic T-SQL statements against ProductsDB. A sample of the queries generated by the application appears in Dynamic.sql. 

Application Requirements 

The planned database has the following requirements: All stored procedures must be signed. The amount of disk space must be minimized. Administrative effort must be minimized at all times. The original product details must be stored in the database. An XML schema must be used to validate the product details. The assembly must be accessible by using T-SQL commands. A table-valued function will be created to search products by type. Backups must be protected by using the highest level of encryption. Dynamic T-SQL statements must be converted to stored procedures. Indexes must be optimized periodically based on their fragmentation. Manufacturing steps stored in the ManufacturingSteps table must refer to a product by the same identifier used by the Products table. 

ProductDetails_Insert.sql 

........... 

Product, xml 

All product types are 11 digits. The first five digits of the product id reference the category of the product and the remaining six digits are the subcategory of the product. 

The following is a sample customer invoice in XML format: 

ProductsByProductType.sql 

Dynamic.sql 

Category FromType.sql 

IndexManagement.sql 

30. An administrator provides a digital certificate named ServerCert. 

You need to implement Transparent Data Encryption (TDE) on ProductsDB. 

Which code segment should you use? 

A. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO B. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO 

C. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO 

D. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO 

Answer: C 

Explanation: 

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



see more http://www.certshared.com/exam/70-464/

Microsoft 70-464 Certification Sample Questions and Answers: https://www.braindumpsall.net/70-464-dumps/

P.S. New 70-464 dumps PDF: http://www.4easydumps.com/70-464-dumps-download.html