BaldingLoopMan (12/9/2009)
All very useful info guys. Once again it appears the geniuses at sql server have designed a new way to do the same thing but w/ different syntax. Sure there are some minor performance gains as usual. Sorry for the disgruntled attitude. The gators lost, I just got in, and haven’t finished my coffee. Also I just ate about 2 lbs of bacon at an all u can eat breakfast buffet. Bacon is my weakness and I’m paying for it now.Personally i develop using the #tables then convert them to @tables where needed when moving my code to prod. I like the #tables in development because i can see what is in them after the process runs and so on. It's essential to the dev process.
Perhaps i will start to integrate these CTE's going forward and be a good boy.
You can see what is in a CTE as well, just SELECT * from it. I'm actually intending to write an article on temp tables and how they're not evil... but they're not always applicable either. Derived tables / CTE's can provide a marked performance increase over temp tables / table variables *in some cases*. Every situation is different, and CTE's (or derived tables) definitely have their place.