Fix My Functions: Speeding Up Scalar and Table Valued UDFs (video)

Last week, I was lucky enough to present on the topic of TSQL User Defined Functions (UDFs) in SQL Server at the PASS Summit.

My session was live-streamed, and the video, scripts, and slides are below.

Want to watch more Summit sessions for free? Check out PASS TV here.

Video (1 hour 10 minutes)

Note: if you’re just here to see the SQL Server 2019 (CTP2.1+) scalar UDF inlining, that starts at 54 minutes into the video. 


Fix My Functions demo script (zip)


Below the slides is an outline of the content, generated by SlideShare.

1. Speeding Up Scalar and Table Valued UDFs Kendra Little, Redgate Fix My Functions

2. Kendra Little Evangelist at Redgate Founder, SQL Workbooks Microsoft MVP, MCM [email protected] @Kendra_Little

3. This talk Level: 200 Audience: DBAs & Devs Goals: Speed up functions

4. “It is a known fact amongst practitioners that UDFs are “evil” when it comes to performance considerations [35, 28].”

5. Agenda

6. Why we use functions Modular meerkat

7. Why functions? Modularity Code reuse Simplify queries

8. Function types Scalar: returns a single value Multi-statement TVF: returns table Inline TVF: returns table

9. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS INT –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Scalar function syntax

10. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS @return_variable TABLE (/* table type definition */) –WITH SCHEMABINDING, … AS BEGIN RETURN END GO Multi-statement TVF syntax

11. CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS TABLE –WITH SCHEMABINDING, … AS RETURN ( ) GO Inline TVF syntax

12. SELECT TOP (10) qp.dbid, qp.query_plan, cp.size_in_bytes / 1024. / 1024. AS size_in_mb FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp ORDER BY size_in_mb DESC; CROSS/OUTER APPLY and TVFs

13. Demo: why so slow?

14. sp_WhoIsActive – free procedure from Adam Machanic:

15. sp_WhoIsActive – free procedure from Adam Machanic:

16. Lightweight Statistics Profiling SQL Server 2014 SP2 through 2017 • Trace Flag 7412 • Install KB 4078596 (2016 & 2017 only) SQL Server 2016 SP1+ MUCH lower overhead SQL Server 2019 no trace flag needed anytime-anywhere

17. sp_WhoIsActive Free procedure Written by @AdamMachanic

18. Find what functions hide Curious corg

19. Get tuning data for functions Estimated & actual execution plans Finding function calls in operators UdfCpuTime and UdfElapsedTime in actual execution plans

20. Demo: spy on function internals

21. Estimated plans help! Scalar functions and multi-statement TVFs: estimated plan shows the function logic • Does not appear in an actual execution plan • Plans for the calling query and the function are stored in sys.dm_exec_query_stats, but you must find them individually

22. UDFs and parallelism TSQL scalar UDFs – serial plan Multi-statement TVFs – serial zone Computed column with TSQL UDF – parallelism eradicator, BEWARE

23. MSTVFs and row estimates SQL Server 2005 – 2012 SQL Server 2014 – 2016 SQL Server 2017+ 100 1 ?

24. Interleaved execution Part of adaptive query processing, all Editions Introduced in SQL Server 2017… • MSTVFS only • Read only queries • Cannot be on the inside of an APPLY • Compatibility level 140+

25. Interleaved execution (continued) Diagram by Joe Sack @JoeSackMSFT ntroducing-interleaved-execution-for-multi-statement-table-valued- functions/

26. Scalar UDF Inlining Clever crow


28. Why are scalar UDFs slow? Executed row by agonizing row Scalar operators not ‘costed’ No cross-statement optimization No parallelism functions/scalar-udf-inlining

29. Automatic inlining Rewrite scalar UDF Substitute rewrite into calling query Then optimize functions/scalar-udf-inlining

30. Demo: wizardry

31. Controlling behavior Database compatibility level 150 CREATE FUNCTION … WITH INLINE = OFF USE HINT (‘DISABLE_TSQL_SCALAR_UDF_INLINING’) functions/scalar-udf-inlining

32. No scalar UDF inlining if it… Uses GETDATE() Uses table variables or TVPs Is in computed column Is in a check constraint functions/scalar-udf-inlining

33. Takeaways

34. Tips for tuning functions Scalar UDFs and Multi-Statement TVFs inhibit parallelism Use SCHEMABINDING if your function doesn’t do data access Use inline TVFs (single statement) or persist data when possible

35. The future of scalar UDFs 2019 inlining is VERY compelling Edition has not been announced Releasing with “high coverage”

36. References & links Lightweight query profiling reference – Pedro Lopes progress-anytime-anywhere/ SQL Server Functions, the basics – Jeremiah Peschka programming/sql-server-functions-the-basics/ Froid: Optimization of Imperative Programs in a Relational Database – Karthik Ramachandra et al

37. References & links continued Interleaved execution for multi-statement TVFs – Joe Sack ucing-interleaved-execution-for-multi-statement-table-valued-functions/ Parallelism inhibitors – Paul White -a-parallel-query-execution-plan.aspx

38. Thank You Where to find me… @Kendra_Little [email protected]

How to cause a simple spill to tempdb

Sometimes it’s useful to know how to cause a problem.

Maybe you’ve never encountered the problem, and want to get hands-on experience. Maybe you’re testing a monitoring tool, and want to see if a condition flags an alert. Maybe you’re testing out a new client tool, and want to see how it displays it.

I recently was going through some demos in SQL Operations Studio, and I found that a spill on a sort operator wasn’t causing a warning to visibly show in the graphic execution plan.

I wanted to file an issue on this and let the Ops Studio team know that would be helpful – but my demo code was somewhat complex and required restoring a rather large database. So I set up a quick code sample to cause a spill that could be run in any database.

What’s the easiest way to cause a spill on a sort operator?

Populate a non-indexed table variable with a bunch of rows, then query the table variable and order the output. Ordering the output will require a sort, and since table variables don’t support column statistics, SQL Server won’t allocate enough memory for the sort.

Voila, super quick sample code…

declare @foo table (i int);

insert @foo
SELECT T1000000.N
FROM Ten T10
CROSS JOIN Ten T100000
CROSS JOIN Ten T1000000;

select 0
FROM @foo

Credit: This code is a simple adaptation of Paul White’s answer on this StackExchange question.

And here’s what the spill looks like in action

Here’s a quick view of what the spills look like in SQL Server Management Studio, and then in Operations Studio.

In Operations Studio, you can currently only see one actual plan at a time (I’ve got an issue filed for that here), and there’s no little warning on the sort operator, although you can see the details of the spill in the tooltip (I commented on this issue to potentially reactivate it).

A quick screencap of the issue turned into an animated gif

Table Value Constructors in TSQL

Last week’s quiz was on Table Value Constructors in TSQL.

Essentially, table value constructors let you create a dataset on the fly. These can occasionally be useful in writing queries, but I think playing with them has another benefit: they provide a simple, lightweight framework to let you develop your ability to think in sets.

I wrote this quiz building up to question #4, which I think is a very fun and interesting pattern.

Let’s take a look at how folks did on the quiz, and see what table valued constructors can do.

Quiz Answers and Explanations

Question 1: A single Table Valued Constructor! How many rows will this query return?

   ('Spider Plant', 'Chlorophytum comosum', 'airplane plant'),
   ('Aloe Vera', 'Aloe vulgari', 'Burn Plant')
)as v(name1, name2, name3);
  • Answer: 2
  • Correct: 252 (86%) / Incorrect: 40 (14%)

The folks who got this incorrect mostly went for 3 — if you haven’t used these before, it’s unclear how those commas work!

Table value constructors allow you to specify multiple rows, each surrounded by (round parens). Look for those round parens to identify the rows.

Screenshot of the results from question 1

Question 2:  I’ve cross applied my TVC with another TVC. How many rows will this query return?

   ('Heart Leaf Philodendron'),
   ('Philodendron cordatum')
) as v(name1)
) as v2(name1);
  • Answer: 4
  • Correct: 239 (82%) / Incorrect: 53 (18%)

We have two table value constructors, and each of them has one column and two rows.

If you look at the execution plan for this query, it takes the datasets and joins them with an INNER JOIN with no join predicate – in this case it’s as if we did a cross join, and got the cartesian product. Each dataset has two rows, so 2 x 2 = 4.

Screenshot of the results of question 2

Question 3:  Something changed in that APPLY. How many rows will this query return?

) as v(thing) 
   (v.thing + ' are friends')
) as v2(thing);
  • Answer: 2
  • Correct: 220 (75%) / Incorrect: 72 (25%)

Whoo, the second table value constructor (v2) is doing something interesting this time: it’s referring to the dataset produced by the first table value constructor (v1), and concatenating on a literal value to the ‘thing’ column.

The results here have two rows: the second TV creates an additional column.

Note: you could get the exact same results using a SELECT to define v2 instead of a VALUES clause.

Screenshot of the results of question 3

Question 4:  Hmmmm… the CROSS APPLY got even more interesting. How many rows will this return?

SELECT v.plant, v2.faves 
   ('Ferns', 'being spritzed', 'shade', 'humidity'), 
   ('Succulents', 'well drained soil', 'to dry out a bit', 'brighter light')
) as v(plant, fav1, fav2, fav3)
) as v2(faves);
  • Answer: 6
  • Correct: 202 (69%) / Incorrect: 90 (31%)

I find this to be a very interesting pattern: we are using a table value constructor to do an unpivot operation!

The first table value constructor (v), is a two row table with four columns: plant, fav1, fav2, and fav3.

The second table value constructor (v2), does something that seems almost impossible:

  • It refers to the fav1, fav2, and fav3 columns from (v)
  • It places all THREE of these columns into a single column named (faves)

This is allowed, and it does work.

The number of rows we get back is the number of values in fav1, fav2, and fav3 – there are two rows and three columns, so 2 x 3 = 6 rows.

Screenshot of the results from question 4


Changing Your Highlight Color in SQL Operations Studio

I’ve been enjoying working with SQL Operations Studio lately – a new, free, cross-platform editor from Microsoft.  The tool is under active development and it’s a great time to try it out and suggest changes for how you’d like to see the product work. (Here’s a suggestion I made this week, for example.)

tldr; use workbench.colorCustomizations

To change the highlight color quickly to bright yellow, open your user settings file, add code like you see in one of the samples below, and save it.

Note that a bright yellow highlight color will not work well with themes that put some font in white – configuring editor.selectionForeground currently only works with high contrast themes (issue).

Because of that current limitation, I like a bright yellow highlight specifically with the “Light SQL Operations Studio” theme:

/* Yellow highlight, works well with Light SQL Operations Studio: */
    "workbench.colorCustomizations": {
        "editor.selectionBackground": "#fffb00",
        "editor.inactiveSelectionBackground": "#fffb007e", 
        "editor.selectionHighlightBackground": "#fffb007e"

The change will take place as soon as you save the settings file.

For the “Dark SQL Operations Studio” theme, which I really like, I find that a purple highlight renders very well:

/* Purple highlight, works well with Dark SQL Operations Studio: */
"workbench.colorCustomizations": {
    "editor.selectionBackground": "#ae00ff86",
    "editor.inactiveSelectionBackground": "#ae00ff41", 
    "editor.selectionHighlightBackground": "#ae00ff41"

Highlighting code is important to me

One of my major uses of SQL Ops studio will be demonstrating code in webcasts and videos, so it’s important to me to be able to set a high-contrast highlight for lines of code.

SQL Ops Studio is based on VSCode, which is very flexible, so I suspected there was a way to do this already. Also, there is a lot of documentation out there on VSCode already, so I searched on “VSCode change highlight color” to help find my way to the solution.

But I figured that lots of folks starting out with SQL Ops Studio may not know that, and that this post might be a good introduction to how to change things like this – as well as how to find things by searching for “vscode”!

Changing the highlight and testing themes

Some handy shortcuts shown in this animation:

  • Opening preferences to edit: ctrl+, (windows) / cmd+, (mac)
  • Toggle block comment: shift+option+a
  • Saving file: ctrl+s (windows) / cmd+s (mac)
  • Viewing/changing color themes: ctrl+k ctrl+t (windows) / cmd+k cmd+t (mac)

Shortcut references: windows, mac

Here’s what it looks like to change the highlight to bright yellow, and to preview it in different themes:

Note that you don’t truly see how the highlight will be implemented until you fully select a theme (preview mode is slightly dimmed), and that the highlight color persists across different themes (which I think is awesome).

This screencap was taken from a mac, using Camtasia screencap to capture the keystrokes.



Why use APPLY?

Michelle asked a great question on the quiz page:

In your own words, why would one want to use a cross apply operator rather than a join operator? I’m old school, and I’m just not getting why a cross apply would be so much better to use than a join.

Here’s my top 3 favorite uses for CROSS APPLY and OUTER APPLY:

  1. APPLY is fantastic for calling table valued functions. I didn’t include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it’s still the #1 use.
  2. Another thing I might use it for is when a query needs a correlated subquery — somewhat like an inline function.
  3. And I also like it for queries that have a calculation that needs to be done and which is referenced in multiple columns in the query, or perhaps also in a predicate and the select. You can perform the computation once in the apply and then reference it multiple times. That way if you have to change the formula later on, you only have to change it in once place, plus I find it’s easier to read in some cases.

But it can do even more…

It can help you (un) pivot data. Check out Kenneth Fisher’s example here.

Brad Schulz’s post here has code samples for those, plus more things like showing how it can be useful for shredding XML:

Brad gets a bit jokey at the end of the post, but there’s a lot of valid uses along the way.

Want to take the quiz on APPLY?

Take the quiz here, then head back here to see how you compare to other folks who took the quiz– reported below.

Quiz Results/Answers/Explanations: CROSS APPLY

The questions in this quiz are based on two tables, created and populated with the following commands:

CREATE TABLE dbo.t2 (t2c1 int IDENTITY PRIMARY KEY, t1c1 int NOT NULL DEFAULT 'foo'); 

GO 5
INSERT dbo.t2 (t1c1) SELECT TOP (2) t1c1 FROM dbo.t1;

This produces two tables which look like this:

Question 1: If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

FROM dbo.t1
     FROM dbo.t2 
     WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: The results of COUNT(*) based on an inner join between the two tables

  • Correct: 106 (42%)
  • Incorrect: 146 (58%)

I know, it’s weird, there’s a 1/0 in there which seems like it would produce a divide by zero error! But that bit of code never gets evaluated in this case, and the query runs successfully.

Sometimes a query using APPLY can be “transformed” into a join operation, and this is one of those cases. You can read more about this in Paul White’s article here.

Most of the time, if you want a join, you should use a join. However, it’s definitely useful to know that APPLY can be used this way for when you are reading and editing code written by others.

Question 2: Will these queries return the same result?

FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

FROM dbo.t1
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: yes

  • Correct: 140 (56%)
  • Incorrect: 112 (44%)

In this case, the second query will be rewritten to use an inner join, just like the first query. One method you can use to verify this is to look at the execution plan for both queries (and you can obviously run them and compare the results).

Both queries return a count of 2.

Here are the estimated plans for both queries:

Question 3: Will these queries return the same result?

FROM dbo.t1
   JOIN dbo.t2
    ON t1.t1c1 = t2.t1c1;

FROM dbo.t1
   FROM dbo.t2 
   WHERE t1.t1c1 = t2.t1c1) AS t2;

Answer: no

  • Correct: 198 (79%)
  • Incorrect: 54 (21%)

The first query returns the count of 2. The second query returns a count of 5.

This is because the first query is implemented as an inner join, and the second query is implemented as an outer join.

Question 4: Will these queries return the same result?

SELECT t1c1 * 2 AS d
FROM dbo.t2;

FROM dbo.t2
CROSS APPLY (SELECT t1c1 * 2 AS d) AS x;

Answer: yes

  • Correct: 161 (64%)
  • Incorrect: 91 (36%)

This is an example of using a CROSS APPLY to compute a calculation.

It’s pretty trivial in this case, but I find this can be very useful in the case of complex queries where you refer to the result of a calculation more than once — doing this can allow you to only do the computation in once place and then refer to it many times. Not only can this make the query more readable sometimes, it can limit the errors if you need to change the calculations (because you’re changing it in fewer places.)


The BREAK Keyword in Transact SQL

BREAK is a useful piece of control-of-flow language in SQL Server, but it only works in specific situations.

Want to take the quiz first?

Take the quiz on BREAK here, then head back here to see how you compare to other folks who took the quiz.

Break Questions, Answers, and Explanations

1) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?




Msg 135, Level 15, State 1, Line 6
Cannot use a BREAK statement outside the scope of a WHILE statement.

How’d people do?

  • Correct: 237 (77%)
  • Incorrect: 70 (23%)

I know, it seems like this should work! But, in fact, BREAK only breaks you out of a WHILE loop.

For other purposes, you may want to use RETURN.

2) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
    PRINT 2

Answer: 2

This one was a little trickier…

  • Correct: 202 (66%)
  • Incorrect: 105 (34%)

In this case BREAK will cause us to exit that WHILE loop (so we won’t be stuck in it forever). BREAK doesn’t cause the execution of the whole batch to terminate, though, so we go on to the next statement, and PRINT 2 is executed.

3) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
IF 1 = 1
    PRINT 2


Msg 135, Level 15, State 1, Line 6
Cannot use a BREAK statement outside the scope of a WHILE statement.

This one foiled even more folks:

  • Correct: 135 (44%)
  • Incorrect: 172 (56%)

The key to this one is that we haven’t defined a statement block. That means that the WHILE statement applies to the next statement only. The IF statement also applies to the next statement only. The BREAK is left out in the cold, and it will throw an error if it is not part of a WHILE loop.

But it’s a tricky question, because it might seem like SQL Server would get to the PRINT and be stuck in an infinite loop, never getting to the point that it throws the error. That doesn’t happen – as soon as we execute, the parse process sees the mistake and we get the error right away.

4) If you run this code in a SQL Server Management Studio session, what will appear in the Messages window?

WHILE 1 = 1
    PRINT 2;

Answer: Commands completed successfully.

  • Correct: 239 (78%)
  • Incorrect: 68 (22%)

In this case, the BREAK and the PRINT are within a defined BEGIN / END statement block together, inside a WHILE loop. When we hit the BREAK it exits the loop, never getting to the PRINT statement.


Using RETURN in Transact SQL: Quiz Results

RETURN is a “control of flow” language keyword in Transact-SQL. It’s very useful for managing when code does — and does NOT — get executed in procedural code, and it can also be used to communicate the status from procedural code.

But not everyone knows how to use RETURN, and learning about it can be awkward because of some quirks of SQL Server Management Studio. It is well worth taking the time to get used to it, though!

Want to test your knowledge?

Take this quiz on RETURN, then head back here to see how you compare to other folks who took the quiz.

Note: I’m going to implement a change soon so that only the four most recent quizzes are open to everyone. I am still going to post questions and answers in follow up posts, but the fun interactive bit won’t be as open (after a month for each quiz).

So now is a good time to go through my quiz archive if you haven’t already!

Quiz results and Discussion of RETURN

I changed the wording on question #4 on July 4 after Nick posted a message about the wording — and he was totally right, there was more than one defensible correct answer.

Changing the wording did not significantly change the average score, it turns out. The average percentage dropped from 65% to 64% once that question was clarified. (That’s not a bad percentage at all — I think a lot of folks aren’t used to using RETURN, and hopefully the quiz made them curious on how it can be useful.)


  • Correct: 292 (78%)
  • Incorrect: 84 (22%)

Answer: 2

The key to this question is that RETURN controls flow within a given batch, and the default batch separator is the word “GO”.

If you want to stop the execution of a script you’re running in SSMS, the RETURN keyword won’t stop everything if you have more than one batch.


  • Correct: 249 (66%)
  • Incorrect: 127 (34%)

Answer: Commands completed successfully.

In this question, RETURN is shown inside a single batch. It will stop the flow of execution before the PRINT statement, and SSMS will simply report, “Commands completed successfully.”

IF 1 = 1

  • Correct: 256 (68%)
  • Incorrect: 120 (32%)

Answer: Commands completed successfully.

This time we have everything in one batch statement again, but we have the RETURN inside of a BEGIN / END block.

The gist of this question is: does RETURN cause it to exit only the BEGIN/END block, or that entire block of code?

The answer is that it causes it to exit from the entire block of code, it will never reach the PRINT statement.

    RETURN 1;

EXEC dbo.test;
  • Correct: 29 (58%)
  • Incorrect: 21 (42%)

Answer: Commands completed successfully.

The wording in this post is the “corrected” wording. The original wording was less clear, so I’ve reported the correct/incorrect ratios for July 5 – 9, after the wording was changed.

This one may seem silly, but I think it’s very important to know that SSMS will not report anything in the Messages tab in the case. It doesn’t mean that RETURN didn’t work, it simply means that the SSMS interface doesn’t show it!

If you want to capture the value that RETURN sends back and do something with it, you need to assign it to something, like this:

    RETURN 1;

EXEC @RESULT = dbo.test;


Used in this way, RETURN can be incredibly useful when it comes to returning status from procedural code.