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.
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.
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:
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!