Tricky Situations When Rewriting Functions In SQL Server

Walk In Line


Far and away, some of the trickiest situations I run into when helping clients is rewriting scalar functions that have WHILE loops in them.

This sort of procedural code is often difficult, but not impossible, to replace with set-based logic.

Sure, lots of IF/THEN/ELSE stuff can be tough too, though that’s often easier to  manage with CASE expressions in stacked CTEs or derived tables.

I ran across a really interesting function recently that I had to rewrite that had a couple WHILE loops in it, and I’ve simplified the example here to show my approach to fixing it.

Table Face


The original intent of the function was to do some string manipulation and return a cleaned version of it.

There were several loops that looked for “illegal” characters, add in formatting characters (like dashes), etc.

The problem the function caused wasn’t it running for a long time (we’ll talk more about that tomorrow), it was that the function was called in really critical code paths that Function Repercussions© were messing with:

  • Row by row execution
  • Inhibited parallelism

These are not the kinds of functions that are Froid Friendly© either. If they were, I could largely leave them alone. Maybe.

Depends on bugs.

Bad Way


The bad way of doing this is like so. If you write functions like this, feel bad. Let it burn a little.

Ten years ago, I’d understand. These days, there’s a billion blog posts about why this is bad.

CREATE OR ALTER FUNCTION
    dbo.CountLetters_Bad
(
    @String varchar(20)
)
RETURNS bigint
AS 
BEGIN

DECLARE
    @CountLetters bigint = 0,
    @Counter int = 0;
    
    WHILE 
        LEN(@String) >= @Counter
    BEGIN
        IF PATINDEX
           (
               '%[^0-9]%', 
               SUBSTRING
               (
                   @String, 
                   LEN(@String) - @Counter, 
                   1
               )
           ) > 0
        BEGIN
            SET @CountLetters += 1;
            SET @Counter += 1;
        END;        
        ELSE        
        BEGIN
            SET @Counter += 1;
        END;
    END;

    RETURN @CountLetters;

END;
GO 


SELECT 
    CountLetters = 
        dbo.CountLetters_Bad('1A1A1A1A1A');

Better Way


This is a better way to write this specific function. It doesn’t come with all the baggage that the other function has.

But the thing is, if you just test them with the example calls at the end, you wouldn’t nearly be able to tell the difference.

CREATE OR ALTER FUNCTION 
   dbo.CountLetters
(   
    @String AS varchar(20)  
)  
RETURNS table
AS
RETURN

WITH
    t AS 
(
    SELECT TOP(LEN(@String))
        *,
        s = 
            SUBSTRING
            (
                @String, 
                n.Number +1, 
                1
            )
    FROM dbo.Numbers AS n
)
    SELECT
        NumLetters = 
            COUNT_BIG(*)
    FROM t
    WHERE PATINDEX('%[^0-9]%', t.s) > 0;
GO

SELECT
    cl.*
FROM dbo.CountLetters('1A1A1A1A1A') AS cl;

Pop Quiz Tomorrow


This is a problem I run into a lot: developers don’t really test SQL code in ways that are realistic to how it’ll be used.

  • Look, this scalar UDF runs fine for a single value
  • Look, this view runs fine on its own
  • Look, this table variable is great when I pass a test value to it

But this is hardly the methodology you should be using, because:

  • You’re gonna stick UDFs all over huge queries
  • You’re gonna join that view to 75,000 other views
  • You’re gonna let users pass real values to table variables that match lots of data

In tomorrow’s post, I’m gonna show you an example of how to better test code that calls functions, and what to look for.

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 on 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.

Proving That SQL Server T-SQL Scalar UDFs Execute Once Per Row

Scripted Content


If you use this script, you can follow along with the results.

The sys.dm_exec_function_stats DMV is only available in SQL Server 2016+, so if you’re on an earlier version than that, you should focus your energies on upgrading.

I’m sure you’ve just been busy for the last five years.

/*Context is everything*/
USE master;
GO 

/*Piece of crap function*/
CREATE OR ALTER FUNCTION 
    dbo.useless_function(@dbid int)
RETURNS sysname
AS
BEGIN
    DECLARE /*This is to ruin scalar UDF inlining*/
        @gd datetime = GETDATE();

    RETURN DB_NAME(@dbid);
END;
GO 

/*Just in the select list*/
SELECT
    database_name = 
        dbo.useless_function(d.database_id),
    total_databases = 
        (SELECT c = COUNT_BIG(*) FROM sys.databases AS d2)
FROM sys.databases AS d;
GO 

/*Executions here should match the count above*/
SELECT
    object_name =
        OBJECT_NAME(defs.object_id),
    defs.execution_count
FROM sys.dm_exec_function_stats AS defs
WHERE defs.object_id = OBJECT_ID('dbo.useless_function');

/*Reset the counters*/
DBCC FREEPROCCACHE;
GO 

/*Now in the where clause*/
SELECT
    mf.name,
    total_database_files = 
        (SELECT c = COUNT_BIG(*) FROM sys.master_files AS mf2)
FROM sys.master_files AS mf
WHERE mf.name = dbo.useless_function(mf.database_id)
GO 

/*Executions here should match the count above*/
SELECT
    object_name =
        OBJECT_NAME(defs.object_id),
    defs.execution_count
FROM sys.dm_exec_function_stats AS defs
WHERE defs.object_id = OBJECT_ID('dbo.useless_function');

Data Driven


On my server, I have 9 total databases and 42 total database files.

The results, therefore, look precisely and consistently like so:

SQL Server Query Results
You Probably Won’t Like This About Functions

Assumption


For the select list, T-SQL scalar UDFs will execute once per row projected by the query, e.g. the final resulting row count, under… Every circumstance I’ve ever seen.

In SQL server. Of course.

As a couple easy-to-digest examples. Let’s say you execute a query that returns 100 rows:

  • Your T-SQL scalar UDF is referenced once in the select list, so it’ll run 100 times
  • Your T-SQL scalar UDF is referenced twice in the select list, so it’ll run 200 times

For T-SQL scalar UDFs in other parts of a query, like:

  • Where Clause
  • Join Clause

They will execute for as many rows need to be filtered when these parts of the query are executed, for as many individual references to the function as there are in the query.

The results here may vary, depending on if there are any other predicates involved that may filter out other rows.

As a couple easy-to-digest examples:

  • If you use a T-SQL scalar UDF as a predicate on a million row table, it’ll execute a million times to produce a result and apply the predicate
  • If you do the same thing as above, but there’s another part of the where clause that filters out 500k rows, the function will only execute 500k times

All sorts of other things might change this, like if the other predicate(s) can be pushed to when the data is accessed, and if there are multiple invocations of the function.

You can see an edge case where that’s not true in this post:

A Hidden Value Of Apply With SQL Server Scalar UDFs

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 on 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 performance problems quickly.

Software Vendor Mistakes With SQL Server: Using Date Functions On Columns Or Local Variables

Reusable Logic


In most programming languages, it’s quite sensible to create a variable or parameter, use some predefined logic to assign it a value, and then keep reusing it to prevent having to execute the same code over and over again.

But those languages are all procedural, and have a bit of a different set of rules and whatnot. In SQL Server, there are certainly somewhat procedural elements.

  • Functions
  • Control-flow logic
  • Cursors
  • While loops
  • Maybe the inner side of Nested Loops joins

You may be able to name some more, if you really get to thinking about it. That should be a common enough list, though.

Reusable Problems


SQL Server has a wonderful optimizer. It’s capable of many things, but it also has some problems.

Many of those problems exist today for “backwards compatibility”. In other words: play legacy games, win legacy performance.

Lots of people have found “workarounds” that rely on exploiting product behavior, and taking that away or changing it would result in… something else.

That’s why so many changes (improvements?) are hidden behind trace flags, compatibility levels, hints, batch mode, and other “fences” that you have to specifically hop to see if the grass is greener.

One of those things is the use of local variables. The linked post details how lousy those can be.

In this post, I show how you’re better off using the date math expressions instead.

And in this post, I show how you’re better off doing date math on parameters rather than on columns.

Let’s bring all that together!

Reusable Solutions


In SQL Server, context is everything. By context, I mean the way different methods of query execution are able to accept arguments from others.

You’ll sometimes hear this referred to as scope, too. Usually people will say inner context/scope and outer context/scope, or something similar.

What that means is something like this, if we’re talking about stored procedures:

CREATE PROCEDURE
    dbo.InnerContext
(
    @StartDate datetime,
    @EndDate datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    SELECT
        C.PostId,
    	Score = 
    	    SUM(C.Score)
    FROM dbo.Comments AS C
    JOIN dbo.Votes AS V
        ON C.PostId = V.PostId
    WHERE C.CreationDate >= @StartDate
    AND   c.CreationDate <  @EndDate
    GROUP BY c.PostId;

END;
GO 

CREATE PROCEDURE
    dbo.OuterContext
(
    @StartDate datetime,
    @EndDate datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

IF @StartDate IS NULL
BEGIN
   SELECT
       @StartDate = GETDATE();
END;

IF @EndDate IS NULL
BEGIN
   SELECT
       @EndDate = DATEADD(DAY, 30, GETDATE());
END;

EXEC dbo.InnerContext
    @StartDate = @StartDate,
    @EndDate = @EndDate;

END;

If you’re okay using dynamic SQL, and really, you should be because it’s awesome when you’re not bad at it, you can do something like this:

CREATE PROCEDURE
    dbo.OuterContext
(
    @StartDate datetime,
    @EndDate datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

IF @StartDate IS NULL
BEGIN
   SELECT
       @StartDate = GETDATE();
END;

IF @EndDate IS NULL
BEGIN
   SELECT
       @EndDate = DATEADD(DAY, 30, GETDATE());
END;

DECLARE 
    @sql nvarchar(MAX) = N'
    /*dbo.OuterContext*/
    SELECT
        C.PostId,
    	Score = 
    	    SUM(C.Score)
    FROM dbo.Comments AS C
    JOIN dbo.Votes AS V
        ON C.PostId = V.PostId
    WHERE C.CreationDate >= @StartDate
    AND   c.CreationDate <  @EndDate
    GROUP BY c.PostId;		
    ';

EXEC sys.sp_executesql
    @sql,
  N'@StartDate datetime, 
    @EndDate datetime',
    @StartDate,
    @EndDate;

END;

Which will achieve the same thing.

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 on 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 performance problems quickly.

Software Vendor Mistakes With SQL Server: Using Functions In Join Or Where Clauses

Easy Thinking


To start with, let’s classify functions into two varietals:

  • Ones built-in to SQL Server
  • Ones that developers write

Of those, built-in functions are pretty much harmless when they’re in the select list. Classifying things a bit further for the ones user writes, we have:

Out of the four up there, only the last one doesn’t have a reference link. Why? Because I don’t write C# — I’m not that smart — if you need someone smart about that, go read my friend Josh’s blog. He’s quite capable.

If you’re too lazy to go read the three reference links:

  • Scalar User Defined Functions generally wreck performance
  • Multi-Statement Table Valued Functions stand a high chance of generally wrecking performance
  • Inline Table Valued Functions are okay as long as you don’t do anything awful in them

Smart Thinking


The important thing to understand is that using any of these functions, let’s call it below the belt, can really mess things up for query performance in new and profound ways compared to what they can do in just the select list.

To be more specific for you, dear developer reader, let’s frame below the belt as anything underneath the from clause. Things here get particularly troublesome, because much of the activity here is considered relational, whereas stuff up above is mostly just informational.

Why is the relational stuff a much bigger deal than the informational stuff? Because that’s where all the math happens in a query plan, and SQL Server’s optimizer decides on all sorts of things at compile-time, like:

  • Which indexes to use
  • Join order
  • Join types
  • Memory grants
  • Parallelism
  • Seeks and Scans
  • Aggregate types
  • Much, much more!

Those things are partially based on how well it’s able to estimate the number of rows that join and where conditions will produce.

Sticking functions in the way of those join and where conditions is a bit like putting a blindfold on SQL Server’s optimization and cardinality estimation process and asking it to swing a bowling ball sharp saber at a piece of confetti at 100 paces.

In other words, don’t complain when your query plans suck and your queries run slow. You’re doing the hobbling, you dirty bird.

Future Thinking


If you want your customers, users, or whatever you want to call them, to be reliably happy in the future, even as their database sizes grow beyond your wildest imagination, and your application gets used in ways that would make Caligula blush, you need to start by obeying the first law of database physics: thou shalt not get in the way of the optimizer.

Going back to a couple other laws of  database physics that cannot be ignored:

  • Anything that makes your job easier makes the optimizer’s job harder
  • Store data the way you query it, and query data the way you store it

If it makes you feel better, stick a few thous and shalls or shalt nots or whences or whenceforths in there. It might make you happier.

It will make your customers, users, or whatever you want to call them happier, if you listen to me.

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 on 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 performance problems quickly.

Software Vendor Mistakes With SQL Server: String Splitting With Functions

Advent


Since SQL Server 2016, at least for databases in a similarly current compatibility level, the STRING_SPLIT function has been available. There were some problems with it initially that have recently been solved, too, like a lack of an “ordinal position” indicator, to show you when items occurred in a string.

But there’s still a bigger problem, since it’s sort of a Jack-Of-All-Splitters, you might not be getting the datatype back that you want. From the docs:

If the ordinal output column is not enabled, STRING_SPLIT returns a single-column table whose rows are the substrings. The name of the column is value. It returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise, it returns varchar. The length of the return type is the same as the length of the string argument.

If you’re comparing the output to something that isn’t stringy, you might hit some weird implicit conversion issues.

Another significant problem is that SQL Server’s optimizer can’t see inside the string you pass in to generate any meaningful statistics information about the values in there.

This is generally true of many of the unpleasant T-SQL User Defined Functions I’ve seen written to split string prior to this, too.

Playtime


The big blaring headline here should be that if you’re going to do split any string and attempt to filter or join to it, you should put the parsed results into a #temp table first, with whatever indexing is appropriate. If you don’t do that, you’re gonna see all the screwy stuff that I mention below.

First, you only get a 50 row estimate no matter how many separate values are in the string. That might be fine for you, but it might not be fine for everyone.

Let’s start by throwing a real knuckleball to the function.

DECLARE 
    @x nvarchar(MAX) = N'';

SELECT 
    @x += 
        (
               SELECT DISTINCT
                   [text()] = 
                       ',' + RTRIM(P.OwnerUserId)
               FROM dbo.Posts AS P
               FOR XML 
                   PATH(''),
                   TYPE
        ).value
            (
                './text()[1]', 
                'nvarchar(max)'
            );

SELECT
    p.OwnerUserId,
    TotalScore = 
        SUM(p.Score * 1.)
FROM dbo.Posts AS P
WHERE P.OwnerUserId IN
(
    SELECT
        SS.value
    FROM STRING_SPLIT(@x, ',') AS SS
)
GROUP BY P.OwnerUserId
OPTION(RECOMPILE);

The code above takes every unique value in the OwnerUseId column in the Posts table and adds them to a comma separated list. Why didn’t I use the STRING_AGG function? I forgot. And after so many years, I have the XML equivalent memorized.

Even with a recompile hint on board, this is the query plan we get:

SQL Server Query Plan

 

Spending 8 seconds inside the splitter function feels pretty bad on its own, but then the entire query plan turns out crappy because of the 50 row estimate, and takes 11 minutes to finish in total. I spent 11 minutes of my life waiting for that to finish. Just for you.

Think about that.

Regularly


Under more normal circumstances, the lack of introspection (of the statistical variety) that you get from directly querying the splitter function can lead to a bad time.

I’m intentionally including a value with a fair amount of skew in the list-to-be-parsed, that leads to what might be a bad plan choice, based on that lack of introspection:

SELECT
    p.OwnerUserId,
    TotalScore = 
        SUM(p.Score * 1.)
FROM dbo.Posts AS P
WHERE P.OwnerUserId IN
(
    SELECT
        SS.value
    FROM STRING_SPLIT('1138,22656', ',') AS SS
)
GROUP BY P.OwnerUserId;
SQL Server Query Plan
zee problemmen!

We get a 50 row guess for two values, but one of those values has ~28k rows associated with it in the Posts table. Since there’s no stats on the internal elements of the string, we don’t get a good estimate for it at all

Big Awkward


If you, or one of your users, throws a malformed string into the mix, you might get some unexpected results.

On top of the values that we just passed in, I’m also going to pass in an empty string at the end after the final comma:

SELECT
    p.OwnerUserId,
    TotalScore = 
        SUM(p.Score * 1.)
FROM dbo.Posts AS P
WHERE P.OwnerUserId IN
(
    SELECT
        SS.value
    FROM STRING_SPLIT('1138,22656, ', ',') AS SS
)
GROUP BY P.OwnerUserId;

This time around, the results are a little different from above. The space at the end is implicitly converted from an empty string to the number 0, and things look like this:

SQL Server Query Plan
played

Most all of the bad guesses are retained as the previous plan, but since the number 0 accounts for a whole mess of rows too, things get a whole lot worse.

We go from 4.5 seconds to 30 seconds, and blah blah blah everything is horrible.

If you want to get around this, sure, you can add a filter to remove empty strings from the results of the splitter, but if you’re reading this, chances are you might not have the most defensive T-SQL coding practices in place. to begin with.

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 on 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 performance problems quickly.

Software Vendor Mistakes With SQL Server: Writing Functions That Already Exist

Reinventing The Sequel


In my time consulting, I’ve seen dbo-prefixed functions that have internal-function names on a number of occasions, acting like a wrapper to the existing functions that come with SQL Server.

While those are fairly ridiculous, and easy to point out the ridiculousness of, there are some other functions that I see pretty often that are maybe a little less obvious. I’m not talking about functions that have been added more recently that might not be supported by all customer installations.

All Supported Versions


One of the more popular versions of this malady that I see looks something like this:

CREATE OR ALTER FUNCTION 
    dbo.ufn_FmtDt
(
    @d datetime
)
RETURNS char(11)
AS
BEGIN
    RETURN CONVERT(char(11), @d)
END;
GO

There are two big reasons this is bad:

  • SQL Server doesn’t store dates as strings, at all, ever
  • You can generate the same internal representation by converting to a date

You gain nothing by encapsulating code like this, at least not since SQL Server 2000 or so.

Do It Again


Using some lessons we learned from earlier in this series, we can keep reasonable up to date with SQL Server’s progress by rewriting the function to something like this:

CREATE OR ALTER FUNCTION 
    dbo.ifn_FmtDt
(
    @d datetime
)
RETURNS TABLE
AS
RETURN 
   
   SELECT d = 
       CONVERT(date, @d);
GO

And now when you need to chop the times off all those dates in your reporting queries, they won’t be artificially held back.

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 on 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 performance problems quickly.

Software Vendor Mistakes With SQL Server: Not Using Inline Table Valued Functions

Swiss Army


In the last couple posts, I’ve talked about the problems caused by two types of T-SQL functions: Scalar User Defined Functions, and Multi-Statement Table Valued Functions.

I’ve also hinted around about a third type of function, called an Inline Table Valued Function. These are different because there are no built-in issues with them as a feature.

They’re only as bad as the query you put in them, and often rewriting T-SQL Scalar User Defined Functions, and Multi-Statement Table Valued Functions as Inline Table Valued Functions can fix a lot of query performance issues.

In the videos below, I’m going to show you how to rewrite T-SQL Scalar User Defined Functions, and a really cool thing they can do on top of just replacing the bad kinds of functions.

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 on 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 performance problems quickly.

Software Vendor Mistakes With SQL Server: Multi Statement Table Valued Functions

Available Units


In T-SQL, there are two kinds of “table valued functions”. One of them returns a select as a derived table (Inline Table Valued Function), and the other returns a @table variable as a result (Multi-Statement Table Valued Functions).

I’ve written before about the problems you’ll run into with T-SQL’s @table variables, so I’m not going to go back over that here.

I will take this time to point out that they don’t behave any better when used in a function than they do when used independent of a function. If you need to stop here to watch that video, go ahead. I’ll be waiting for you.

To learn more about how T-SQL’s Multi-Statement Table Valued Functions can mess up SQL Server query performance, check out the video below. And as always, if this is the kind of thing you love learning about, hit the link below to get 75% off all of my training material.

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 on 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 performance problems quickly.

Software Vendor Mistakes With SQL Server: Scalar User Defined Functions

The Very Worst Idea


Scalar UDFs answered a very good question: How do I ruin query performance so that two generations of consultants can make a living off of SQL Server?

In the videos below, which are part of my paid training, I’m going to show you how T-SQL Scalar UDFs ruin query performance, and one way of fixing them. If you’ve got lots of these little devils hanging around your codebase, you’ll wanna pay close attention, here to see how:

  • T-SQL Scalar UDFs force queries to run single threaded
  • T-SQL Scalar UDFs run once per row that they process
  • T-SQL Scalar UDFs hide all the work they actually do in query plans and other metrics

There’s a ton more available in the full paid training courses, so hit the link below to get 75% off the whole thing.

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 on 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 performance problems quickly.

Be Careful Where You Call Inline Table Valued Functions In SQL Server Queries

Keep’em Comin!


While helping someone tame a bunch of rather unfortunate scalar valued functions, we eventually hit a point where they were able to do some of the rewrites themselves. During testing, they ran into a situation where performance got worse when they made the switch over, and it wasn’t because an Eager Index Spool popped up.

I was able to come up with a demo that shows a reasonable enough performance difference in a couple queries using the same technique as I gave them to fix things.

So uh, here goes that.

Muppet


The query they were tuning had a couple OUTER APPLYs already written into it, and so they added the function on as another.

SELECT
    u.DisplayName,
    b.Name,
    nbi.AvgPostsPerDay
FROM dbo.Users AS u
OUTER APPLY
(
    SELECT 
        CreationDate = 
            MIN(p.CreationDate)
    FROM dbo.Posts AS p
    JOIN dbo.Votes AS v
        ON  v.PostId = p.Id
        AND v.VoteTypeId = 1
    WHERE p.OwnerUserId = u.Id
) AS p
OUTER APPLY
(
    SELECT TOP (1)
        b.Name
    FROM dbo.Badges AS b
    WHERE b.UserId = u.Id
    ORDER BY b.Date DESC
) AS b
OUTER APPLY dbo.no_bueno_inline(u.Id, p.CreationDate) AS nbi
WHERE u.Reputation >= 100000
ORDER BY u.Reputation DESC;

Since they didn’t want to lose rows to the function, they couldn’t use CROSS APPLY. Good enough.

Moutarde


But what they really wanted was to move the function up into the select list, like this:

SELECT
    u.DisplayName,
    b.Name,
    AvgPostsPerDay = 
    (
        SELECT 
            nbi.AvgPostsPerDay 
        FROM dbo.no_bueno_inline
        (
            u.Id, 
            p.CreationDate
        ) AS nbi
    )
FROM dbo.Users AS u
OUTER APPLY
(
    SELECT 
        CreationDate = 
            MIN(p.CreationDate)
    FROM dbo.Posts AS p
    JOIN dbo.Votes AS v
        ON  v.PostId = p.Id
        AND v.VoteTypeId = 1
    WHERE p.OwnerUserId = u.Id
) AS p
OUTER APPLY
(
    SELECT TOP (1)
        b.Name
    FROM dbo.Badges AS b
    WHERE b.UserId = u.Id
    ORDER BY b.Date DESC
) AS b
WHERE u.Reputation >= 100000
ORDER BY u.Reputation DESC;

That way you don’t lose any rows like you could with CROSS APPLY, and the optimizer is more likely to holler at the function later on in the query plan, since the values from it are only being projected — that’s fancy for selected.

Mapperoo


The full query plan is a bit much to untangle quickly in this post, but the timing difference is noticeable enough for my purposes:

SQL Server Query Plan
tootin

So if you ever end up rewriting a scalar valued function as an inline table valued function, make sure you test calling it in the same way. Moving query syntax around may produce logically equivalent results, but won’t always produce equivalent performance.

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 on 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 performance problems quickly.