I can agree with Daniel. I have used them and hadven't had any problems
with them at all and they are QUICK! With a lot less resources!

-----Original Message-----
From: Daniel McGarry
[mailto:mssqldba-ezmlmshield-x68482477.[Email address protected]
Sent: Friday, May 27, 2005 7:33 AM
To: LazyDBA Discussion
Subject: Re: table variable vs temp table?

The first instinct of any good DBA is to avoid Cursors at any and all
possible costs - especially if he or she is designing a query that will go
into production rather than a one-off use. That being said, sometimes they
are just unavoidable. However, there is a third option that may make your
life easier here. Depending on the nature of the initial query in question,
it may be possible to have more work done there than you realize.

I am a big fan of the SQL Magazine's author Itzik Ben-Gan and his principle
of using set operations over iterative approaches; he was the first to open
my eyes to the possibilities of correlated subqueries (a subquery that is
employed in an overall query just as any other set would be but it
references values in the "outer" query). A good article to get you started
in that regard is here:
http://www.windowsitpro.com/Article/ArticleID/8826/8826.html.

I have used correlated subqueries in both where and from clauses - anywhere
a set is permissible - and was amazed at both the speed and versitility for
solving problems that I had previously thought were only possible through
iteration. You also might want to chek out this article as well:
http://www.windowsitpro.com/Article/ArticleID/27051/27051.html. Both
articles are free, and should get you to start thinking beyond cursors and
into correlated subqueries. I have made the leap and I can tell you the
water is fine.


----- Original Message -----
From: "Hugh du Toit " <mssqldba-ezmlmshield-x97524742.[Email address
protected]
To: "LazyDBA Discussion" <[Email address protected]
Subject: table variable vs temp table?
Date: Fri, 27 May 2005 11:33:17 +0200

>
>
> Hi,
>
> I run a stored procedure and it returns a result of a couple thousand
> rows. I need to get these rows into a temp table or table variable,
> and then loop through the results. I need to run 1 or 2 queries for
> each rows in this result set. What way is the best to go?
>
> Table variable / temp table. Cursor / while loop. I want to pass the
> results to a different stored procedure to be processed like explained
above.
>
> What is the best practice?
>
> Thanks!
> Hugh
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website
> : http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




MS Sql Server LazyDBA home page