Posted by dave on 07/07/05 15:41

"AK" <steven.kuzine@gmail.com> wrote in message
news:1120662133.422618.170300@g44g2000cwa.googlegroups.com...
> this approach can result in poor performance. just think: even if there
> are 2 indexes on postcode and another one on address, the optimizer
> cannot use neither index to get ordered results, because it must come
> up with one generic plan, and it does not know at compile time how to
> order the results.
>
> this might perform better:
>
> if @SortOrder = 'postcode'
> then begin
> select ...
> order by address.postcode
> end
> else
> begin
> select ...
> order by address
> end
>
> Even if don't see any difference when you run it agaist a small test
> data set with no indexes, the difference in production could be dramatic
>

thanks steven (?). makes sense so i'll look at writing that approach into
all of the SP's where i use a non-static ORDER BY.

[Back to original message]