Ranking functions inside views

Question

Is it reliable to use ranking functions inside views in sql server (2008 r2) and especially the

over (order by ...)

clause ?

It is known that the

order by

clause inside views in not a good approach, even if you specify the

top (100) percent

What about the

row_number() over(order by myColumn)

Sql server designer complains about it, but I cannot find any reliable info regarding this combination.

score 0 · Accepted Answer · answered Jul 22 '15 at 22:47

Yes row_number works fine in views. The designer only handles limited syntax.

It does not guarantee any particular order for the select from the views though. For that you need an order by in the outer query.

One issue to be aware of is that, if you have a partition by, parameterised predicates on the partitioning column won't be pushed down past that even though it would be more efficient to do so. And for that reason a parameterised inline table valued function may be preferred.

Thanks Martin! Good answer! – George Mavritsakis Jul 22 '15 at 23:21 — George Mavritsakis, Jul 22 '15 at 23:21

Ranking functions inside views

1 Answers1