Function or procedure?

Question

Function or procedure?

leonardo_gt

Old Hand

Points: 382
More actions
June 30, 2010 at 6:11 am

#387245

I have the following questions,
To obtain a single value as a result of an operation, which is more convenient to use: a function or procedure? in terms of performance are the same?
Thank you very much for your comments

Viewing 15 posts - 1 through 15 (of 20 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Function can mean Scalar Function (slowest), Multistatement Table-valued Function (slow) or Inline Table-Valued Function (quite fast).

Functions don't have predetermined cached query plans, stored procedures do.

I think the real tie-breaker is the ability to use Functions in queries, allowing code incapsulation and reuse. The APPLY operator allows the use of Table-Valued Functions in queries.

It really depends on what you're after.

-- Gianluca Sartori

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

I'm in agreement with Gianluca.

Having said that - as a rule of thumbs - I generally favor functions when the piece of code is expected to be reused and can be written in about 10 to 20 lines of code.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

leonardo_gt (6/30/2010)
To obtain a single value as a result of an operation...

That's normally the end of any chance at performance. Stop thinking about what you want to do to a row and starting thinking about what you want to do to a column. Scalar functions and the like are some of the worst forms of RBAR because they look set based (no explicit loops) but actually aren't.

--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)

Paul White SSC Guru Points: 150468 More actions · Answer 4

Gianluca Sartori (6/30/2010)
Functions don't have predetermined cached query plans, stored procedures do.

That's a common misconception, which I think goes back to the days when SQL Server had a 'procedure cache' rather than the 'plan cache' it has now. (Prior to SQL Server 7, query plans for adhoc queries were never cached). Currently supported versions of SQL Server are able to cache almost anything.

In fact, scalar functions and multi-statement table-valued functions are cached almost identically to stored procedures (as the dynamic management views like sys.dm_exec_cached_plans will show you). Scalar functions can even be used with EXECUTE (and the WITH RECOMPILE option!)

In-line table-valued functions are treated almost exactly like views, so although a plan is cached, it will only be matched and re-used if the exact same query (with the same parameters) were submitted.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 5

leonardo_gt (6/30/2010)
...which is more convenient to use: a function or procedure? in terms of performance are the same?

In very general terms, it is often possible to write a stored procedure as a function, and vice-versa. If the two versions do exactly the same thing, there is no performance difference worth talking about - the mechanics of both are so very nearly identical.

Whether you choose to implement something as a function or stored procedure is usually decided by other factors, as each is intended for different uses, and has its own set of restrictions and abilities.

There is a huge amount of information on both in Books Online. One page worth reading is:

Rewriting Stored Procedures as Functions

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Paul White NZ (7/1/2010)
In fact, scalar functions and multi-statement table-valued functions are cached almost identically to stored procedures (as the dynamic management views like sys.dm_exec_cached_plans will show you). Scalar functions can even be used with EXECUTE (and the WITH RECOMPILE option!)

Paul, there's something to learn from you every day! 🙂

I ran this query to check for cached plans:

SELECT c.objectid, d.name, d.type, text

FROM sys.dm_exec_query_stats AS a

INNER JOIN sys.dm_exec_cached_plans AS b

ON a.plan_handle = b.plan_handle

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS c

INNER JOIN sys.objects AS d

ON d.object_id = c.objectid

WHERE d.type IN ('IF','TF','FN')

Only inline table-valued functions are not listed. I thought that also multistatement and scalar function behaved in the same way.

Thank you for the information.

-- Gianluca Sartori

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

An odd fact about inline table valued functions is that you can run inserts, updates, and deletes against them. They really are treated like parameterized views.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

leonardo_gt Old Hand Points: 382 More actions · Answer 8

Thank you all for your comments.

RBAR first time I've heard, read later that article, because I find it interesting.

Greetings...

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

The Dixie Flatline (7/1/2010)
An odd fact about inline table valued functions is that you can run inserts, updates, and deletes against them. They really are treated like parameterized views.

Good tip. Make sense that you can , never tried it.

jorgeclam12 (7/2/2010)
i think for single value retrieval curser are better solution

Cursor are, generally , the most poorly performing piece of functionality available.

Clear Sky SQL
My Blog[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

jorgeclam12 (7/2/2010)
i think for single value retrieval curser are better solution

I guess you can do even better if you try harder:

Create UDF which will call stored procedure which retrieves the value from table using a cursor (make sure it is GLOBAL and DYNAMIC)!

:w00t::-D:w00t:

You can't call sp from UDF? 🙁 Damp it 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 11

Dave Ballantyne (7/2/2010)
The Dixie Flatline (7/1/2010)
An odd fact about inline table valued functions is that you can run inserts, updates, and deletes against them. They really are treated like parameterized views.
Good tip. Make sense that you can , never tried it.

Same deal as being able to directly INSERT, UPDATE, or DELETE via a CTE or derived table...

Dave Ballantyne (7/2/2010)
jorgeclam12 (7/2/2010)
i think for single value retrieval curser are better solution
Cursor are, generally , the most poorly performing piece of functionality available.

Reported as spam - assuming no-one here is in the market for kitchen cabinets?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

Paul White NZ (7/2/2010)Reported as spam - assuming no-one here is in the market for kitchen cabinets?

On days like today I wish I was...

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 13

Paul White NZ (7/2/2010)
Reported as spam - assuming no-one here is in the market for kitchen cabinets?

Thought about it but gave the benefit of the doubt.

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 14

Dave Ballantyne (7/2/2010)
Thought about it but gave the benefit of the doubt.

There have been a spate of copy-n-paste replies from one-point members with a link to something random.

I suppose it's slightly cleverer than the norm...but still pretty obvious in my view.

I might be wrong.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi