January 31, 2005 at 11:11 am
Can anyone offer an educated opinion and/or actual experience on the behavior of these two methods, especially concerning speed? Do you believe a Table Valued function will outperform a temporary table used for the same basic purpose, or would there be any difference in performance? If so, would SQL Server 2005 perform any differently than SQL Server 2000 using the same senario?
January 31, 2005 at 11:40 am
>>Do you believe a Table Valued function will outperform a temporary table used for the same basic purpose,
Unfortunately the answer is "it depends".
Depends on table size and how it is being used. eg If you have a large temp table and are joining it to other tables, you may find that an index on the temp table really improves the performance. This is a scenario where the temp table would perform better because you can't index a table variable but you can index a temp table.
January 31, 2005 at 11:52 am
January 31, 2005 at 3:35 pm
More good info:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=157332&p=2
January 31, 2005 at 4:16 pm
February 1, 2005 at 7:58 am
Contrary to a statement above, you can declare a PK or Unique constraint - which creates an index - on a table variable.
Table vars can be as good or slower than temp tables.
IMHO: Table valued functions: if you're only going to call it once, and it returns a relatively small set, they're OK. If you're going to use the results more than once, dump them into a @table of #table.
P
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply