Posted by Gert-Jan Strik on 09/30/90 12:00

Gints,

"How top actually works" cannot be determined with a table with only 9
rows in 48 KB.

I ran your example query

select * from (
select top 2 * from t3
) as q
order by data asc

and replaced "t3" with a 65 million row table (18 GB). It would complete
with only 4 logical reads. When I replaced "top 2" with "top 10" in the
inner query, it would complete after 4 logical reads.

Your table is so small, that even the simplest query plan is so cheap
that the optimizer seems to consider it useless to search for anything
better.

--
Gert-Jan


Gints Plivna wrote:
>
> I'm coming from Oracle world and trying to find something similar to
> rownum in Oracle. I know there exists TOP which normally if used in
> the same select woth order by firstly sorts data and then only gets
> top n. So the question is what actually happens when top is used in
> inner query and order by in outer query. The problem is that it seems
> to be somehow inconsistent at least for the first sight.
>
> Using SQL Server 2005
> So I have following test case:
>
> create table t3 (id integer, data varchar(4000));
> insert into t3 values (1, replicate('a', 4000));
> insert into t3 values (2, replicate('b', 4000));
> insert into t3 values (3, replicate('c', 4000));
> insert into t3 values (4, replicate('d', 4000));
> insert into t3 values (5, replicate('e', 4000));
> insert into t3 values (6, replicate('f', 4000));
> insert into t3 values (7, replicate('g', 4000));
> insert into t3 values (8, replicate('h', 4000));
> insert into t3 values (9, replicate('i', 4000));
>
> SET STATISTICS IO ON
> firstly just select all rows to know how many logical reads are needed
> for all table.
>
> select * from t3
> 1 aaa...
> ...
> 9 iiii....
> logical reads 5
>
> Now get first two rows without any where clause:
> select top 2 * from t3
> 1 aaa...
> 2 bbb...
> logical reads 1
>
> Now the same first two rows just with outer select without any order
> by:
> select * from (
> select top 2 * from t3
> ) as q
> 1 aaa...
> 2 bbb...
> logical reads 1
>
> OK till now it's as expected, just one logical read get first 2 rows
> and end query.
> However look at next query's logical reads 5. This somehow is very
> interestingly equal to logical reads for select all rows from t3.
>
> select * from (
> select top 2 * from t3
> ) as q
> order by data asc
> 1 aaa...
> 2 bbb...
> logical reads 5
>
> So the next one shows that order by clause has affected the result set
> and actually semms to be pushed into inner query. Also logical reads
> are 5 meaning that actually we have scanned all the table.
>
> select * from (
> select top 2 * from t3
> ) as q
> order by data desc
> 9 iii..
> 8 hhh...
> logical reads 5
>
> However for TOP 1 everything works in a different way i.e. there is
> always the same one row and the same one logical read in spite of
> diffferent order by clauses:
>
> select * from (
> select top 1 * from t3
> ) as q
> order by data asc
> 1 aaa....
> logical reads 1
>
> select * from (
> select top 1 * from t3
> ) as q
> order by data desc
> 1 aaa....
> logical reads 1
>
> So where is the truth? Why the functionality is different?
>
> The business case is that we have search with potentially weak user
> criteria resulting in BIG potential result sets, but we want to show
> the user just ANY N rows satisfying criteria. But these N rows should
> be ordered. So what I'd like to achieve is:
> 1) get ANY no more than N rows according to my criteria
> 2) sort these N rows according to my order by clause.
>
> I DEFINITELY don't want:
> 1) get ALL rows
> 2) sort them and throw away all but first N.
>
> TIA, Gints

[Back to original message]