The topic of today’s post might be obvious, but is backed by a question that came to me while making a change to one of our in-line table-valued functions (TVF).

We had an in-line TVF to which we had to add another SELECT statement in order to achieve a business requirement. Normally, the first thing that most developers would do is to convert it into a multi-line TVF. As a DBA, this would raise a red flag due to the much-talked about performance issues associated with multi-line TVFs.

Per Books-on-line (refer “Further Reading” section below):

The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

Now, a view can use multiple SELECT statements separated by UNION or UNION ALL. Hence, if a UNION can be used be used such that the SELECTs in a TVF can be combined into a single statement, then SQL Server will continue to treat the function as an in-line TVF. Here’s the simple example I used to cross-check that a function with only multiple SELECTs combined via the use of UNION will make it an in-line TVF:

USE tempdb;
GO</pre>
--Safety Check
IF OBJECT_ID('dbo.tmpfunc_MultiLineTest','IF') IS NOT NULL
DROP FUNCTION dbo.tmpfunc_MultiLineTest;
GO

--Create the function
--Disclaimer: This function has been created for demo purposes only!
CREATE FUNCTION dbo.tmpfunc_MultiLineTest
(@id1 INT,
@id2 INT
)
RETURNS TABLE
AS
RETURN
SELECT 1 AS Col1, 2 AS Col2
UNION
SELECT 2 AS Col1, 3 AS Col2
UNION
SELECT @id1 AS Col1, @id2 AS Col2
GO

--Is the function an in-line table valued function?
SELECT so.name,
so.type,
so.type_desc
FROM sys.objects AS so
WHERE so.name = 'tmpfunc_MultiLineTest';
GO
-------------------------
-- RESULTS
-------------------------
--name type type_desc
-------------------------- ---- ---------------------------------
--tmpfunc_MultiLineTest IF SQL_INLINE_TABLE_VALUED_FUNCTION

As you can see, I have a function that uses the syntax for in-line TVFs and has 3 SELECT statements combined via the use of a UNION operator. I then query the sys.objects catalog view which clearly tells me that this created an in-line TVF.

The reason? It’s quite simple – it’s still a single query!

Further Reading

• Inline User Defined Table Valued Functions [TechNet Link]
• Table valued parameters to a table valued function – Underappreciated features of Microsoft SQL Server [Blog Link]

Until we meet next time,

Be courteous. Drive responsibly.