SQL Server offers a number of ways to get at your data. You don’t explicitly choose – it has options, and it picks the most applicable data access method for a query and the structure of the data. I think our state of the art knowledge of cognitive science doesn’t understand the specifics, and I was thinking about how this works as an analogy.
- Table scan – An entire table is read from disc, slow by slow, and evaluated against the query request. Ignoring the downsides, this is the most broadly applicable method in the toolkit. Any query could be satisfied with table scans.
- Index scan – Generally a range scan. In a customers table, this would apply to a query for all customers whose last name begins with a C. An index is a b-tree, so the server will traverse it, reading only the necessary parts, get the locations for the physical data rows, and read them, and only them, from disc. Obviously this is preferable, but not always possible.
- Index seek – Usually a single, particular row is demanded, and it’s the only row pulled off the disc. When this happens against a clustered index, you the best performance possible.
It’ll use a mix; if you ask for two predicates (state == ‘ca’ && last_name == ‘kaku‘) with an and, it will perform the most limiting first, then verify only that set against the most permissive. That’s the most economical solution available.
The English language doesn’t have a word for schadenfreude. Usually when somebody says language X doesn’t have a word for Y, you won’t normally lose money betting against them. The implication is that people who use that language don’t understand the concept being that word, as if Americans couldn’t comprehend the sister of your girlfriend. But what if I said English doesn’t have a word for the sister of your father? You would say “That sounds like your aunt.” And you’d have the answer pretty quickly – obviously you’re “hitting an index” and only even considering a fraction of the words available that might mean that.
Could you do something like a table scan of/in the mind, if you wanted to? Say you want to list all the words you know, or even the names of all the people you know? I don’t think that’s possible. It’s not because we forget – this stuff comes back instantly, in context. It just doesn’t seem to be organized to be available out of context.