70-464 試験問題を無料オンラインアクセス

You have a table named ORDERS that contains 10,514,003 Orders. The ORDERS table has an IDENTITY(1,1) column named ORDER_ID. ORDER_ID is the UNIQUE CLUSTERED INDEX and PRIMARY KEY for the table. The first ORDER_ID is 1. There are no missing ORDER_IDs in the set.
Based on table usage patterns, you decide to use partitioning on this table based off of the ORDER_ID column.
You need to create the following partitions:

Which Transact-SQL statement should you run?

• A.CREATE PARTITION FUNCTION pfOrderIDRange (int)
  AS RANGE RIGHT FOR VALUES (7500000,10000000)
• B.CREATE PARTITION FUNCTION pfOrderIDRange (int)
  AS RANGE LEFT FOR VALUES (7500000,10000000)
• C.CREATE PARTITION FUNCTION pfOrderIDRange (int)
  AS RANGE LEFT FOR VALUES (0,7500000,10000000)
• D.CREATE PARTITION FUNCTION pfOrderIDRange (int)
  AS RANGE RIGHT FOR VALUES (0,7500000,10000000)

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:
Identify unused indexes.
Identify indexes that need to be defragmented.
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:
Note:
* sys.dm_db_index_usage_stats
Returns counts of different types of index operations and the time each type of operation was last performed.
* sys.dm_db_index_physical_stats
Returns size and fragmentation information for the data and indexes of the specified table or view.

You plan to deploy SQL Server 2012. You must create two tables named Table1 and Table2 that will have the following specifications:
Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
Table2 will contain a column named Column2 that is the product of two other columns in Table2.
Both Table1 and Table2 will contain more than 1 million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables. Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area.

正解：

Explanation:
http://msdn.microsoft.com/en-us/library/cc280604.aspx
http://msdn.microsoft.com/en-us/library/ms186241.aspx

You execute usp_TestSpeakers.
You discover that usp_SelectSpeakersByName uses inefficient execution plans.
You need to update usp_SelectSpeakersByName to ensure that the most efficient execution plan is used.
What should you add at line 30 of Procedures.sql?

• A.OPTION (OPTIMIZE FOR UNKNOWN)
• B.OPTION (FORCESEEK)
• C.OPTION (FORCESCAN)
• D.OPTION (OPTIMIZE FOR (@LastName= 'Anderson'))

You have a table named Table1. Table1 has 1 million rows.
Table1 has a columnstore index for a column named Column1.
You need to import data to Table1. The solution must minimize the amount of time it takes to import the data.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

正解：

1 - Create a table named Table2 by using the same schema as Table1.
2 - Partition Table1.
3 - Import the data to Table2.
4 - Create a columnstore index on Table2 for Column1.
5 - Switch Table2 toTable1.
Explanation:
Note:
* An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index.
Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries.
* Tables that have a columnstore index cannot be updated.
There are three ways to work around this problem.
A) To update a table with a columnstore index, drop the columnstore index, perform any required INSERT, DELETE, UPDATE, or MERGE operations, and then rebuild the columnstore index.
B) (applies in this scenario) Partition the table and switch partitions. For a bulk insert, insert data into a staging table, build a columnstore index on the staging table, and then switch the staging table into an empty partition. For other updates, switch a partition out of the main table into a staging table, disable or drop the columnstore index on the staging table, perform the update operations, rebuild or re-create the columnstore index on the staging table, and then switch the staging table back into the main table.
C) Place static data into a main table with a columnstore index, and put new data and recent data likely to change, into a separate table with the same schema that does not have a columnstore index.