What is difference between Inline Table-values Function and Multi-statement Table-vlued Function

Question

What is difference between Inline Table-values Function and Multi-statement Table-vlued Function

Pravin Patel-491467

Say Hey Kid

Points: 674
More actions
October 15, 2008 at 7:16 am

#99318

What is difference bwtween Inline Table-values Function and Multi-statement Table-vlued Function?
My function result is fix. it is only one row with 5 column.
I need it frequently.
which function should i use in above situation. which function use less memory?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

An inline function only allows a single select statement as the function body. When run, the optimiser will treat it like a subquery.

multi-statement functions allow a number of statements inside, allowing more complex processing. However they tend to perform very badly.

See - http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

It really depends on how you're going to use the information. Since it's only a single row returned, probably, you won't have issues with either approach. Usually, the inline is recommended over the multi-statement table valued function. I wrote up some tests and posted them here[/url]. Gail posted an even better discussion and example here[/url]. And there was a discussion around all this just recently here at SQL Server Central.

Hopefully one, or all, of those will help.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Oops. There I am typing up the response and looking for your URL and there you are posting it. Ah well. Reinforces the point.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Grant Fritchey (10/15/2008)
Oops. There I am typing up the response and looking for your URL and there you are posting it. Ah well. Reinforces the point.

😀

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ck9663 Right there with Babe Points: 763 More actions · Answer 5

If the output is fixed and you don't need to pass any parameter, consider VIEWS.

-- CK

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Seems to be continued here: http://qa.sqlservercentral.com/Forums/Topic588073-146-1.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 7

Thanks for the valueable reply.

My requirement is to call this function approximately 2000 times in a hour from front end system, will it serve purpose, or still i need look at something else? (This function has simple Select statement fetched values from table having data not more than 1000 rows)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Pravin Patel (10/18/2008)
Thanks for the valueable reply.
My requirement is to call this function approximately 2000 times in a hour from front end system, will it serve purpose, or still i need look at something else? (This function has simple Select statement fetched values from table having data not more than 1000 rows)

Then, it should probably not be a function at all. It should probably be a stored proc or perhaps a view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Jeff Moden (10/18/2008)
Then, it should probably not be a function at all. It should probably be a stored proc or perhaps a view.

Probably.

I think at this point we need to see either code for this function, or detailed requirements, as right now we're just going round in circles getting nowhere.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass