Table Valued UDF vs Stored Proc for passing values to a SELECT statement

Question

I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL Server view).

I believe this was done due to it being more efficient for the given queries to have values passed deep into the inner queries, than it was to use a view with a WHERE clause.

In these cases, none of the other features you get by using a stored procedure are required e.g. they are all only select statements... no transactions, it's a single result set, no error handling, no calling to other procedures, etc.

For this specific use case - which is basically a "parameterized view" - are there efficiencies to be gained by using an inline user-defined function that returns a table data type instead of stored procedures for this?

The use case I am dealing with involves very small result sets. A simple example would be where a web app calls a proc with, say, a member id parameter, the proc contains a query with a bunch of joins and subqueries etc. (the parameter is used deep within these) and then send back a list of, say, subscriptions, owned by that user.

Is one method generally better than the other?

Answer 1

The answer partially depends on what you do with the output of the object. Both stored procedures and table-valued functions support inserting into table, but using an inline table-valued function can be significantly more efficient.

Below are a few silly object definitions for the demo. Both of them generate a result set of about 6.4 million rows with a single column.

CREATE PROCEDURE dbo.DUMMY_PROCEDURE (@i BIGINT)
AS
BEGIN
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0;
END;

GO

CREATE FUNCTION dbo.DUMMY_FUNCTION (@i BIGINT)
returns table
as
return (
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0
);

GO

Next I'll insert the results of the stored procedure into a temp table. In general, this isn't a good thing to do for result sets that aren't tiny. The results are written to a separate internal structure before being inserted into the table. SQL Server uses a cardinality estimate of 1 row for the insert which can also cause performance issues.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 11514 ms,  elapsed time = 10748 ms
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
EXEC dbo.DUMMY_PROCEDURE 1;

The query took about 11 seconds. I uploaded the actual plan here if you want to take a look. Here's a screenshot of the worst part:

Things are much better with the function. The function definition is inlined and I'm eligible for parallel insert in SQL Server 2016. The insert takes about half a second.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 1562 ms,  elapsed time = 520 ms.
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
SELECT COL
FROM dbo.DUMMY_FUNCTION (1);

The query plan is healthier and much more typical:

There might be other internals differences in addition to that. I couldn't tell you.