August 23, 2012 at 3:43 pm
[font="Comic Sans MS"]hi ,
plz when and how can we use a table valued function ?
thank you:-)
August 23, 2012 at 3:46 pm
It depends. You use them when they are appropriate. One use for them is as a parameterized view.
August 23, 2012 at 3:52 pm
I'm a biginner,
Can you please point me to a simple example
Thanks for your response
August 23, 2012 at 4:33 pm
Honestly, if you're a beginner maybe stay away from table-valued functions. They can be performance nightmares used wrong.
They can be used anywhere a table can be used, whether it's a good idea to use them or not is a whole nothing matter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2012 at 5:37 pm
A TVF is used If you want a table as an output of a function. The results can be used in a stored procedure.
Syntax:
CREATE FUNCTION [dbo].[functionname]
(
@inputvraiables Datatype
)
RETURNS @tablevariablename table
(
Table columns
)
AS
BEGIN
whatever result data set you want the table variable to hold(Basically select stmts)
RETURN
END
As told above this can affect performance on a larger scale.
Hope this helps,
Vihar
August 23, 2012 at 5:42 pm
Thanks everyone,
This is perfect
I'm sure my dba will not let me put any queries in production without good testing:-)
regards
August 23, 2012 at 6:01 pm
Nullified (8/23/2012)
A TVF is used If you want a table as an output of a function. The results can be used in a stored procedure.Syntax:
CREATE FUNCTION [dbo].[functionname]
(
@inputvraiables Datatype
)
RETURNS @tablevariablename table
(
Table columns
)
AS
BEGIN
whatever result data set you want the table variable to hold(Basically select stmts)
RETURN
END
As told above this can affect performance on a larger scale.
Hope this helps,
Vihar
This is just one way to write a tvf. You can also write them as an inline tvf and they are usually more performant. Best bet is to read about them in Books Online.
August 24, 2012 at 1:50 am
Nullified (8/23/2012)
CREATE FUNCTION [dbo].[functionname](
@inputvraiables Datatype
)
RETURNS @tablevariablename table
(
Table columns
)
AS
BEGIN
whatever result data set you want the table variable to hold(Basically select stmts)
RETURN
END
With that form (which is the multi-statement) you could have any statements at all there, the minimum you'd need is an insert (not a select) into the table variable.
Also note that the form showed there is the inefficient form of a TVF, best avoided most of the time
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply