I had this issue long time ago, I found a workaround which suited me and forgot about it.

But now there's that question on SO so I'm willing to bring this problem up.

There's a view that joins few tables in a very straightforward way (orders + order lines).

When queried without a where clause, the view returns several million lines.
However, noone ever calls it like that. The usual query is

select * from that_nasty_view where order_number = 123456;

This returns about 10 records out of 5m.

An important thing: the view contains a window function, rank(), which is partitioned exactly by the field using which the view is always queried:

rank() over (partition by order_number order by detail_line_number)

Now, if this view is queried with literal parameters in the query string, exactly as shown above, it returns the rows instantly. The execution plan is fine:

  • Index seek on both tables using the indices on order_number (returns 10 rows).
  • Calculating windows over the returned tiny result.
  • Selecting.

However, when the view is called in a parametrized way, things get nasty:

  • Index scan on all tables ignoring indices. Returns 5m rows.
  • Huge join.
  • Calculating windows over all partitions (about 500k windows).
  • Filter to take 10 rows out of 5m.
  • Select

This happens in all cases when parameters are involved. It can be SSMS:

declare @order_number int = 123456;
select * from that_nasty_view where order_number = @order_number;

It can be an ODBC client, such as Excel:

select * from that_nasty_view where order_number = ?

Or it can be any other client that uses parameters and not sql concatenation.

If the window function is removed from the view, it runs perfectly quickly, regardless of whether or not it's queried with parameters.

My workaround was to remove the offending function and reapply it at a later stage.

But, what gives? Is it genuinely a bug in how SQL Server 2008 handles window functions?

  • order_number is primary key? Datatypes of column and parameter match?
    – gbn
    Commented Feb 8, 2012 at 19:08
  • order_number is not a primary key. It is int not null with nonclustered index on it in both tables.
    – GSerg
    Commented Feb 8, 2012 at 19:10
  • 5
    SQL Server 2005 had issues with predicate pushing in this area. I thought they were fixed now. BTW your TSQL example uses a variable not a parameter. Does adding OPTION (RECOMPILE) help? Commented Feb 8, 2012 at 19:20
  • 1
    @GSerg - So on the bad plan with the filter last it has an estimated 5 million rows into the filter and an estimated 10 rows out matching the actual? If so maybe it is that predicate pushing issue still not fixed completely then. Commented Feb 8, 2012 at 19:49
  • 2
    Also see this answer on StackOverflow Commented Mar 10, 2013 at 8:06

2 Answers 2


This appears to be a long standing issue that keeps resurfacing in one form or another and is still present in SQL Server 2012.

Some posts discussing it are

All current versions of SQL Server up to and including 2012 are not able to push the filter on a partitioning group past the sequence project for a parameterised predicate except if option(recompile) is used (if 2008+).

An alternative to the recompile hint would be to rewrite the query to use a parameterised inline TVF as suggested by @a1ex07)


I'd try replacing the view by table-valued udf. That way it will filter records first, and then apply window function. This function may accept table parameter so you can pass multiple order_number into it

  • Yet another workaround, yes. I couldn't do that though because not all clients were able to consume a table-valued function.
    – GSerg
    Commented Feb 8, 2012 at 19:39
  • Why? I'm not 100% sure, but I think all you need is to change query a little to something like SELECT * FROM my_funct(12345)
    – a1ex07
    Commented Feb 8, 2012 at 19:40
  • One of the requirement was the query is consumable by end users using Excel (that is, by MS Query), and MS Query will not let you do that, at least in versions up to 2003.
    – GSerg
    Commented Feb 8, 2012 at 19:47
  • it will filter records first, and then apply window function is incorrect. There is no deterministic order to execution Commented Mar 9, 2013 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.