[Virtual] 70-464 Microsoft exam cram 121-135 (Mar 2016)

70-464 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

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

Free Certification Real IT 70-464 Exam pdf Collection

Question No. 121

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:


Question No. 122

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:

Explanation: 

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


Question No. 123

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: 


Question No. 124

You have an application that uses a view to access data from multiple tables. 

You need to ensure that you can insert rows into the underlying tables by using the view. 

What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Define the view by using the SCHEMABINDING option. 

C. Define the view by using the CHECK option. 

D. Materialize the view. 

Answer:

Explanation: 

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


Question No. 125

You need to identify which long running transactions use an index. Which dynamic management view should you use? 

A. sys.dm_exec_query_optimizer_info 

B. sys.dm_exec_connections 

C. sys.dm_exec_query_stats 

D. sys.dm_exec_sessions 

Answer:


Question No. 126

While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan rather than a seek. 

You need to minimize the amount of time it takes to execute usp_GetFutureSessions. 

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

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

F. Option F 

Answer: B,E 

Explanation: Future delivery dates. 


Question No. 127

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:


Question No. 128

You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql. 

Which code segment should you use to complete the function? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Question No. 129

You have a SQL Azure database. 

You execute the following script: 

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2. 

You plan to deploy an application that will search Column2. 

You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements. 

Which code segment should you execute? 

A. CREATE INDEX IX_Table1 ON Table1 (Column2) WITH FILLFACTOR-0 

B. CREATE INDEX IX_Table1 OK Table1 (Column1) INCLUDE (Column2) 

C. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL 

D. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NOT NULL 

Answer:

Explanation: 

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


Question No. 130

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 


Question No. 131

Topic 8) 

You plan to create a custom aggregation function named Median. 

You plan to deploy Median to a SQL Server 2014 server named Server1. 

You need to ensure that Median can access a web service named WebApp1. The solution 

must minimize the number of changes made to the database. 

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

You upload a certificate to Server1. 

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: 


Question No. 132

You need to resolve the performance issues of the usp_ExportOpenings stored procedure. The solution must minimize the amount of hard disk space used. 

Which statement should you execute on DB1? 

A. EXEC sp_dboption 'DB1', 'auto create statistics', 'TRUE' 

B. CREATE INDEX IX_Exp_Openings ON Openings(PostDate, FilledDate) INCLUDE 

(Description, Title, Salary); 

C. CREATE INDEX IX_Exp_Openings ON Openings(PostDate) INCLUDE (Description, 

Title, 

Salary) WHERE FilledDate IS NULL; 

D. EXEC sp_recompile 'usp_ExportOpenings' 

Answer:


Question No. 133

Topic 1) 

You need to build a stored procedure that amortizes the invoice amount. Which code segment should you use to create the stored procedure? 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: 


Question No. 134

You have a table named Rooms that contains three columns. You execute the following query: 

You discover the execution plan shown in the exhibit. (Click the Exhibit button.) 

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

What should you do? 

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

A. Include the RoomName column and the Position column in the Room_IX index. 

B. Create a nonclustered index for RoomName, Id, and Position. 

C. Create a clustered index for Id. 

D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint. 

Answer:


Question No. 135

You have a database that contains a table named Department. Department contains the names and locations of each department. 

You have a table-valued function named ProjectList() that returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function. 

You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects. 

What should you add to the query? 

A. OUTER APPLY 

B. OUTER JOIN 

C. CROSS JOIN 

D. CROSS APPLY 

Answer: