SQL Server Table Valued Function Performance Comparison – Multi-Statement vs Inline

Problem

Have you ever experienced a slow query that calls a multi-statement table valued function? In this article, I will investigate why these types of queries are slow and how you can deal with them.

Solution

Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. You can read more about them in User-defined functions.

I am a huge fan of writing stored procedures. They are very flexible because we can use temporary tables, query hints such as OPTION (RECOMPILE), etc. within them. However, sometimes we need to write a function. In fact, these are two different objects with different use cases. For example, we can use a scalar function in a SELECT statement, but we cannot use a stored procedure in the same way.

What are table valued functions (TVFs)?

In SQL Server, there are two main types of table-valued functions (excluding CLR functions):

  • Multi-statement table valued function: It refers to a TVF where it has a return table variable. Inside the function body, there will be statements populating this table variable.
  • Inline table valued function: It refers to a TVF where the function body just contains one line of select statement. There is not a return variable.

The difference between a scalar function and a TVF is that the former returns a single value, while the latter can return multiple rows and columns.

I found a useful article titled Query Performance and Multi-Statement Table-Valued Functions on a Microsoft blog post and recommend reading it.

Before I discuss the performance of TVFs, it is essential to explain a concept called parallelism. Parallelism enables SQL Server to execute queries across multiple CPUs simultaneously. It is a useful capability when SQL Server needs to process large numbers of rows. You can read more about parallelism at:

Using a multi-statement TVF (MSTVF) is useful when the query does not return many rows and does not participate in a join operator. This type of TVF prevents SQL Server from executing a query in parallel mode, even when the query returns many rows.  I will set up a very simple demo to demonstrate this issue and provide a solution.

Set Up Test Environment

I will use SQL Server 2025 and StackOverflow database for examples. StackOverflow database is an open-source database from StackOverflow.com. I set the database compatibility level to 170.

Use master
GO
Alter Database StackOverflow Set Compatibility_Level = 170;
GO

The StackOverflow database has a table for storing user’s information, with a clustered primary key on the Id column. The query below returns the information of users who live in India. To view the actual execution plan, I simply press Ctrl + M in SSMS.

Use StackOverflow
GO
Select 
 u.id, 
 u.DisplayName, 
 u.Reputation, 
 u.CreationDate, 
 u.LastAccessDate, 
 u.DownVotes, 
 u.UpVotes 
From dbo.Users u
Where u.Location = N'India'
GO

You can see the query execution plan in the image below:

query plan

If you right-click on the Select operator, click Properties, and go to the Query Time Stats section in the window on the right, you will see that the CPU time is greater than the elapsed time, which shows that the query ran in parallel.

query time stats

What happens if I wrap the query in an MSTVF?

I put the query in an MSTVF as shown below and pass India as an input parameter value to it:

Create Or ALTER Function [dbo].[ReturnUsers_Multi]
(
 @Location Nvarchar(100)
)
 
Returns @Users TABLE
(
   [Id] [int] NOT NULL,
   [DisplayName] [nvarchar](40) NOT NULL,
   [Reputation] [int] NOT NULL,
   [CreationDate] [datetime] NOT NULL, 
   [LastAccessDate] [datetime] NOT NULL,
   [DownVotes] [int] NOT NULL, 
   [UpVotes] [int] NOT NULL
PRIMARY KEY CLUSTERED 
(
   [Id] ASC
))
AS
 
Begin
 
 Insert Into @Users
 Select
  u.id, 
  u.DisplayName, 
  u.Reputation, 
  u.CreationDate, 
  u.LastAccessDate, 
  u.DownVotes, 
  u.UpVotes 
 From dbo.Users u
 Where u.Location = @Location
 Return 
End
GO
 
Select * From [dbo].[ReturnUsers_Multi] (N'India')
GO

SQL Server ran the query in serial execution mode since there is no parallelism icon in the actual execution plan:

query plan

The following image shows that the query elapsed time is greater than the CPU time:

query time stats

The query ran nearly three times slower when I executed it by calling the MSTVF. We can see that the function is not inlineable by calling the following system DMV:

Select * From sys.sql_modules sm
Where sm.object_id = OBJECT_ID(N'ReturnUsers_Multi')
GO

The image shows that the is_inlineable column is equal to zero.

query results

What Is the solution?

As I said earlier, MSTVF prevents the query from running in parallel. So this time I wrap the query in an inline table-valued function and then pass India to it.

CREATE Or ALTER Function [dbo].[ReturnUsers_Inline]
(
 @Location Nvarchar(100)
)
Returns Table
AS
Return
(
 Select 
  u.id, 
  u.DisplayName, 
  u.Reputation, 
  u.CreationDate, 
  u.LastAccessDate, 
  u.DownVotes, 
  u.UpVotes 
 From dbo.Users u
 Where u.Location = @Location
)
GO
 
Select * From [dbo].[ReturnUsers_Inline] (N'India')
GO

As shown in the image below, SQL Server ran the query in parallel:

query plan

The query elapsed time dropped to 575 milliseconds:

query time stats

Summary

SQL Server can execute queries using multiple CPUs simultaneously, called parallelism. It can be used to reduce the response time of a single query. If you need to write a TVF that returns or processes many rows, or that participates in a join operator, you need to be aware that MSTVFs do not run in parallel. In contrast, Inline TVF functions can execute in parallel mode, as I showed in this article.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *