A Fun Way To Return Zero Rows To SQL Server Management Studio Using ROW_NUMBER

Posted on by Erik Darling

Demos And Demos!

Sending rows out to SSMS is annoying. It takes a long time when there are a lot of them, and sometimes you just wanna show a query plan that does a lot of work without all the blah blah.

And sure, there are other great ways that I talked about in this Q&A.

One other way is a method I used in yesterday’s post.

Let’s be timely and look at that!

Everyone Loves Windowing Functions

They do all sorts of funny stuff. Number things, rank things.

And they make great demos, whether you’re trying to show how to do something cool, or make them perform well.

The funny thing is that the optimizer doesn’t really understand that, unless you intervene, the numbering and ranking doesn’t start at zero.

You can have a whole query run, do a bunch of really hard work, take a long time, and not return any rows, just by filtering to where the function = 0.

Using yesterday’s query as an example, here’s what happens:

SELECT u.Id, 
       u.DisplayName, 
       u.Reputation, 
       u.CreationDate, 
       ca.*
FROM dbo.Users AS u
OUTER APPLY
    (
        SELECT *, 
        DENSE_RANK() 
              OVER( PARTITION BY vs.Id 
                    ORDER BY vs.Id DESC ) AS whatever
        FROM dbo.VotesSkewed AS vs
        WHERE vs.UserId = u.Id
        AND   vs.VoteTypeId BETWEEN 1 AND 5
    ) AS ca
WHERE ca.whatever = 0;

Since I’m generating the rank here on the primary key/clustered index for the table, it’s basically free to do. It’s fully supported by the index.

If you create a different index to support that, e.g. a POC index (Partitioning, Ordering, Covering), you can use that too.

SQL Server Query Plan
FULL SIZE

Bag Of Bones

This query will, of course, return no rows. But it will do all the prerequisite work to generate a result set to filter on. That’s where that filter operator kicks in, and nothing passes through it. It’s a stopping point right before things would actually have to start kicking out to SSMS.

SQL Server Query Plan
less than zero

But of course, the optimizer doesn’t know that until we get there. If it did, we might just end up with a constant scan and a query that finishes instantly.

For example, if you add a silly where clause like this:

SELECT u.Id, 
       u.DisplayName, 
       u.Reputation, 
       u.CreationDate, 
       ca.*
FROM dbo.Users AS u
OUTER APPLY
    (
        SELECT *, 
        DENSE_RANK() 
              OVER( PARTITION BY vs.Id 
                    ORDER BY vs.Id DESC ) AS whatever
        FROM dbo.VotesSkewed AS vs
        WHERE vs.UserId = u.Id
        AND   vs.VoteTypeId BETWEEN 1 AND 5
    ) AS ca
WHERE 1 = (SELECT 0)

You end up with this:

SQL Server Query Plan
O NO A SCAN

That’s That

If you find yourself with a demo that returns a lot of rows, and you don’t want to use a TOP or OFFSET/FETCH to only return some of them, this is a fun way to return nothing.

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

Related Posts