Prev: Always rounding up
Next: Returning status/error codes as well as resultsets from stored procedure.
From: tshad on 24 Mar 2010 18:29
We have a procedure that is calling a function as a table

Select * from fnTerm (@Term,1)

********************************
ALTER FUNCTION [dbo].[fnTerm]
(@Id int,
@ByList BIT)
RETURNS TABLE
AS
RETURN
SELECT PId, TId, CategoryType,Name
FROM Term

UNION

SELECT PId, TId, CategoryType,Name
FROM Term_Alias
***************************************

The function actually does 4 unions.

Is there some type of cost in using a function versus a View?

Thanks,

Tom


From: Plamen Ratchev on 24 Mar 2010 18:37
Inline table-valued functions are pretty much like views and there should be not much difference. But if you use
parameters then you cannot use a view because views do not have parameters.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 24 Mar 2010 18:41
Also, I found that the function returns over 10,000 rows.

If my statement is:

SELECT *
FROM fnTerm(@Term,1) T
JOIN Warehouse w on w.TId = t.Tid
Where @UserID = T.UserID

Is this going to pass back all 10,000 rows then do the Join then do the
Where filter?

If I had a Derived Table in place of the function, would it do better
filtering or would it be a wash?

Thanks,

Tom


"tshad" <t(a)dslextreme.com> wrote in message
news:e1pisF6yKHA.5360(a)TK2MSFTNGP06.phx.gbl...
> We have a procedure that is calling a function as a table
>
> Select * from fnTerm (@Term,1)
>
> ********************************
> ALTER FUNCTION [dbo].[fnTerm]
> (@Id int,
> @ByList BIT)
> RETURNS TABLE
> AS
> RETURN
> SELECT PId, TId, CategoryType,Name
> FROM Term
>
> UNION
>
> SELECT PId, TId, CategoryType,Name
> FROM Term_Alias
> ***************************************
>
> The function actually does 4 unions.
>
> Is there some type of cost in using a function versus a View?
>
> Thanks,
>
> Tom
>


From: tshad on 24 Mar 2010 18:46
I realized that after I sent that out.

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:8KOdnXBOF9-hCTfWnZ2dnUVZ_tYAAAAA(a)speakeasy.net...
> Inline table-valued functions are pretty much like views and there should
> be not much difference. But if you use parameters then you cannot use a
> view because views do not have parameters.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 24 Mar 2010 21:43
Take a look at the execution plan. In general using a view, derived table, or inline table-valued function should not
make much difference as they get expanded in the query plan.

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Always rounding up
Next: Returning status/error codes as well as resultsets from stored procedure.