Exam DP-300 topic 4 question 21 discussion

For me all the three are NO: 1.If you force that query plan, the behaviour will be always the same, so you will not reduce or increase the I/O compared with what we see in the image. Only in the case you see in the chart another and worst query plan used by the query, choosing this one will reduce the average I/O. 2.You can create another index in the table but if using it is worse than using the current one, the sql engine will not choose it in the plan. 3.PK is the clustered index and it has all columns by definition.

Answers are No, No and Yes. 1) force plan - there is only one execution plan. Forcing it will not change the run time. 2) No - adding another index can incur maintenance overhead, but it is not going to INCREASE the IO. If it did the optimizer would use the plan it already has. 3) Yes - Agreed with whomever said the extra columns should not be added to the primary key, but design is not the question. Question is will it make the plan more efficient. Yes it will. Currently we have a lookup. Adding those columns will make the index a covering index for this query and eliminate the lookup.

No, Yes, No. - Issue can be resolved by modifying the existing non-clustered index making it a covering index, or creating a new non-clustered covering index. Making the clustered index a covering index does not make any sense.

1. NO - self explanatory, only one plan present. 2. NO - query stats will improve if you create an index (you will see a decrease in the IO for the query). 3. NO - the question mentions PK_, while the print screen shows IX_.

3rd point - interesting one, if you add these 3 columns to the Clustered Index (and you can do it, columns in clustered index are just changing the physical sorting order of all rows) execution plan likely will change to Clustered Index Scan. Estimated Number of rows is 1, Clustered Scan and Key Lookup, in that case, will be pretty much the same, because in both cases plan is looking for SalesPersonID first in a Clustered index and then scan all the filtered pages for needed data. I think the answer is NO, there will be no improvement in I/O.

PK_....does that mean it's a primary key

BTW, Provided Answer is correct.

My take on this: - No, there is only 1 query plan makes no sense to force it in the current context - Yes, by creating a non-clustered index that includes the column in the output list would yield better results - No, you can not *include* columns in a PK only add columns to it, and it never makes sense to add this kind of columns to PK which is used for integrity reasons and not for querying reasons and perf.

The provided answer is correct. the item #1 and Item #2 are general topics and the item #3, check execution plan, The execution plan already complains for those keys. so we need to add within the PK to reduce execution cost!

No Forcing this plan will not reduce I/O or query execution time, as this is the only plan available. No A new index with 3 columns SalesPersonId (sole key column), subtotal & totaldue would reduce I/O and query execution time, as there would be fewer rows per 8k block, compared to using the shown nonclustered index + lookups to the clustered index. No Makes no sense, as can't add same columns to a clustered index (which always includes all table columns).

For third, "include" may mean to ADD those additional columns into primary key as covering index, not using INCLUDE clause.

3rd option is NO. This is a clustered index. It would only be true if you created a non-clustered index.

@Phund...This pattern in an execution plan (lookup - either RID or straight lookup) means that there is no index that completely covers the query. For the purpose of this question if a covering index were created it would improve performance.

1.No: Just have only one Execution Plan, so force have no other plan to be forced 2.No: Add index increase IO at maintain time and creation time not at query time 3.No: Cluster Index have got all columns included

so, what is the answer for this?

Answers are correct as they make sense to the given conditions and questions.

N. 1 NO: we have only a plan, doesn't make sense to force it N.2 NO: we already have e NCI on the table N.3 YES: this way you'll avoid the keylookup