Luis Cazares (11/21/2014)
Damian-167372 (11/21/2014)
Luis Cazares (11/21/2014)
Damian-167372 (11/21/2014)
yes your absolutely right I agree table variable is a overhead for this query,
but for any other large queries I can use this method slightly change to a temp table instead of Table variable then i think its ok right?
No, it's not. You're still using a loop when you shouldn't. Loops/cursors have their places and you posted a good example of them. A while loop shouldn't replace a cursor that's already working fine.
I guess you didn't read my post i said "for any other large queries" that means for a cursor that loads lots of data, then this approch should be ok?
Yes, I read it. Let me explain this again. If it's a great load for a cursor, then it would be a great load for a loop. You need to change the logic, stop thinking on what to do with each row and start thinking on what you want to do with the columns. Read the following articles: The "Numbers" or "Tally" Table: What it is and how it replaces a loop By Jeff Moden[/url]
There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction By RBarry Young[/url]
There Must Be 15 Ways To Lose Your Cursors… Part 2 By RBarry Young[/url]
fwiw sometimes I replace a cursor with a while loop, not because the loop is any faster, but just because it can be less code to write and look at.
that said, you have to be careful how you write the while loop to avoid O(N^2) runtimes.
Gerald Britton, Pluralsight courses