141 reputation
4
bio website melpadden.wordpress.com
location Zurich, Switzerland
age 33
visits member for 2 years, 7 months
seen Aug 22 at 7:11

I'm an engineer (and sometime "architect", but I don't trust architects) focussing on .NET, python and functional languages in the realm of visualisation/statistical analysis, based in Zurich, Switzerland. Currently my main clients are Financial Services behemoths, but I also dabble in the kool kid stuff for fun and profit, and I batter a guitar in the name of song and merriment in the bars of Switzerland from time to time, when I'm not skiing, cycling or working on code. Mail me at mel dot padden at gmail dot com. I try to update my blog but usually, I'm far too busy doing real work.


Apr
12
awarded  Supporter
Mar
16
awarded  Teacher
Mar
16
answered Where to learn advanced SQL coding
Mar
14
comment What's the difference between a CTE and a Temp Table?
Ah I see. Withdrawn.
Mar
12
comment What's the difference between a CTE and a Temp Table?
It is of course madness to suggest that using a similar CTE again and again is faster than usign a temp table or TV. All - and ALL - I really want to push out is that I think TT and TV are generally over-used, and with a little thought you can often get away with not touching the temp tablespace at all, and not re-implementing the same logic.
Mar
12
comment What's the difference between a CTE and a Temp Table?
@Martin Smith, if you're applying "disingenuous" to my post, then you're assuming deceitful intent, which couldn't be further from the truth. I do however agree that using the moniker "Evil" was over the top. Basically, it's a reactionary statement against what I see in the code I have to deal with day-to-day, which basically assumes that there's no problem whatever in shoving loads of data into temp tables all the time. So, in that respect, I guess, I'm hoisted on my own petard - one overreaction against another overreaction.
Mar
12
awarded  Editor
Mar
12
revised What's the difference between a CTE and a Temp Table?
Spelling correction
Mar
8
answered SQL Query Against Previous SQL Query Results
Mar
8
comment What's the difference between a CTE and a Temp Table?
Also, there is no free ride; to create a materialised #temp table with the appropriate indexes takes CPU time. Of course, the read will run faster after you've laid the groundwork. That's the whole concept of indexing. So you have to take into account the steps to get to the point where you can use a materialised temp table with indexes when you're slathering ALWAYS around in block capitals. I never made my points as a catch-all solution; you seem insistent on painting them thusly and responding in kind.
Mar
8
comment What's the difference between a CTE and a Temp Table?
My position is that you should generally use the technique that requires the least from the CPU, to get the job done. Not just the largest feature set, "in case" you're going to need it. I for one have never had to use transactions on a temp table, and I find it hard to imagine a situation where that behaviour could not be factored out of the requirements. If it needs to be persisted after the transaction completes, store it. If you're worried about the transaction failing, then the table is no longer in scope.
Mar
8
comment What's the difference between a CTE and a Temp Table?
"No, you illustrated that using CTEs correctly is better than misusing #temp tables" I think pedantry in this is not productive. You're correct in the second half of that sentence, but my statement and yours are not mutually exclusive, so the first, curt "no" is combative and uneccessary. You're assuming that I sprinkle table vars around willy nilly. I don't. For the most part I think people throw stuff in a temp table OR a table var because they're not being rigorous enough, they just want to sub-select a view and be done with it.
Feb
20
comment What's the difference between a CTE and a Temp Table?
So, to sum up, my decision-making process goes like this: Use a CTE first, if it needs to be visible after the current scope then move to a table var, if I need a lot of records or if it needs to be visible outside the proc use a temp table. If I'm writing a UDF I can't use a temp table anyway.
Feb
20
comment What's the difference between a CTE and a Temp Table?
To answer the second part of your question, I'm perfectly happy to use both. I just have very little patience for the kind of fire-and-forget thinking that accompanies copious or cargo-cult use of one or the other solution. I don't want to make assumptions about which side you're on but you seem pretty hot on temp tables.
Feb
20
comment What's the difference between a CTE and a Temp Table?
I don't know what dictionary you pulled that definition from, but I used "disingenuous" in the sense of a misleading statement. The point is that to write, in bold type, on a public web page, that a CTE should never be used for performance is misleading, if not downright incorrect. I illustrated that CTEs are in fact staggeringly effective depending on the task. I know the discussion is really about temp tables. I just didn't want the OP to go off thinking "All CTE bad. Avoid CTE".
Feb
17
answered What's the difference between a CTE and a Temp Table?
Jan
19
answered Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
Jan
7
awarded  Autobiographer