Since functions are not pre-compiled, is there any performance gain from using user-defined functions as opposed to stored procedures?

I have seen in many instances where a stored procedure is used solely because one can pass parameters to it and use these parameters in the WHERE portion of a SELECT clause. These stored procedures do nothing more than return a result set.

I was also told that SQL Server has a FIFO queue where only a certain number of pre-compiled stored procedures reside.  Furthermore, I was also told that I should consider using user-defined functions that does its SELECT from a view(s) and uses any parameters passed in as delimiters in the WHERE portion of the SELECT clause.

Since functions are not pre-compiled but stored procedures are, is there any performance gain from using user-defined functions with views as opposed to stored procedures?

Answer

Before SQL Server 2000, user-defined functions were unavailable. Because of this, stored procedures were often the only way to emulate what a user-defined function can do now. So the question becomes, are there any advantages of converting my current stored procedures to user-defined functions?

I want to break the answer for this question into two parts: performance and convenience issues. Let's start with performance first.

For the most part, rewriting stored procedures as functions will not give you any performance benefits, and quite possibly, they may cause a performance hit because of the extra overhead they incur as compared to stored procedures. The amount of the performance hit, if any, will depend on how the function is written and what it is doing. Keep in mind that user-defined functions are also pre-optimized and compiled similarly to stored procedures (unlike what you have been told). Even so, they have more overhead than corresponding stored procedures.

From a convenience standpoint, there are some reasons to rewrite some stored procedures as user-defined functions, assuming performance is not a major issue. Some of these include:

• The ability for a user-defined function to act like a table gives developers the ability to break out complex logic into shorter code blocks. This will generally provides the additional benefit of making the code less complex, and easier to write and maintain.
• If you want to be able to invoke a stored procedure directly from within a query, then rewriting a stored procedure as a user-defined function would be worthwhile.

In your question, you say, "I was also told that I should consider using user-defined functions that does its SELECT from a view(s) and uses any parameters passed in as delimiters in the WHERE portion of the SELECT clause"

What you were told is not exactly correct. What you described in your question is what is referred to as an inline user-defined table-valued function. An inline function returns a table data type and is an alternative to using a view, as a user-defined function can pass parameters into a T-SQL select command, and in essence, provide you with a parameterized, non-updateable view of the underlying tables. This can be convenient in many cases, but it won't perform faster than performing the same task in SQL Server. In addition, this type of function is more limited in its functionality than a stored procedure.

I also want to mention that stored procedure query plans are not cached in a FIFO (first-in, first-out) manner in the SQL Server data cache. They are actually cycled out of the data cache based on how often they are used. Because of this, often used query plans can stay in cache for a long time, while seldom used query plans will be cycled out, making room for other stored procedures as needed.