In my experience, larger queries give SQL Server more opportunities to get it wrong. Using smaller queries can help SQL Server use a good query plan. For example, in your development environment, SQL Server might perform a huge query exactly in the order you wrote it. Later, when the code migrates to production, suddenly the data is much different. SQL Server might make a wrong assumption and choose to start the query in the middle, and suddenly you end up with crazy joins that create a billion temporary records that get spooled to tempdb.
Performance of complicated queries against huge databases varies. Sometimes, having a complicated query performs better, because it processes the data only once. Sometimes, separating steps is both faster and more readable, because you can ensure the results of the first query limit the data to a small set of rows before performing the rest of the steps.
I try to use separate queries for separate logical steps. I'll document each step with comments so I can more easily remember what it was trying to accomplish. For example:
- Do some pre-processing to figure out what the user wants
- Gather the data, filtering to the user's selection
- Fill in some date-based data from some pricing table
- Drop a few records that are not relevant because of some rare condition
- Fill in some fields for reporting purposes
- Return the results
If you are joining to multiple levels of sub-queries, as in Bill's example, then breaking apart the sub-queries into separate steps can improve performance.
Also note that your plan may incur more I/O as you create, populate, index, and select from temporary tables.
And finally, if it ain't broke-- don't fix it!