If I create a Conditional Lookup field, it will by default use the Max operator to enforce only one result from the lookup. If I change the operator to Top without a Sort specified (give me the first record whatever it is, formally non-deterministic) , I get the same code as I would with the None operator.
While it seems the result of the None operator code enforces 1 result for the lookup (which is mandated by the SET statement, see: https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16#best-practices). I wonder if you could prove that the undetermined result from the INNER JOIN is the same record as would have been returned by a TOP(1). I suspect that it may not always be the same record.
If I specify a Sort, I see that the code implements a Top 1 explicitly.
What I would like is that a Top operator without a sort gives a warning on apply / Deploy that offers the user a choice:
- None operator (because this code will mostly be faster, but not always)
- explicit Top without Sort. This option will almost always be slower and may or may not return the same result as an INNER JOIN would, but will be explicitly what the user asked for.
- The option to specify a Sort: this may actually be what the user needed.
This choice could be influenced by the operators on other lookups in the table, as bundling them together is faster than running them serially.
See some code examples below. I have a list of breweries, which guarantees mutliple breweries for some US states. And I have a list of states with population per age. I am looking up an age from the state table to the brewery table based on the state name being equal. There are at least 87 records for each state ( age 0 - 85 plus 999 for people over 85 years of age).
Max operator:
Top without sort / None operator:
Top with sort:
