TOP N ROWS - DOUBT ABOUT HOW IT EXECUTES

Question

TOP N ROWS - DOUBT ABOUT HOW IT EXECUTES

ColdCoffee

SSC-Dedicated

Points: 39972
More actions
April 16, 2010 at 3:18 am

#231856

Hello all,
Probabaly this question might seem very novice, but was hanging in my mind for some time..
Assume i use
SELECT TOP N * FROM TABLE_A join TABLE_B
The question is:
Will the engine filter the result set with N rows as soon as the rows start appearing in the result set
or
will the engine first find out all the rows (say N+X rows) producing out of that join and then outputs TOP N rows?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Looking at the exec plan, you will see that the TOP operator is applied as last.

Looking at the estimated row counts, you'll see that it can actually be applied as first, if no ORDER BY clause is present.

Hope this helps

Gianluca

-- Gianluca Sartori

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Thanks Gianluca.. what if i dont have a order by clause? still the top operator is processed only after getting the result set?

Do u think SET ROWCOUNT will be better than TOP in this scenario?

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

The in-memory representation will contain every record from the tabel and then display only TOP N records.This is how TOP works.

Still would advice to use @@ROWCOUNT to avoid un-necessary records to scan

Raunak J

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Thanks Mate!!

Can u provide me a sample of using @@ROWCOUNT to filter the result set?

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

COldCoffee (4/16/2010)
Thanks Gianluca.. what if i dont have a order by clause? still the top operator is processed only after getting the result set?
Do u think SET ROWCOUNT will be better than TOP in this scenario?

SET ROWCOUNT has the same effect as TOP on the query plan. The only benefit I can see is that you can apply it to client queries without changing the query text.

-- Gianluca Sartori

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 6

Sure,

SET ROWCOUNT 10

--Followed by SQL squery...the query execution will stop once 10 records are scanned

Raunak J

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

Raunak Jhawar (4/16/2010)
SET ROWCOUNT 10

Oh ok, u were talking about SET ROWCOUNT only!! OK, i thot u were mentioning about @@ROWCOUNT 😉

Thanks for your time and reply guys... I will tail-gate your replies 😀