December 13, 2008 at 7:21 pm
Can a table-value function be included in a view?
If so, how do I proceed? Have no idea of how to create it.
Can you help me with it?
December 13, 2008 at 7:24 pm
A table-valued function can be used just like a View. And yes, that includes being called from a View.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 13, 2008 at 7:28 pm
how do I create one based on what I want to do?
sorry, have no idea of where/how to do it or to start.
December 13, 2008 at 9:50 pm
here is a start:
CREATE FUNCTION dbo.fnDim_Concept()
RETURNS
@tblVar TABLE
(col1 int,
col2 int)
AS
BEGIN
if (select failed from dimstatus) = 'N'
INSERT Into @tblVar select * from dim_region_mgr
else
INSERT Into @tblVar select * from vw_dim_region_mgr
RETURN
END
[/CODE]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 14, 2008 at 6:34 am
wow Barry, thanks a million!!!! your help is highly appreciated. I will try it and get back to you. Now, how do I call a function from a view, a Select or what?
December 14, 2008 at 10:04 am
washingtonen (12/14/2008)
wow Barry, thanks a million!!!! your help is highly appreciated. I will try it and get back to you. Now, how do I call a function from a view, a Select or what?
I don't see a need to call the function from within a view. The function Barry provided or the view with the union all I suggested should both give you your desired result with roughly equal performance.
But, if you want to do it, try something like:
create view vdim_concept
as
select *
from dbo.fnDim_Concept()
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 14, 2008 at 11:42 am
You guys are THE BEST
THANKS!
December 14, 2008 at 11:51 am
And BTW, the Function worked like a CHAMP.
If condition = N then the data is retrieved in 5 seconds else it take about 5 minutes, and that's exactly what I was expecting.
Again, Thanks.
December 14, 2008 at 11:56 am
A table-valued function is used just like you would a table or a view:
Select * from dbo.fnDim_Concept()
You can use the function reference anywhere in a query that you would put a View or a table name.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 14, 2008 at 11:57 am
Oops, I missed these replies before my answer above...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 14, 2008 at 12:01 pm
washingtonen (12/14/2008)
And BTW, the Function worked like a CHAMP.If condition = N then the data is retrieved in 5 seconds else it take about 5 minutes, and that's exactly what I was expecting.
Again, Thanks.
You should note that this it the exception, not the rule. And it probably only happens in this case because the optimizer is doing something wrong with your Union-based View. Normally a (multi-statement) function is slower than a View that returns the same results.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy