Exam DP-420 topic 4 question 1 discussion

Actual exam question from Microsoft's DP-420

Question #: 1
Topic #: 4

HOTSPOT -
You have the indexing policy shown in the following exhibit.

Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: ORDER BY c.name DESC, c.age DESC
Queries that have an ORDER BY clause with two or more properties require a composite index.
The following considerations are used when using composite indexes for queries with an ORDER BY clause with two or more properties:
✑ If the composite index paths do not match the sequence of the properties in the ORDER BY clause, then the composite index can't support the query.
✑ The order of composite index paths (ascending or descending) should also match the order in the ORDER BY clause.
✑ The composite index also supports an ORDER BY clause with the opposite order on all paths.
Box 2: At the same time as the item creation
Azure Cosmos DB supports two indexing modes:
✑ Consistent: The index is updated synchronously as you create, update or delete items. This means that the consistency of your read queries will be the consistency configured for the account.
✑ None: Indexing is disabled on the container.
Reference:
https://docs.microsoft.com/en-us/azure/cosmos-db/index-policy

by UNIFYSolutions at Aug. 6, 2023, 9:55 a.m.

Comments

Submit Cancel

[Removed]

Highly Voted 1 year, 3 months ago

1 - ORDER BY c.name DESC, c.age DESC "It's optional to specify the order. If not specified, the order is ascending." https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/how-to-manage-indexing-policy?tabs=dotnetv3%2Cpythonv3#composite-index-defined-for-name-asc-age-asc "The composite index also supports an ORDER BY clause with the opposite order on all paths." https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy#order-by-queries-on-multiple-properties 2 - at the same time as the item creation https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy#indexing-mode

upvoted 5 times

xRiot007

11 months ago

It seems that MS has updated their page. "If not specified, the order is ascending." has been replaced by "When creating a composite index to optimize queries with multiple filters, the ORDER of the composite index will have no impact on the results. This property is optional."

upvoted 1 times

...

Norvegec

Most Recent 1 year, 1 month ago

The index definition didn't include order for each property, so by default it's ASC. When querying it is used even in DESC direction as long as all props direction are matched. Also the order of props matter. Thus, only the "ORDER BY c.name DESC, c.name DESC" utilizes index.

upvoted 2 times

...

UNIFYSolutions

1 year, 3 months ago

The second one was easy - the index is updated on creation. The first one is potentially missing some context. name then age for ORDER BY, but I guess DESC is the same for both on the last option, and there is no ASC for both name and age, otherwise would have gone for that one.

upvoted 3 times

xRiot007

11 months ago

The first option (order by age ASC, name ASC) is not viable because for composite indexes order of the filter matters. So if the index is (name, age) that is the order in which you have to specify them in the filter

upvoted 1 times

...