070-464 無料問題集「Microsoft Developing Microsoft SQL Server Databases」
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?
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
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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.
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.
正解：
1 - Execute the CREATE ASSEMBLY statement.
2 - Use the certificate to add a digital signature to the assembly.
3 - Execute the CREATE AGGREGATE statement.
Explanation:
Note:
* CREATE AGGREGATE
Creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the .NET Framework. For the Database Engine to bind the aggregate function to its implementation, the .NET Framework assembly that contains the implementation must first be uploaded into an instance of SQL Server by using a CREATE ASSEMBLY statement.
You have a database that uses the following management views:
Sys.dm_os_volume_stats
Sys.dm_db_partition_stats
Sys.dm_db_file_space_usage
Sys.fulltext_indexes
You plan to migrate the database to Microsoft SQL Azure.
You need to identify which view can be used in SQL Azure.
Which view should you identify?
Sys.dm_os_volume_stats
Sys.dm_db_partition_stats
Sys.dm_db_file_space_usage
Sys.fulltext_indexes
You plan to migrate the database to Microsoft SQL Azure.
You need to identify which view can be used in SQL Azure.
Which view should you identify?
正解：C
解答を投票する
You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
正解：
Explanation:
Box 1: sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats shows you how many times the index was used for user queries. It returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.
Box 2: sys.dm_db_missing_index_details
sys.dm_db_missing_index_details returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql
https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx
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.
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.
正解：
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 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.
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.
正解：
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.
You are planning two stored procedures named SProc1 and SProc2.
You identify the following requirements:
SProc1 must return a table.
SProc2 must return a scalar value.
You need to identify which option must be implemented for each stored procedure to return the desired data.
Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)
You identify the following requirements:
SProc1 must return a table.
SProc2 must return a scalar value.
You need to identify which option must be implemented for each stored procedure to return the desired data.
Which options should you identify?
To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)
正解：
Explanation:
Note:
* a table (a set of rows) can be returned through a SELECT statement
*a scalar can be returned through an output parameter.
* incorrect: TVP is used for input not output.
You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
正解：C
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)