RowCount VS TOP - Microsoft SQL Server

wackyphill

Is Set RowCount @RowCount

More efficient than simply using TOP?

Thanks for any input.

Jul 25 '05 #1

Subscribe Reply

11001

Hugo Kornelis

On 25 Jul 2005 11:00:01 -0700, wa********@yaho o.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)

Jul 25 '05 #2

wackyphill

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.

Jul 25 '05 #3

Hugo Kornelis

On 25 Jul 2005 12:29:36 -0700, wa********@yaho o.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)

Jul 25 '05 #4

Erland Sommarskog

(wa********@yah oo.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, es****@sommarsk og.se

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

Jul 25 '05 #5

wackyphill

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.

Jul 25 '05 #6

Similar topics

5205

Using @@rowcount after update in ASP

by: FLORAL | last post by:

One Update statement in my following function UpdateSuccess_Order goes like this: Function UpdateSuccess_Order(ByVal Ord_No, ByVal Ord_NoSuccess, ByRef strError) UpdateSuccess_PAL = "ERROR" Dim SQL, ITZ_RESULT, zRS Dim Tmp, cnt cnt=-999

ASP / Active Server Pages

1775

ADO.NET - rows less than total rowcount

by: Papri Chakravorty | last post by:

hello i have been trying to connect to a table an error keeps coming up in html that the total rows is less than total row count also an error in the datagrid, that the index number is not same as a non-negative count hence a run time erro comes up any help will be appreciated thanks Papri

.NET Framework

7437

Updating a large table: set @@rowcount versus explicit transactions

by: louis | last post by:

When I need to perform an update against multi-million row table I typically specify @@rowcount, to reduce locks. e.g. set @@rowcount 1000 while exists (select * from myTable where col2 is null) update myTable set col2 = col1 + 'blahblah' where col2 is null

Microsoft SQL Server

10829

Can @@ROWCOUNT return NULL?

by: Dimitri Furman | last post by:

SQL Server 2000 SP3. Is it possible for the @@ROWCOUNT function to return NULL after a statement? I am troubleshooting a relatively large stored procedure with multiple SELECT statements and a couple of INSERTs into table variables. Immediately after each statement I save the value returned by @@ROWCOUNT to a local variable. That information eventually is passed back to the client via one output parameter, for all statements in the...

Microsoft SQL Server

23507

Getting rowcount from a stored procedure

by: Richard G | last post by:

I'm a database guy, so go easy on me here. :) How can I get the rowcount of the affected rows of a SQL statement from a stored procedure call? I know that "set nocount on" does not return the number of affected rows to the client, so I would assume that "set nocount off" sends the number of affected rows to the client, and therefore, is available programatically. If so, how to get that rowcount? Thanks, Richard

C# / C Sharp

4205

SqlDataReader - ReturnValue - rowcount

by: Jon Haakon Ariansen | last post by:

Hi, I realize that it's not possible to get the rowcount from SqlDataReader, but if you have a stored procedure where you return @@ROWCOUNT it should be possible to get the rowcount through the returnvalue parameter to sqldatareader, shouldn't it??? Here is an example: SqlDataReader dr = null; SqlConnection sc = new SqlConnection(_conn);

ASP.NET

24938

dataGridView.Rows.Count and datagridview.RowCount

by: SePp | last post by:

Hello all, I want to display the number of items in an datagridview. I use this one datagridview for different datasets. The datagridview.Rowcount works fine for the most of the dataset. But there is one dataset which has more items than the others and the Rowcount wont work. The result is all the time 0, but it should be something like 85.

C# / C Sharp

5724

DataGridView.RowCount setting is slow

by: Andrus | last post by:

I have DataGridView in virtual mode containing 3500 rows. In code below, assigning to RowCount value to 3500 takes 8 seconds. CPU usage goes high at this time. Stepping by F11 into user code shows few celltemplate property getters and combobox/datecombo constructor calls without database access which does not take a lot of time. Debug output (below) shows lot of messages Stepping over non-user code. Running in release mode from...

C# / C Sharp

8588

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8236

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8410

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

5526

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4037

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4103

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2541

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1690

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1407

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General