Online Access Free 70-464 Practice Test

You plan to design an application that temporarily stores data in a SQL Azure database.
You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session.
Which type of objects should you identify?

• A.Common table expressions (CTEs)
• B.Table variables
• C.Temporary stored procedures
• D.Temporary tables

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.CREATE INDEX IX_Exp_Openings ON Openings(PostDate) INCLUDE (Description, Title, Salary) WHERE FilledDate IS NULL;
• B.EXEC sp_recompile 'usp_ExportOpenings';
• C.EXEC sp_dboption 'DB1', 'auto create statistics', 'TRUE';
• D.CREATE INDEX IX_Exp_Openings ON Openings(PostDate, FilledDate) INCLUDE (Description, Title, Salary);

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.

Correct Answer:

You are designing a database for a university. The database will contain two tables named Classes and StudentGrades that have the following specifications:
Classes will store brochures in the XPS format.
The brochures must be structured in folders and must be accessible byusing UNC paths.
StudentGrades must be backed up on a separate schedule than the rest ofthe database.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

Correct Answer:

Explanation:
http://msdn.microsoft.com/en-us/library/gg471497.aspx
http://msdn.microsoft.com/en-us/library/ff929144.aspx
http://msdn.microsoft.com/en-us/library/ms189563.aspx
http://msdn.microsoft.com/en-us/library/ms190174.aspx
http://msdn.microsoft.com/en-us/library/ms187956.aspx

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.

Correct Answer:

Explanation:
Note:
* Box 1 - Box 2:
/ You need to move the data from the first partition to a new table named SalesHistory.
/ First create the new table, then move the contents of the first partition.
*( Box 3 Box 4) Drop the partition scheme and then the partition function and the recreate them (box 5-box6). First recreate the partition function.
/You need, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months.
/ A partition function can be dropped only if there are no partition schemes currently using the partition function. If there are partition schemes using the partition function, DROP PARTITION FUNCTION returns an error.