Microsoft Developing Microsoft SQL Server Databases - 070-464 Exam Questions
QUESTION NO: 1
You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName. You discover that usp_SelectEmployeesByName uses inefficient execution plans.
You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used.
What should you add at line 31 of StoredProcedures.sql?
You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName. You discover that usp_SelectEmployeesByName uses inefficient execution plans.
You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used.
What should you add at line 31 of StoredProcedures.sql?
Correct Answer: C
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 2
You need to create a function that will use a SELECT statement in ProductsByProductType.sql.
Which code segment should you use to complete the function?
You need to create a function that will use a SELECT statement in ProductsByProductType.sql.
Which code segment should you use to complete the function?
Correct Answer: D
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 3
You need to modify InsertInvoice to comply with the application requirements.
Which code segment should you execute?
You need to modify InsertInvoice to comply with the application requirements.
Which code segment should you execute?
Correct Answer: C
QUESTION NO: 4
You need to design the UserActivity table.
Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.
You need to design the UserActivity table.
Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
1 - Create a clustered columnstore index.
2 - Create a partitioning scheme for use by the table.
3 - Use an ALTER INDEX REORGANIZE on a specific partition.
Explanation:
Note:
* Creating a partitioned table or index typically happens in four parts:
Create a filegroup or filegroups and corresponding files that will hold the partitions specified by the partition scheme.
Create a partition function that maps the rows of a table or index into partitions based on the values of a specified column.
Create a partition scheme that maps the partitions of a partitioned table or index to the new filegroups.
Create or modify a table or index and specify the partition scheme as the storage location.
* Reorganizing an index uses minimal system resources.
* From scenario:
/ The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance.
/ The CoffeeAnalytics database will combine imports of the POSTransaction and MobileLocation tables to create a UserActivity table for reports on the trends in activity. Queries against the UserActivity table will include aggregated calculations on all columns that are not used in filters or groupings.
/ When the daily maintenance finishes, micropayments that are one week old must be available for queries in UserActivity table but will be queried most frequently within their first week and will require support for in-memory queries for data within first week.
The maintenance of the UserActivity table must allow frequent maintenance on the day's most recent activities with minimal impact on the use of disk space and the resources available to queries. The processes that add data to the UserActivity table must be able to update data from any time period, even while maintenance is running.
* Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. Often, these are queries for data warehousing workloads. Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.
QUESTION NO: 5
You need to implement a solution that meets the site requirements.
What should you implement?
You need to implement a solution that meets the site requirements.
What should you implement?
Correct Answer: A
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 6
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.
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
QUESTION NO: 7
You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: A
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 8
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?
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?
Correct Answer: C
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
