What is the use of User Defined Functions in SQL Server in spite of having disadvantages like Temp tables cannot be used in functions? Would suggest scrapping functions in the future releases of SQL Server?
That totally depends on the user requirement.
There are products built without any functions too.
Microsoft can't possibly supply every function anyone could ever possibly want... So they provide a tool that allows us to create our own (often to our own disadvantage). Not being able to create temp tables in UDFs??? There are 3 classes of UDFs... and two of the are "Table Valued Functions".
It's true that their inability to perform side affecting actions and determinism can pose some limitations, but I've seen some very talented developers come up with some very ingenious functions and provide functionality that would fall somewhere between extremely difficult and impossible without them.
To add to what Jason stated...
MS has also taken to adding common functions such as the new Split_String() function. They probably wouldn't have done such a thing if so many people hadn't actually created their own function to split strings. I'm really happy that MS is starting to get with it except that except they bloody well did it wrong. They didn't include an "element position column", it returns no output when given a NULL input, there's no way to absolutely guarantee the order of the return, and MS refuses to say the order of the return is guaranteed. Imagine if the only thing we could do is what MS actually provided a function for.
As for not being able to perform side affecting actions, that would go against nearly ever programming paradigm there ever was or is. I can think of a couple of places where that would be a nice to have feature but the work arounds are incredible simple to accomplish, so no biggee there and, there are a couple of us that DO know how to make them side affecting 
For example, there are many MS provided stored procedures, such as sp_Who that provide the kind of output we want but no way to filter it unless we first dump the output to a Temp Table and then select what we want from that table. Using the trick of being able to execute a stored procedure that takes no parameters from an iTVF (inline table valued function) that uses OPENQUERY, we can avoid all of that and use the function in a FROM clause or CROSS APPLY operator and filter with a simple WHERE clause without having to go through the ardor of creating a Temp Table to hold every bloody column that is returned from the stored procedure itself.
There are also problems with using non-deterministic intrinsic functions within a function. For example, you can't use NEWID() directly in a function. You can, however, call a view from a function and so it's a simple matter of making a view with one column and one row made by "SELECT NEWID() as MyNewID" and then calling the view from the function. Heh... I call that "box thinking". The quote here is that "Before you can think outside the box, you must first realized... you're in a box".
Functions also allow for what every good programming language allows for... code reuse and abstraction. Imagine having to write the code to split string every time you needed to split strings instead of just calling the function through a CROSS APPLY.
Saying that functions should be scrapped because they can't access Temp Tables or because they are missing other functionality is a bit like say that pants should have no pockets because the pockets can't carry what a dump trunk can. Even small tools are incredibly important when you need them.
2 Likes
Speaking of...
Ladies & gents... Jeff Moden!
Talk about the right guy on the right thread.
No arguments from me... IIRC, both restrictions are were part of the mathematical definition of a function before there were computers to apply them to. But... You admit that MS plays by a different set of rules than they make us play by. CHECKSUM, CASE, COALESCE & a few of the DMFs for example, allow for a varying number of optional parameters...
So, they don't follow the strict definition of determinism... Then again, they're also referred to as expressions CASE & COALESCE are technically considered "expressions"... so I suppose what I really want is , "User Defined Expressions".
Also... You can't tell me that you're not looking forward to the when we get inline scalar functions...
A preprocessor with Macros would do me ...
Can't quite bring myself to use Erland Sommarskog's preprocessor, c'oz I'd have to run it on all code changes before I could apply them - even in DEV.
Erland Sommarskog... That's guy's not just in another league... He's playing an entirely different sport. Anytime I read any part of his blog, I realize just how much I have yet to learn.
1 Like
You're too kind. Thanks for the kudo, kind Sir.
They are well deserved... The funny thing is,I had you, Adam Machanic & Itzik Ben-Gan, specifically in mind when I made the previous comment. So you showing up when you did was perfect timing.
Nice article