Performance of stored procedure vs scalar-valued function in Microsoft SQL Server

I am wondering abou开发者_运维知识库t performance difference between stored procedure and scalar-valued function. For now i use mostly scalar-valued functions because i can use them inside other queries (and 99% of time they return 1 value anyway). But there are scalar-valued functions that I never use within other queries and usually i call them with simple SELECT dbo.somefunction (parameter) and that's it.

Would it be better from performance point of view to migrate them to stored procedure?

Scalar valued functions are recompiled every time they are called. This is because they cannot be included in the plan cache created from the sql processed by the query optimizer/processor.

For cases where you only call the udf once, (as in Select dbo.UDFName(params) ) it really doesn't matter much, but if you use a scalar valued udf in a query that examines a million rows, the udf will be compiled one million times. This will definitely be a performance hit. There is a technique where (if the algorithm can be written in a set-based structure) that you can convert scalar UDFs into what are called table valued in-line udfs that return one row/one column tables... and these can be included in sql queries plan caches along with the rest of the sql, so they are not subject to this performance hit...

There are no absolutes here, the devil is in the details. The best approach is to test for a given situation by examining the query plan and statistics.

Also, whether the function is CLR or not can make a difference of several orders of magnitude.