The How To Write SQL Server Queries Correctly Cheat Sheet: Views vs. Inline User Defined Functions

Making Plans For Nigel

Views get a somewhat bad rap from performance tuners, but… It’s not because views are inherently bad. It’s just that we’ve seen things. Horrible things.

Attack ships on fire off the shoulder of Orion… I watched sea-beams glitter in the dark near the Tannhäuser Gate. All those moments will be lost in time, like tears in rain…

The problem is really the stuff that people stick into views. They’re sort of like a junk drawer for data. Someone builds a view that returns a correct set of results, which becomes a source of truth. Then someone else comes along and uses that view in another view, because they know it returns the correct results, and so on and so on. Worse, views tend to do a bunch of data massaging, left joining and coalescing and substringing and replacing and case expressioning and converting things to other things. The bottom line is that views are as bad as you make them.

The end result is a trash monster with a query plan that can only be viewed in full from deep space.

When critical processes start to rely on these views, things inevitably slow to a crawl.

I’ve said all that about views to say that the exact same problem can happen with inline table valued functions. I worked with a client last year who (smartly) started getting away from scalar and multi-statement functions, but the end results were many, many layers of nested inline functions.

Performance wasn’t great. It wasn’t worse, but it was nothing to gloat and beam over.

The Case For Views

Really, the main reason to use a view over an inline table valued function is the potential for turning it into an indexed view. If Microsoft would put an ounce of effort into making indexed views more useful and usable, it would loom a bit larger.

There are some niche reasons too, like some query generation applications use metadata discovery to build dynamic queries that can’t “see” into inline table valued functions the way they can with views, but I try not to get bogged down in tool-specific requirements like that without good reason.

Both views and inline table valued functions offer schemabinding as a creation option. This, among other incantations, are necessary if you’re going to follow the indexed view path.

But, here we find ourselves at the end of the case for views. Perhaps I’m not digging deep enough, but I can’t find much realistic upside.

While doing some research for this, I read through the CREATE VIEW documentation to see if I was missing anything. I was a bit surprised by this, but don’t see it as a great reason to use them:

CHECK OPTION

Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

If you’re into that sort of thing, perhaps this will make views more appealing to you. I’m not sure I can think of why I’d want this to happen, but 🤷‍♂️

The Case For Inline Functions

Now that we’re squared away on views, and we’ve made sure we’re starting with the understanding that either of these module types can be lousy for performance if you put a lousy query in them, and fail to create useful indexes for those queries to access data efficiently.

What would sway my heart of stone towards the humble inline table valued function?

Parameters.

Views can’t be created in a way to pass parameter directly to them. This can be a huge performance win under the right conditions, especially because if you use cross or outer apply to integrate an inline table valued function into your query. You can pass table columns directly in to the function as parameter values. Inline table valued functions take the ick away.

You know how with stored procedures, if you want to use one to process multiple rows from a table, the most workable approach is to use a loop or cursor to assign row values to parameters, and then execute the procedure with them?

Just an example, if you had a stored procedure to take (to make it simple, full) backups, it would be handy to be able to do something like this:

EXEC dbo.TakeAFullBackup
    @DatabaseName AS 
    SELECT
        d.name
    FROM sys.databases AS d
    WHERE d.database_id > 4;

But no, we have to write procedural code to get a list of database names, loop through them, and execute the procedure for each one (or some other close-enough approximation).

Kinda lame, SQL Server. Kinda lame.

Rat Race

When I first came across this oddity, I probably thought (and wrote) things like: “though this is a rare occurrence in views…”

Time has tried that line of thinking and found it wanting. I’ve seen this happen many, many times over now. It’s funny, the more things you learn that can go wrong in a query plan, the more things you become quite paranoid about. The mental checklist is astounding.

Let’s start, as we often do, with an index:

CREATE INDEX
    p
ON dbo.Posts
    (OwnerUserId, Score DESC)
INCLUDE 
    (CreationDate, LastActivityDate)
WITH
    (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Now, before we move on, it’s worth noting that this issue is fixed under certain conditions:

You’re on SQL Server 2017 CU30, and have query optimizer hot fixes enabled
I’m not sure which CU this fix was released for in SQL Server 2019, it’s not in any that I can find easily
You’re on SQL Server 2022 and using compatibility level 160

From my testing, it doesn’t matter which compatibility level you’re in on SQL Server 2017 or 2019, as long as optimizer hot fixes are enabled.

/*Using a database scoped configuration*/
ALTER DATABASE SCOPED CONFIGURATION 
SET QUERY_OPTIMIZER_HOTFIXES = ON;

/*Using a trace flag instead*/
DBCC TRACEON(4199, -1);

/*SQL Server 2022+ only*/
ALTER DATABASE StackOverflow2013
SET COMPATIBILITY_LEVEL = 160;

For our purposes, we’ll be using SQL Server 2022 in compatibility level 150, with query optimizer hot fixes disabled.

No Problemo (Query)

Here’s a view and a query, where things work just fine:

CREATE OR ALTER VIEW
    dbo.DasView
WITH SCHEMABINDING
AS
SELECT
    p.Score,
    p.OwnerUserId,
    p.CreationDate,
    p.LastActivityDate,
    DENSE_RANK() OVER 
    ( 
        PARTITION BY 
            p.OwnerUserId
        ORDER BY 
            p.Score DESC
    ) AS PostRank
FROM dbo.Posts AS p;
GO

SELECT 
    p.*
FROM dbo.DasView AS p
WHERE p.OwnerUserId = 22656;
GO

The reason this works fine is because the where clause contains a literal value, and not a variable or parameter placeholder.

Everything is how we would expect this query plan to look, given the indexes available.

Si Problemo (View)

Where things become wantonly unhinged is when we supply a placeholder for that literal value.

CREATE OR ALTER PROCEDURE 
    dbo.DasProcedure 
(
    @OwnerUserId integer
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
    SELECT 
        p.*
    FROM dbo.DasView AS p
    WHERE p.OwnerUserId = @OwnerUserId
    /*OPTION(QUERYTRACEON 4199)*/
    /*OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160'))*/;
END;
GO

EXEC dbo.DasProcedure 
    @OwnerUserId = 22656;
GO

Note that I have a query trace on and use hint here, but quoted out. You could also use these to fix the issue for a single query, but my goal is to show you what happens when things aren’t fixed.

Here’s what that looks like:

Rather than a seek into the index we created, the entire thing is scanned, and we have a filter that evaluates our placeholder from 17 million rows and whittles the results down to 27,901 rows.

No Problemo (Function)

Using an inline table valued function allows us to bypass the issue, without any hints or database settings changes.

CREATE OR ALTER FUNCTION  
    dbo.DasFunction 
(
    @OwnerUserId integer
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
    p.Score,
    p.OwnerUserId,
    p.CreationDate,
    p.LastActivityDate,
    DENSE_RANK() OVER 
    ( 
        PARTITION BY 
            p.OwnerUserId
        ORDER BY 
            p.Score DESC
    ) AS PostRank
FROM dbo.Posts AS p
WHERE p.OwnerUserId = @OwnerUserId;
GO

This changes our procedure as well:

CREATE OR ALTER PROCEDURE 
    dbo.DasProcedure 
(
    @OwnerUserId integer
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
    SELECT 
        p.*
    FROM dbo.DasFunction(@OwnerUserId) AS p;
END;
GO


EXEC dbo.DasProcedure
    @OwnerUserId = 22656;

And our query plan goes back to normal.

Even if you don’t have this specific problem, it’s often worth exploring converting views to inline table valued functions, because quite often there is a common filtering or joining criteria, and having parameters to express that is beneficial in a couple ways:

It better shows the intent of module and what it can be used for
It prevents developers from forgetting filtering criteria and exploding results

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.