I ran into a situation with two options:
Use a single query with a cost of ~0.07
INSERT INTO @vals SELECT ... FROM ... JOIN ... JOIN ... WHERE a.col = 1 OR b.col IS NOT NULL
Use two queries to get the same result -- the first cost ~0.05, the second cost ~0.3
INSERT INTO @temp SELECT ... FROM ... JOIN ... INSERT INTO @vals SELECT ... FROM @temp JOIN ... WHERE a.col = 1 UNION ALL SELECT ... FROM @temp JOIN ... JOIN ... WHERE b.col IS NOT NULL
I chose the first option - single query, so less concern of mutating data between queries and less overall cost. Was this the prudent choice?
This was for an operation that originally took ~5+ minutes, timing out in our application. The re-write got that down to 1.5 minutes consistently, using table variables with unique clustered indexes.