Tag Archives: Indexes

Dataverse Web API Tip #4: Alternate Identities (Alternate Keys)

, ,

Out of the box with Microsoft Dataverse, a single table row may be referenced using pluralized-table-name(primary-key) syntax, like:

GET {{webApiUrl}}stores(78c91f30-0e86-4716-8718-176c14174cab)
(include the "always include" headers)

However, there are times where you know that rows in a table may also be uniquely identified using another column’s value (or maybe even a unique set of values across several columns). For example, imagine a table describing your company’s stores. In the technical domain, Dataverse identifies each row using an auto-assigned GUID primary key—but to the business, each store is identified by a numerical store number stored in a StoreId column, like StoreId = 1 or StoreId = 2.

You can formally recognize the presence of an alternate unique identifier by declaring the relevant column (or columns) as an alternate key.

Defining an alternate key brings with it several advantages, including:

  • It’s uniqueness will be enforced. The system will not allow multiple rows to have the same key value (so, in our example, if StoreId is declared as an alternate key, two records with StoreId = 1 won’t be allowed).
  • The ability to reference rows using the alternate key (more on this below).
  • Optimized lookup performance.
Continue reading

T-SQL Ordering Myth #1: Clustered Indexes Determine Default Sort Order

,

If I query a table that has a clustered index without specifying an ORDER BY clause, the resultset will be sorted according to the clustered index” may sound reasonable. After all, since “a clustered index determines the physical order of data in a table” (per TechNet), isn’t it logical to assume that a query with no ORDER BY clause returns data sorted this way? Continue reading