1

What is the exact need for user defined functions in SQL Server?

Everything we can accomplish using functions can be done by stored procedures, so then why were user defined functions introduced?

I'm aware of the differences between stored procedures and functions, but none of them clarifies my question, and I couldn't find anything that a function can do that a stored procedures can't.

Are functions only for better maintainability and understanding or am I missing something?

Dale K
  • 25,246
  • 15
  • 42
  • 71
rinesh
  • 493
  • 1
  • 8
  • 26
  • 2
    You can use functions *in* a query - you can't do that with an SP. – Dale K Aug 05 '19 at 04:29
  • 3
    I would say maintainability myself - you can reuse the same query in multiple places without duplicating code. – Dale K Aug 05 '19 at 04:30
  • 3
    "Everything we can accomplish using functions can be done by stored procedures itself," - this is patently untrue. – Dai Aug 05 '19 at 04:31

2 Answers2

8

Everything we can accomplish using functions can be done by stored procedures itself,

This is false.

  • Stored procedures cannot be recomposed or inlined (like how a VIEW or Table-Valued Function can - and SQL Server 2017 can now inline scalar functions too).
  • Stored procedures cannot be proven "safe" by the database engine (as stored procedures can perform DML/DDL operations, whereas UDFs can only perform read-only operations).
  • Stored procedures cannot be used to perform operations on scalar values.

You can make the argument that everything you can do inside a query that would use a UDF could be done without that UDF - and this is technically true (because stored procedures are Turing Complete), but it's like saying we don't need for, while, try/catch, using, or async in C# because surely if and goto is good enough for everyone!

Code-quality, maintainability, and runtime performance matters - appropriate use of UDFs results in not only aesthetically beautiful queries, but also better performance thanks to inlining, recomposition, expression tree pruning, and so on.

Besides, who is using stored procedures today when all the cool kids are using Entity Framework? :D

Footnote

I want to elaborate on my remark:

Stored procedures cannot be used to perform operations on scalar values.

While it's true that stored procedures have return values and output parameters - they still aren't classed the same as true UDFs because a stored procedure return value can only be an int value (they're closer to program exit codes in POSIX/Win32 than a real return value) and stored procedure output parameters cannot be used inside another query unless you're doing ugly hacks with CURSOR or Dynamic SQL (Injection).

Dai
  • 141,631
  • 28
  • 261
  • 374
  • Technically you could perform operations on scalar values with a stored procedure using output (assuming you mean something like inputting a variable and outputting it with a different value). – ZLK Aug 05 '19 at 04:40
  • 2
    @ZLK You can't use stored procedures in set-based operations, though, which is a huge gain UDFs have over them. – AlwaysLearning Aug 05 '19 at 06:31
  • @AlwaysLearning Yes, that was covered in the first bullet point though (so if it was brought up because it can't be "inlined", then it's a redundant point). – ZLK Aug 05 '19 at 22:13
  • @ZLK The use of functions (or sprocs) in set-based operations isn't about them being inlineable or recomposable. (Scalar functions aren't inlined unless you use SQL Server 2017 or later, and scalar functions worked fine back in SQL Server 2000 - they were just slightly more computationally expensive to use. Similarly, hypothetically, sprocs **could** be used inside a query (e.g. to `JOIN` on a sproc's result tables) if SQL Server's product design team decided to add support for that - probably with some convoluted syntax. – Dai Aug 05 '19 at 22:42
5

In addition to all the points @Dai has already mentioned: I had used scalar functions to "extract" certain bits and pieces as atomic values (e.g. INT etc.) from a rather large XML column, and used a scalar function to define a computed column (based on that XML column).

That's another thing you CANNOT do with a stored procedure - use a function to define the value of a computed column (or define the check or default constraint for a "regular" column).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459