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.