Of Windowing Functions And Where Clauses

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.

Carpaccio

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!

WHY clause

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.

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.

Off by one!

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.

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.

LORELEIIIIIIIIIIII

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!

Previous Post
Pop Quiz: Can You Use Your Monitoring Tool?
Next Post
[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
  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}