RowCount VS TOP

Is Set RowCount @RowCount

More efficient than simply using TOP?

Thanks for any input.

On 25 Jul 2005 11:00:01 -0700, wackyphill@yahoo.com wrote:

>Is Set RowCount @RowCount
>
>More efficient than simply using TOP?
>
>Thanks for any input.

Hi wackyphill,

Depends. They have different characteristics.

SET ROWCOUNT:
- takes a variable as well as a constant,
- affects ALL future queries, until another SET ROWCOUNT is executed,
- affects only the end result of the complete query.

TOP:
- takes only a constant,
- affects only the current query,
- may be used to limit the number of rows in a subquery.

If you want to limit output from all your queries to twenty rows for
testing and debugging purposes, SET ROWCOUNT is easily the best: just
issue the command once, then run all your queries without the need to
change. If you want to limit the output to a number determined at
runtime, SET ROWCOUNT wins as well - pop the value in a variable, then
run SET ROWCOUNT @NewLimit.

On the other hand, if each query needs another limit, TOP is more
efficient since you'd otherwise have to run a SET ROWCOUNT between all
your queries. And if you're trying to find the salesmen that are NOT
amongst the 10 best sellers, SET ROWCOUNT can't be used at all, whereas
TOP can.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

OK, that's interesting. So, given a single query, do they both have the
server run through all records and then return the first say 10 rows or
does one or both have a way of short circuiting the process after it
found 10 matches?

See, I know they both help w/ network traffic but I didn't know if one
had the server do more/less work.

Thanks for your help.

On 25 Jul 2005 12:29:36 -0700, wackyphill@yahoo.com wrote:

>OK, that's interesting. So, given a single query, do they both have the
>server run through all records and then return the first say 10 rows or
>does one or both have a way of short circuiting the process after it
>found 10 matches?
>
>See, I know they both help w/ network traffic but I didn't know if one
>had the server do more/less work.
>
>Thanks for your help.

Hi wackyphill,

My *guess* is that they'll be executed the same.

If you want to *know*, then run both (for your queries, using your
tables on your hardware - as these factors might all influence the
result), and compare execution plans.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

(wackyphill@yahoo.com)  writes:
> OK, that's interesting. So, given a single query, do they both have the
> server run through all records and then return the first say 10 rows or
> does one or both have a way of short circuiting the process after it
> found 10 matches?

That's more likely to happen with TOP. But it depends on the query.
If you say:

SELECT TOP 20 * FROM tbl

SQL Server will only read the first 20 rows it finds, and that's that.

But if you say

SELECT TOP 20 * FROM tbl ORDER BY non_indexed_col

SQL Server will have to read the entire table, sort it, and pick the
first 20 rows according to the ORDER BY.

The same applies to SET ROWCOUNT, but I'm not really sure that SQL Server
looks at the actual value for SET ROWCOUNT, but makes some standard
assumption. This standard assumption can be a low number, though, in which
case you are likely to the same query plan as TOP.

I had a horror story once, where one component in our system produced
SET ROWCOUNT 7899808, yeah that's right an 7-digit number. (The
component was written in C++, using the ODBC API, and there was an
ODBC call that for some reason produced this.) This caused one
particular query in an essential stored procedure to get a different
query plan - and a bad one. And this happened just a few days before
an important customer were to go live.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

OK, thanks very much guys. I guess there's no reason I should sweat the
difference then except for the behavioral differences mentioned above.
I just wanted to be sure.

Thanks again for your time.