T-SQL: Inline TVF vs Standard TVF

There is sometimes a confusion about when you should make use of a standard TVF and an inline TVF. I have often found myself ignoring the merits of an ITVF and instead just ploughing ahead with STVF, possibly because the syntax just pops out of my fingers and its also the most common one I use.

However, recently I came across an issue whereby a TVF was taking a particularly long time to complete and causing performance issues. I checked the definition of the function to see the query construction and the tables referenced but my first instinct was to check the existence of indexes and possible fragmentation. On glancing back at the function I noticed that although it was returning the results of a single query, it was written as a standard TVF. It was then that I had a moment of clarity and rewriting the query as an inline TVF had immediate performance benefits.

An inline TVF is essentially just a stored parameterised sql statement, similar to a view. A standard TVF is much more than that and is intented to run a batch of SQL statements with the return value being a table of results. At its simplest when using a standard TVF you essentially run a query and insert the results into a table variable and return this table variable. Even as I type that it sounds crazy that in that scenario I would choose a standard over an inline, but thats what I have seen time and again (and been guilty of myself!). There is an obvious overhead of writing the data to a table variable (hitting tempdb) and then just reading that data straight back out. Much more sensible to read the data straight.

I've created a simple example which illustrates the point. I'm running SQL2008 SP1.

Here are the functions:

USE AdventureWorks
GO
-- the standard TVF
CREATE FUNCTION dbo.stvf_GetEmployees(@ManagerID INT)
RETURNS @Employee TABLE (
[EmployeeID] [int], [NationalIDNumber] [nvarchar](15) NOT NULL,
[ContactID] [int] NOT NULL, [LoginID] [nvarchar](256) NOT NULL,
[ManagerID] [int] NULL, [Title] [nvarchar](50) NOT NULL,
[BirthDate] [datetime] NOT NULL, [MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL, [HireDate] [datetime] NOT NULL,
[SalariedFlag] [dbo].[Flag] NOT NULL, [VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL, [CurrentFlag] [dbo].[Flag] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
AS
BEGIN

INSERT INTO @Employee
SELECT EmployeeID, NationalIDNumber, ContactID, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, ModifiedDate
FROM HumanResources.Employee
WHERE ManagerID = @ManagerID

RETURN

END
GO
-- the Inline TVF
CREATE FUNCTION dbo.itvf_GetEmployees(@ManagerID INT)
RETURNS TABLE
AS
RETURN
SELECT EmployeeID, NationalIDNumber, ContactID, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, ModifiedDate
FROM HumanResources.Employee
WHERE ManagerID = @ManagerID

GO


Running the inline tvf generates a pretty simple estimated execution plan:



Whereas running the standard tvf shows the extra work that is involved - inserting and THEN reading:



Making this change took the execution time for the function down from 10mins to nearer 2mins and had significant benefits further down the chain (as the tvf was used in a CROSS APPLY).

So if you're like me and tend to just stick with the same style TVF then be aware of the potential performance implications. Microsoft has provided these 2 types of functions (probably for this reason!) so make sure you use of the appropriate TVF to ensure optimum performance.