Complex Select Query Vs Creating a Temp Table and then inserting values in temp table and then selecting it

Question

I was just wondering which approach is better.

1. Creating a complex select query with many CASE WHEN THEN ... ELSE ... END statements or sub queries in select clause.

2. Create a temp table and then insert and update it. Finally select * from @tempTable

This question is only for complex queries not for simple joins or just when some small info has to be picked from other table.

Answer 1

For ease and speed of development, 2 in my experience.

For performance - it depends on the number of rows and tables you're joining and retrieving back. In memory temporary tables (as it looks like you're suggesting) have an overhead, which is obviously the memory they're using.

I'd stick to number 2 as mentioned, unless you're working with Walmart sized data then worry about performance if it becomes a problem - "Hardware is cheap, writing software is expensive"