Interpretation of BoL - table variables
-
It was my understanding that Table variables were more efficient for a smaller set of data. By using the Table variable you would not be writing to your temp log and would not incurr the overhead that causes. The problem with Variable tables is when you get into a bigger dataset. They become much slower and more cumbersome. Just wanted to add my two cents.
-
Frank, would you mind elaborating on your commment about temp tables? I was under the impression that table variables typically incurred less server cost during use.
Or, are they typically just prefered because they are dropped at the batch level instead of the connection level?
Ah, that will be a bit difficult. I don't have a subscribtion to SQL Server Mag anymore. Some time ago there were several articles there, stating that. IIRC, Brian Moran did one of this. If you have a subscription, you might want to search there.
I'm also pretty sure I have read something in that direction on TechNet. However, I must dig to find that link.
For now, I'll second Todd's 2 cents.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Disadvantages of #temp tables:
Logged in tempdb log
Cannot be created inside UDFs, therefore only @Table varaibles can be used to return non-scalar values from UDFs
Intermixing of DDL and DML statements in stored procedures forces procedure recompiles. (http://support.microsoft.com/default.aspx/kb/243586)
Disadvantages of @Table variables:
Can't be released early, have to wait until they go out of scope
Can't be indexed or truncated
Can't be passed as parameters to Procs/UDFs
-
Looks like I don't need to search TechNet anymore. guess I had better scrolled up that thread *before* replying. Thanks for the links! This time I'll keep them.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
David -
Yes, there is a large difference between commercials in Europe and the States. They are our last gasp of Puritan ethics. The French side of my family was amused and slightly confused with the Janet Jackson nipple thing. "But it was a nipple. Have you not seen nipples on TV before?" Just one of those little cultural difference things.
Yes, the shows are stretched beyond belief. I "watched" an 88 minute movie in 3.5 hours because there were commercials every 7 minutes. Commercials that lasted five minutes each time. You just have to love cable programming.
Quand on parle du loup, on en voit la queue
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply