difference between stored proc and functions?
-
hello,
I have been asked in an interview the following Q.
What is the difference between stored procedure and function?
Please help me out with the answer.
Thanks
-
There are few differences let me list out what comes to my mind.
A stored procedure need not take a value and return a value where as a function needs to do them.
A stored procedure can be called outside the server remotely where as a function can't be called.
Just google out for it you will get n number of links.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
http://www.go4expert.com/forums/showthread.php?t=329
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
thanks a lot for ur help..
-
No one mentioned the other important things...
- UDF's are normally slower than direct code and the exceptions are fairly rare. For example, a function to return a date sans time will frequently take 3 to 10 times more time to execute (depending on the server) than using the instrinsic date functions directly in the code.
- Table Variable UDF's can be tremendously slower because Table Variables do not and cannot be made to use statistics. Further, Table Variables are not spawned in memory only as so many think. Please refer to the following URL and pay very close attention to Q3/A3 and Q4/A4.
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
- Although UDF's are well used to encapsulate difficult, repetative processes, many folks use them instead of designing a database correctly. For example: A UDF to split a variable that contains a CSV list is a good idea if that variable comes from a GUI. Using that same function to split a column of CSVs is a bad idea because storing a column of CSVs in a database is almost always a very bad idea to begin with.
- Using UDF's in a WHERE clause will almost always defeat any chance of the holy-grail of performance, an Index SEEK.
- Using UDF's in a WHERE clause normally indicates that the query is fully RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row") instead of good, high-speed, set-based logic.
--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) -
My, my. One gets the impression anyone who uses a UDF should be drummed out of the industry. One wonders why the awful things were ever developed in the first place.
1. UDF's are normally slower than direct code ...
True. However, 3 to 10 times zilch is still pretty close to zilch. There are advantages to UDFs that handily overcome modest performance hits.
2. Table Variable UDF's can be tremendously slower because Table Variables do not and cannot be made to use statistics....
Also true. However, I only have one of these in my entire toolkit, and the service it performs, though rarely needed, is invaluable when it is needed.
3. Although UDF's are well used to encapsulate difficult, repetative processes, many folks use them instead of designing a database correctly.
I couldn't agree more. However, I get upset at anything used to compensate for poor data modeling. Why pick on UDFs?
4. Using UDF's in a WHERE clause will almost always defeat any chance of the holy-grail of performance, an Index SEEK.
Not necessarily. It depends on where in the WHERE it is used.
WHERE udf_xxx(field) = value
is bad. It makes the WHERE clause non-sargable.
WHERE field = udf_xxx(value)
is fine because the sargablility of the WHERE is maintained. Also note that any performance hit in calling the UDF is averaged out over the result set as it is executed only once per query, not per row. Plus, if business rules have any effect at all on the value returned by the UDF, it is insanity NOT to use one. Business rules--all together, now--CHANGE!
5. Using UDF's in a WHERE clause normally indicates that the query is fully RBAR...
See 4.
Like most of us, I spend a lot of time and effort on performance. However, there seems to be a lot of people who declare that there is a performance hit with using UDFs therefore UDFs should never be used--I currently work in such an environment. The indiscriminate use of UDFs should be discouraged, but the indiscriminate use of anything should be discouraged. A good design will consider the costs vs benefits of all options and select the best ones at every turn.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Like a hammer and a screwdriver, both are tools used to build with.
Sometimes the hammer is better to use than the screwdriver, or the other way around, depending on the task at hand. The skilled craftsman uses one or the other when it's suitable, not just one of them for everything.
Stored procs and UDF's follows the same principle.
=;o)
/Kenneth
-
Heh... that's what I get for playing devil's advocate and I should have explained more...
Thanks for filling in the details. 
You're last paragraph summed it up pretty well and, although my list of potential problems without the necessary explanations for each point seemed to indicate that I was a UDF-a-phobe, I'm not nor do I recommend that anyone become one. My intention was to point out potential problems (took the opposite stance of using them for everything) with UDF's and to consider everything before using them (like anything else). The only reason it appears that I'm picking on UDF's is because that was the subject of this thread. Cursors, single row procs, bad design, and a wealth of other SQL problems just seemed to be too large a subject to cover here
I absolutely agree that "3 to 10 times zilch is still pretty close to zilch" when it comes to UDF's... except when folks use dozens of them on multi-million row processes. Like anything else, "it depends" on what the function does. Although I'm also an advocate of making things easy and extremely readable, I can't advocate taking a 2 second performance hit on a lousy million row lookup just to make things convenient to (for example) strip the time from a date (set its time element to midnight).
I should have clarified point 4 much more (like you did)... most folks look at the estimated execution plan for things like...
WHERE dbo.DateNoTime(t1.StartDate) < dbo.DateNoTime(t1.StartDate)
... where they see the index SCANs and say "Hmmm... it used an index... what's the problem?"
You're right... UDF's have their place. I was just taking the opposite stance of using them as a panacea and I didn't take the time to explain like you did.
Thanks, Tomm.--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) -
Maybe I came on a little heavy. Right now I'm working where there are dozens of servers with over a thousand databases (total -- not per each) and the use of UDFs are forbidden. After fighting what appears to be a losing battle to get at least a few common-sense UDFs defined, I may be a tad sensitive.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Not to worry... considering the situation you said you're in, I absolutely don't blame you especially in the absence of anything else in my rather one-sided first post. I'm in exactly the opposite situation where they want just about everything in a UDF
and you can see that I'm a "tad sensitive" to that Anyone who says "no UDF's" or "only UDF's" has forgotten the primary rule of good decision making... "It Depends"...
--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) -
Heh... I was trying to think of a way to prove some of the bad experiences I've had with some programmers using UDF's for everything... then I checked my email
... this is one of the points I was trying to make... UDF's are not a panacea for every problem but some sure do think so... sour's even the most laid back DBA's on their usage when they see junk like this (second post in the thread)...Again, I'm not against using UDF's and I sure do feel bad about Tomm's current experience with not being allowed to use them at all... but I thought you folks might enjoy seeing why some DBA's just refuse to allow them into their database... here's the URL for the thread I'm talking about...
http://p2p.wrox.com/topic.asp?TOPIC_ID=63033
[Edit] And the kicker is, the UDF isn't even written correctly (doesn't do the required SUM)
--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) -
and, regarding the web site, either a data row has an error, or the office hours sum is wrong in the original post, as well.
the original topic question reminds me of the older question: what's the difference between a subroutine and a function?
the labels (and sometimes the answers) change, but the questions remain the same.
-
Hi,
I use UDFs and stored procedures. For me the most important things that determine whether I use a UDF or a procedure are that:
UDFs cannot use non-deterministic built-in functions such as GetDate(), stored procedures can. {you can get around this by defining a View based on the function}
UDFs cannot modify data in permanent tables or change table structure, stored procedures can.
I use a lot of table valued UDFs for returning commonly used lists in my system. I made that decision because I wanted to define in a single place the code required to retreive a list of, say Project Managers, rather than the motley collection of queries scattered throughout the system which all returned slightly different variations on the theme.
I use stored procedures where I need to modify permanent table data, my log shipping process, one of my major reports which needed use of a physical table populated by a stored procedure to achieve the required performance.
I have only found one occaision where I needed to use cursors (a data extraction job).
Although I don't have a performance issue at the moment, reading this thread I think I might try re-writing some of my functions as stored procs to see if they perform better
David
If it ain't broke, don't fix it...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply