How top actually works
- From: Gints Plivna <gints.plivna@xxxxxxxxx>
- Date: Wed, 16 Jan 2008 14:59:49 -0800 (PST)
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
.
- Follow-Ups:
- Re: How top actually works
- From: Gert-Jan Strik
- Re: How top actually works
- From: David Portas
- Re: How top actually works
- Prev by Date: Re: Need Help Importing Data from Excel to My SQL Database
- Next by Date: Re: How top actually works
- Previous by thread: duplicate checking
- Next by thread: Re: How top actually works
- Index(es):
Relevant Pages
|
Loading
