Nested queries or temp (#) tables in SQL Server I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' I have used heavily nested queries including the aggregations, however I have just been reading here about optimising large complex queries by breaking them down into temp (#) tables.  This seems to conflict with a lot of literature suggesting that using temp tables is not the best practice.

The web app itself is to be used by quite a few people, so ideally I wouldn't want the temp table, if they need indexing, to conflict with each other.

What is the best way forward here for query optimisation, and best practice for a widely used web app?

Thanks in advance.

CM
The SQLCat team ideas and articles are sometimes pitched at edge cases that most folk will never see given their load or database size.

Saying that, the ideas they give there are common techniques to improve performance (eg change OR to UNION)

Temp tables have some issues around stored procedure compilation etc, but don't confuse these with table variables (the SQLCat articles mentions this). Also, temp tables should be local not global to separate processes don't affect each other 

Personally, I use temp tables quite often to break queries down: but not all the time. If I can do it in one SQL statement that runs well enough (for it's frequency of use) then I'll use that.

In your case, I'd identify a few problem queries and see if using temp tables suits these better. If you can't see any problem queries then do nothing.