Are key lookups from non-clustered indexes always slower than a second query that does the lookup? I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. 

The first with the non-clustered index inserting only the key field into a temp table (so no key lookup is performed) and the second using that temp table to join back to the original table to filter it down on the key and then select the fields I need.

I'm typically querying tables with hundreds of millions to tens of billions of rows when I notice this. I'm not sure if it can be related to the fact that I'm eliminating the key lookup when the table is first loaded into memory and instead I'm inserting the key into a temp table so that the subsequent field lookup query occurs between two tables already in memory?

The difference in time I'll see is usually significant too, e.g. on the order of minutes.
I can think of a couple of cases where this approach might be beneficial.


Sometimes you can end up with an execution plan which does a load of lookups for rows that are then ultimately filtered out downstream (I've noticed this especially with pagination queries). If you only store the rows post filtering there will be fewer "manual lookups" to resolve. This scenario can generally be addressed with a self join and does not need intermediate materialisation.
Lookups always use nested loops. The "manual lookup" might use a different join type - the cardinality estimates for rows to lookup will be spot on when materialised and may differ from the original estimates encouraging this.


For the case where neither of the above apply (and you are just materialising into a temp table and getting the same number of nested loops lookups as you would have got without this step and not benefiting from improved cardinality estimates) I would expect this to generally be slower than the original query without the intermediate step (as on the face of it you are doing the same work with some additional overhead added) but haven't tested this.