Jeff provides half a dozen prompts to get the ball rolling, and I thought I’d discuss my thoughts on “the whole family. #temp vs. table variables vs. CTEs vs. indexed views. When does each one earn a spot?”
Indexed views are an odd man out. As far as table variables go, there are two places where I really like to use them. First is for logging, because they survive a transaction rollback, so even if you roll back your transaction in a catch block, you can still insert that vital error information into a logging table. Second is for table-valued parameters in stored procedures, though that does require a user-defined table type versus an ad hoc table variable.