Supercharge Your SQL Server Scalar Functions By Switching To Table Value Functions

Gavin Draper, 2019-01-18

User defined functions in SQL server can cause all kinds of performance problems, there are however some tricks that are well worth knowing when you can’t avoid using them…

Examples below are all on the Stack Overflow Database which you can restore if you want to follow along.

Imagine for whatever reason there are places where you need to compare a DATETIME as an INT YYYYMMDD, we can write a scalar function to do just that…

CREATE FUNCTION dbo.DateToNumber (@Date DATETIME) RETURNS INT AS
BEGIN
   RETURN CONVERT(VARCHAR(10), @Date, 112)
END
GO

Now let’s imagine we want all badges obtained on a given date and just to make this example relevant let’s also imagine for some obscure reason we need to switch all the dates to our numerical format to filter them…

SELECT 
   * 
FROM 
   Badges 
WHERE 
   dbo.DateToNumber([Date]) = '20120804'

I completely accept it’s quicker to just change our parameter to a DATETIME, but let’s imagine in a real scenario we can’t do that because our predicate is a join on a table that uses this numeric date format. The above query takes about 30 seconds on my machine to return 3500 results. At this point we’re kind of out of luck with indexes as no index on date is going to help that index SCAN, if we look at the plan we can immediately see one possible issue…

Scalar Plan

This plan did not go parallel anywhere even though it is seemingly pretty high cost, this is because a scalar function used anywhere in your query will force a serial plan. Now because the scalar function we’ve written is a single statement we can rewrite it as a Table Value Function and SQL Server will essentially inline it into our query…

CREATE FUNCTION dbo.DateToNumberTvf(@Date DATETIME)
RETURNS TABLE
AS
   RETURN (SELECT CONVERT(VARCHAR(10), @Date, 112) [Date]);
GO

Now let’s adapt our SELECT query…

SELECT 
   * 
FROM 
   Badges 
   CROSS APPLY dbo.DateToNumberTvf([Date]) d
WHERE d.[Date] = '20120804'

This gives that exact same results but now runs in less than a second. What’s changed?

Scalar Plan

Not only do we now have a much better plan/faster query but we also have the same plan plan we’d end up with if we’d inlined the function ourselves…

SELECT 
   * 
FROM 
   Badges 
   CROSS APPLY (SELECT CONVERT(VARCHAR(10), Badges.[Date], 112) [Date]) d
WHERE d.[Date] = '20120804'

With the above in mind I find any time I’m running a single statement scalar function across anything more than a couple of rows I’ll lean towards using that Table Value Function first even if it does feel a little less intuitive to write.

One final note on this topic is as of the preview release of SQL Server 2019 the optimizer is now automatically inlining a lot of single statement scalar functions so this table valued function optimization will probably not be needed in future versions.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads