Of Windowing Functions And Where Clauses

Last Updated 6 years ago

Seen One, Seen’em All

This isn’t about indexing! I promise. In fact, I’ve dropped all my indexes for this. It’s just that, well, I’ve seen this happen with two clients in a row, so maybe it’s time to blog about it.

If you run this query, you get a fairly obvious plan.

WITH nonsense
    AS (   SELECT   *,
                    ROW_NUMBER() OVER ( PARTITION BY v.UserId
                                        ORDER BY v.CreationDate ) AS rn
           FROM     dbo.Votes AS v )
SELECT  *
FROM    nonsense AS n
WHERE   n.VoteTypeId = 5
AND n.rn = 1;
GO

WITH nonsense

AS ( SELECT *,

ROW_NUMBER() OVER ( PARTITION BY v.UserId

ORDER BY v.CreationDate ) AS rn

FROM dbo.Votes AS v )

SELECT *

FROM nonsense AS n

WHERE n.VoteTypeId = 5

AND n.rn = 1;

With no index to help us avoid Sorting, we gotta do some of that, and we need a Filter operator for our WHERE clause.

Since the results of the ROW_NUMBER aren’t persisted anywhere, that’s the only way to narrow the results down to just rn = 1.

But there’s something else being filtered!

This is a perfectly SARGable predicate that we can perfectly push to the Clustered Index.

Why Doesn’t That Happen Here?

Well, in this case, predicate pushdown might give you… ~~incorrect~~ different results.

WITH nonsense
    AS (   SELECT   *,
                    ROW_NUMBER() OVER ( PARTITION BY v.UserId
                                        ORDER BY v.CreationDate ) AS rn
           FROM     dbo.Votes AS v
           WHERE    v.VoteTypeId = 5 )
SELECT  *
FROM    nonsense AS n
WHERE   n.rn = 1;
GO

WITH nonsense

AS ( SELECT *,

ROW_NUMBER() OVER ( PARTITION BY v.UserId

ORDER BY v.CreationDate ) AS rn

FROM dbo.Votes AS v

WHERE v.VoteTypeId = 5 )

SELECT *

FROM nonsense AS n

WHERE n.rn = 1;

This query is logically different. I think you can see why. It may not be obvious in the results at first, but just getting a simple count gives us two slightly different results.

WITH nonsense
    AS (   SELECT   *,
                    ROW_NUMBER() OVER ( PARTITION BY v.UserId
                                        ORDER BY v.CreationDate ) AS rn
           FROM     dbo.Votes AS v )
SELECT  COUNT(*) AS records
FROM    nonsense AS n
WHERE   n.VoteTypeId = 5
AND n.rn = 1;
GO


WITH nonsense
    AS (   SELECT   *,
                    ROW_NUMBER() OVER ( PARTITION BY v.UserId
                                        ORDER BY v.CreationDate ) AS rn
           FROM     dbo.Votes AS v
           WHERE    v.VoteTypeId = 5 )
SELECT  COUNT(*) AS records
FROM    nonsense AS n
WHERE   n.rn = 1;
GO

WITH nonsense

AS ( SELECT *,

ROW_NUMBER() OVER ( PARTITION BY v.UserId

ORDER BY v.CreationDate ) AS rn

FROM dbo.Votes AS v )

SELECT COUNT(*) AS records

FROM nonsense AS n

WHERE n.VoteTypeId = 5

AND n.rn = 1;

WITH nonsense

AS ( SELECT *,

ROW_NUMBER() OVER ( PARTITION BY v.UserId

ORDER BY v.CreationDate ) AS rn

FROM dbo.Votes AS v

WHERE v.VoteTypeId = 5 )

SELECT COUNT(*) AS records

FROM nonsense AS n

WHERE n.rn = 1;

I can hear a lot of you running to go fix code. Don’t worry, this will be here when you get back.

When Can It Happen?

If the column that you’re filtering on in the WHERE clause is in the PARTITION BY clause of your Windowing function, the predicate can be safely pushed.

WITH nonsense
    AS (   SELECT   *,
                    ROW_NUMBER() OVER ( PARTITION BY v.UserId, v.VoteTypeId
                                        ORDER BY v.CreationDate ) AS rn
           FROM     dbo.Votes AS v )
SELECT  *
FROM    nonsense AS n
WHERE   n.VoteTypeId = 5
AND n.rn = 1;
GO

WITH nonsense

AS ( SELECT *,

ROW_NUMBER() OVER ( PARTITION BY v.UserId, v.VoteTypeId

ORDER BY v.CreationDate ) AS rn

FROM dbo.Votes AS v )

SELECT *

FROM nonsense AS n

WHERE n.VoteTypeId = 5

AND n.rn = 1;

This case, the plan shows us the VoteTypeId predicate being applied to the Clustered Index Scan, and the Filter only being used for the rn = 1 predicate.

The same doesn’t work if it’s only in the ORDER BY of the Windowing Function.

This query is logically different from the other two, and by even more (this one only brings back 52,294 records).

It’s not a drop-in replacement for one or the other, it’s just an example of when a predicate like this can be pushed.

Does This Happen Anywhere Else?

Yes, it will also happen in derived tables and views.

You can work around this sort of thing with inline table valued functions, as Paul White notes in this answer on Stack Overflow.

Thanks for reading!

Pop Quiz: Can You Use Your Monitoring Tool?

[Video] Office Hours 2018/02/28 (With Transcriptions)

3 Comments. Leave new

Luis de Santos
March 2, 2018 9:30 am

Trying wrapping that into a view and remove the where clause. You’ll find that moving the where clause into a select from the view it will no longer have the predicate in the clustered index scan part of the query plan. This is an issue I was having with a payroll timecard process that was not solveable until I ditched using the view and instead used a table valued function.

Reply
DW
March 2, 2018 5:05 pm

Sorry, isn’t this whole post basically, “If I ask for 3 different subsets of data, SQL Server will likely get it in 3 different ways?” The “SARGable predicate” is a red herring if it’s not used to create the initial subset of data, right? It’s not really SARGable in that case. Unless you did this with a Temp table instead of a CTE and added the index to the temp table; then it would be SARGable against the Temp table.

Or maybe the real lesson is that you need to fully understand your business question before you decide where you place your filter and partition clauses. “I want the first time a user voted 5” is very different from “I want users whose first vote was 5.”

Reply
- Erik Darling
  March 2, 2018 5:24 pm
  
  DW – Sort of. The results of these queries can look misleadingly correct. This is a fairly trivial example, though. In more complicated queries, it can be much harder to track down logical errors like these.
  
  Thanks!
  
  Reply