function to return table variable

my question has to do with the performance of user-defined function that
returns a table variable vs. traditional temp tables. i have a function
that consists of a select statement that populates and returns a table
variable. the select is fairly complex and takes a siginificant amount of
time to run mainly due to the size of the tables being queried. the
resultset, is not that large (usually around 1000 rows in 10 columns). the
procedure that uses the data in the table variable makes reference to this
table variable in more than one location using something like:
select * from dbo.fTblVar(param1,param2,param3)
does this mean that each time the calling procedure makes reference to the
function, the complex (and long-running) select statement will be executed?
if this is the case, it seems that i would be better off using a traditional
temp table that is created and populated once, then acted on as needed. or
does this so somehow get cached sql server's memory for the duration of the
procedure? what if the parameter values change?Well if you call the UDF every time, then yes, it willrun every time... But
if the contents are not different then just run the UDF Once, outside your
query, and dump the valeusinto a local table variable, and use that table
variable in your query, then it won;t be running everytime
Declare @.T Table (<column Defintions> )
Insert @.T
select * from dbo.fTblVar(param1,param2,param3)
-- Now the @.T variable can be used throughout the rest of your SP Exactly
like a temp table would be used...
Which is better depends on how much data is in it, and what you need to do
with it. You can't put additional indexes (Other than Primary Key COnstrain
t
Index) on table variables, so if you need to really manipulate the data in
the table, a temp table is more flexible, but if all you need is a temporar
y
list of keys, say, for joining in another query, then table variables are
100% in memory, and should be much faster. If you use them, however, keep
them narrow.
"JT" wrote:

> my question has to do with the performance of user-defined function that
> returns a table variable vs. traditional temp tables. i have a function
> that consists of a select statement that populates and returns a table
> variable. the select is fairly complex and takes a siginificant amount of
> time to run mainly due to the size of the tables being queried. the
> resultset, is not that large (usually around 1000 rows in 10 columns). th
e
> procedure that uses the data in the table variable makes reference to this
> table variable in more than one location using something like:
> select * from dbo.fTblVar(param1,param2,param3)
> does this mean that each time the calling procedure makes reference to the
> function, the complex (and long-running) select statement will be executed
?
> if this is the case, it seems that i would be better off using a tradition
al
> temp table that is created and populated once, then acted on as needed. o
r
> does this so somehow get cached sql server's memory for the duration of th
e
> procedure? what if the parameter values change?
>
>