Sql-server – When is it appropriate to use a scalar valued function

functionssql servert-sql

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.

Best Answer

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.

Related Solutions

T-sql – T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs

This is easy if you have a numbers table. The following example is cut and pasted from Erland Sommarskog's site:

CREATE FUNCTION fixbinary_single(@str varbinary(MAX))
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,
              n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4))
       FROM   Numbers n
       WHERE  n.Number <= datalength(@str) / 4 )

Sql-server – Suggest better design pattern for reverse DNS lookups

Do not do it in SQL. From the few things that can be categorized as 'stupid use of SQLCLR', making expensive lengthy network calls ranks as #1. At the very very very very least, make sure the CLR code calls Thread.BeginThreadAffinity() before it goes into waiting for the intertubez to respond (DNS lookup and reverse lookup included).

The proper way to handle this is to use an external process, place the IPs to resolve in a queue, dequeue in batches and resolve several (tens) of IPs in parallel using asynchronous I/O, eg. the non-obsolete Dns.BeginGetHostEntry().

Best Answer

Related Solutions

T-sql – T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs

Sql-server – Suggest better design pattern for reverse DNS lookups

Related Question