Home » Microsoft » MB6-886 » Which action should you take?
You are a database developer on an instance of SQL Server. You have a table named ProjectDetails that is partitioned by the ProjectDate column.
You have several queries that are performing poorly. To improve performance, you want to create a table-aligned index on the ProjectDetails table.
Which action should you take?
A. Create a partition function on the same data type as the partition function used by the ProjectDetails table. Create an index using the new partition function.
B. Create a filtered index on the ProjectDetails table.
C. Create an index using the same partition scheme and key as the ProjectDetails table.
D. Create an index on the ProjectDate column and rebuild the ProjectDetails table.
Correct Answer: C
Explanation/Reference:
To create an index that will be aligned with the table, you must specify the same partition scheme and key that was used when the table was created. For example, in this scenario, if you specified the ProjectDetails_PS partition scheme when creating the ProjectDetails table, you might use the following statement to create a table-aligned index:
CREATE UNIQUE CLUSTERED INDEX IX_ProjectDetails ON ProjectDetails(ProjectDate)ON ProjectDetails_PS (ProjectDate);
You should not create a partition function on the same data type as the partition function used by the ProjectDetails table and create an index using the new partition function. There is no need to create an additional partition function in this scenario.
Note: An index does not have to participate in the same named partition function to be aligned with its base table. However, the partition function of the index and the base table must be essentially the same, in that 1) the arguments of the partition functions have the same data type, 2) they define the same number of partitions, and 3) they define the same boundary values for partitions.
You should not create a filtered index. A filtered index is a nonclustered index that is defined including a specific WHERE clause to optimize the index for specific queries.
You should not create an index on the ProjectDate column and rebuild the ProjectDetails table. There is no need to rebuild the ProjectDetails table. In addition, to create an index that is aligned with the table, you must specify the same partition scheme used with the ProjectDetails table when creating the index.
