70-464 Exam Questions - Online Test

70-464 Premium VCE File

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

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

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

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

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

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

Q45. Topic 8) 

You are designing two stored procedures named Procedure1 and Procedure2. 

You identify the following requirements: 

. Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure. . Procedure2 must use business logic that resides in a Microsoft .NET Framework assembly. 

You need to identify the appropriate technology for each stored procedure. 

Which technologies should you identify? 

To answer, drag the appropriate technology to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.) 

Answer: 

Q46. You plan to create a new column in a table. The column must meet the following requirements: 

. Be able to store images that are larger than 1 MB each. 

. Be able to access the images from Microsoft .NET Framework applications. 

You need to recommend which data type must be used in the column. 

Which data type should you recommend? 

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

A. nvarchar 

B. varbinary 

C. image 

D. FileStream 

Answer: D 

Q47. You need to create the object used by the parameter of usp_InsertSessions. Which statement should you use? 

A. CREATE XML SCHEMA COLLECTION SessionDataTable 

B. CREATE TYPE SessionDataTable AS Table 

C. CREATE SCHEMA SessionDataTable 

D. CREATE TABLE SessionDataTable 

Answer: B 

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

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

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