Here's a little bit more from me:
1. We definitely have to do a better job showing when ordering / filtering is done only on part of the table vs the entire table. (It might not really be necessary to show anything extra when there is no ordering / filtering applied, because you are just browsing through the table, and yes, the list of records ends before the table ends, but you will see that when you scroll down to the end. Maybe the final record could be made more prominent, but that's about it. The real problem is with ordering / filtering where you can do it and get a result without noticing that it is partial. This is bad, agree completely.)
How specifically to indicate that ordering / filtering is done only on part of the table is secondary - there are multiple options, we'll choose something that would work for similar cases in other windows. But it has to be noticeable. We will try to do it.
2. The real issue, however, is what happens when, OK, you know that ordering / filtering is done only on part of the table, but you still want to do it on the entire table.
There is an obvious unsolvable case when the table does not have an index on the involved fields and has to be retrieved in full *and* when it is also so big that retrieving it in full will take hours (or days, or years - like with the table backing Bing imagery for the entire planet). But that unsolvable case aside, there are also cases where the involved fields do have indexes (not even required to be unique) and in those cases we can use those indexes to perform ordering / filtering without retrieving the table in full. And there are also cases where the involved fields do not have indexes, but the table is not exorbitantly large and can be retrieved in full in some significant but still acceptable time - say, in minutes instead of in hours.
We can do this: (a) if the table has an index on the involved fields, use that for ordering / filtering without asking anything, (b) if the table does not have an index on the involved fields, offer to fetch it in full, allow canceling the process. And, for good measure, (c) allow clicking the final record of the partially fetched table to fetch another 50,000 records, also with an ability to cancel the process.