SQL Server, Creating a View

Question

Post reply

SQL Server, Creating a View

Viewing 11 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

washingtonen Old Hand Points: 380 More actions · Answer 1

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?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

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]

washingtonen Old Hand Points: 380 More actions · Answer 3

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

washingtonen Old Hand Points: 380 More actions · Answer 5

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?

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 6

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/

washingtonen Old Hand Points: 380 More actions · Answer 7

washingtonen

Old Hand

Points: 380

December 14, 2008 at 11:42 am

#912236

You guys are THE BEST

THANKS!

washingtonen Old Hand Points: 380 More actions · Answer 8

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

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]

SQL Server, Creating a View

Cookies on SQLServerCentral