One of the coolest things Microsoft added to SQL Server was the ability to create user defined functions. There are different types of functions you can create. You can create a scalar function returning only a single variable. You can create set functions returning a virtual table, much like a view. You can also create your own aggregate functions, although those require you to write them in Dot Net. All of the other types of functions may be written in either TSQL or Dot Net.
At first glance the Scalar functions make the most sense. A good example you can review was my last editorial where I demonstrate a function that calculates the number of years between one date and another, including the date and day values in the calculation. You don’t really have to write a function to gather the result. It is just easier to understand what is going on, and a little bit more efficient.
The set functions are more like a view in SQL Server. They are non-materialized data sets, working with one or more tables, or views, returning a single result set as the output. Where table value functions differ from views is that you can provide parameters specific to the function. With a view, you are not sure how or when filtering criteria will be applied, especially if you are joining it to other set sources. With the table function, it is possible that it could be materialized independently without respect to other sets used in your query.
I have found table value functions to be quite useful when working with complicated stored procedures. Rather than combining some complex query work in the same procedure with a log of logic embedded, I have found it is sometimes easier to create a table value function that can be independently tested for accuracy, and then focus on the other aspects of the stored procedure using the proven function, or even using a mock query.
For those of you entering into SQL Server, I highly recommend you build up your TSQL skills for working with functions.
Cheers,
Ben