November 15, 2002 at 1:13 pm
Does this have to read the whole table or only the top?
select top 1000 place_of_svc from medical_Claims where place_of_svc is not null
November 15, 2002 at 1:20 pm
Only the top 1000 rows (which are not null)
November 15, 2002 at 2:37 pm
it might be worth mentioning that these top 1000 records will be selected by the heap order (order of insertion), unless the table has a clustered index, in which case they will be the first according to that clustered index.
November 15, 2002 at 2:41 pm
though by ANSI standard, the order of the rows returned is not guarenteed. If you want a particular 1000 rows, include an order by (which might force a table scan.
Steve Jones
November 15, 2002 at 4:49 pm
I believe TOP is a short cirsuit function i.e once the criteria is satisfied it finishes. This is why TOP is better than SET ROWCOUNT.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 15, 2002 at 10:17 pm
OK, I'm going to show my ignorance here. I guess I just never thought about it and I suppose it would be easy enough to test, but does anyone know off the top of their heads.
If you use the top 1000 in a query that has correlated subqueries, the top 1000 only effects the outermost recordset involved, right? In other words, the subqueries would still be based on full recordsets. And are there issues with that. Taking it a little further, if you used top 1000 in each of the subqueries doing order by's by primary key, and criterialized the results to a particular range this way, would it be effective in preventing hot spots caused by heap inserts. What would be the issues, if anyone knows?
November 16, 2002 at 5:18 am
it depends on your subquery, if you use a derived view then it will probably evaluate the whole or the derived view, but if you use a subquery in the select or where clause it will only be evaluated for each row returned.
I believe the optimiser has some ways of reduceing the number of reads during a TOP but I am not 100% what.
Have a look at to prove the first statement
set statistics io on
go
select c.* , o.name
from sysobjects o
join (select id , max(colid) max_colid
from syscolumns
group by id )c on c.id = o.id
go
select top 10 c.max_colid , o.name
from sysobjects o
join (select id , max(colid) max_colid
from syscolumns
group by id )c on c.id = o.id
order by name
go
select top 10 (select max(colid) max_colid
from syscolumns c
where c.id = o.id)
, o.name
from sysobjects o
order by name
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 16, 2002 at 11:17 am
yea, I was curious enough to run some tests myself after I thought about it some more. There are some quite dramatic differences bewtween the top statement and setting rowcount too that are subtle until you start looking at the execution plans and resultsets as well. I'm still playing with it, but I'll post anything interesting I find out. I hadn't done anything with aggregates so far, and your query has me looking at some different ideas, thanks simon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply