I've just read several articles that were critical of scalar functions because of performance issues. These articles usually point to using table functions instead. Under what circumstances would it be appropriate to use a scalar function?

Here is a simple example. In SQL Server, we have 5 parts of a name (prefix, first, middle, last, suffix) that come from a person table. In addition to having a column for each name part, we also would like to have a column that concatenates them. In order to be consistent and to save on some typing, we are considering creating a function to create a "full name" string and putting the results in a separate column.

  • For this scenario, I would create a computed persisted column that contained the concatenated name. You can even use your scalar function for it. But by making it persisted, the computed column only gets updated when one of the base values changes, otherwise, it can be queried and selected without incurring additional penalty (except for data storage, etc.). – Jonathan Fite Mar 6 '17 at 16:13

There are good reasons to be critical of the performance of scalar UDFs. Performance problems include forcing the entire plan to be run serially, excessive memory grants, cardinality estimation problems, and lack of inlining. However, that doesn't mean that they don't have good use cases.

Consider a series of stored procedures which needs to call the same complicated business logic that returns a scalar value. A scalar UDF used to assign the requested value to a local variable can work very well. The performance issues of scalar UDFs are really only felt when they are used inside of a query. Using a Scalar UDF inside a stored procedure will not force the entire stored procedure to run serially.

Some applications might require you to send the exact same SQL code to multiple RDBMS platforms. Scalar UDFs can be a good workaround to get functionality which is implemented in different ways on different platforms. For example, truncating a datetime in both SQL Server and Oracle is fairly difficult to do with the same code unless you use a UDF. In this case you will pay the performance penalties of UDFs, but sometimes they are your only option. For a scenario like this you may not be able to use TVFs because that syntax may not be supported by the other platform.

Other than that it's difficult to think of a good use for scalar UDFs. I suppose you might have a query for which response time is not critical. It might be easier to code the query with a scalar UDF, but you're likely to get better (or at the very least no worse) performance with a TVF instead.

| improve this answer | |

The short answer would be when you have no set based way to get the information or have exhausted all other options and rejected them based on their performance being worse.

The reason for avoiding scalar sub queries is that they force SQL Server to perform the scalar query once per every row that is returned. There is almost always ways to get the information back using a set operation that will perform and scale better.

| improve this answer | |
  • The question is about scalar functions, not scalar sub-queries. – a_horse_with_no_name Sep 26 '16 at 14:25
  • @a_horse_with_no_name And scalar functions can very often contain one or more queries to various tables, effectively making them correlated subqueries. – db2 Sep 26 '16 at 15:06

I interpret your question as if you are wondering about the overhead of a function call such as:

select my_concat_function(x,y,z) from t

instead of

select x || y || z from t

Without being an expert on SQL-server, I would guess that the overhead of that function call is negligible.

Function calls are generally considered bad when it comes to predicates. For an sql query like:

select ...
from A
join B
    on f(A.x) = f(B.x)

f can[1] prevent index access on A.x and B.x which will result in a scan of the tables. For a nested loop join this means O(||A|| * ||B||) function calls which can severely decrease performance even if the tables are moderate in size.

[1] A smart optimizer that have enough information may handle the situation fine.

| improve this answer | |
  • Naturally, the small overhead of a function call can add up if you're retrieving thousands of records. – Jon of All Trades Sep 26 '16 at 16:49
  • Sure, but the overhead will be linear. I would not worry about the overhead for returning a couple of thousand ROWS. – Lennart Sep 26 '16 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.