Difference between SET ROWCOUNT & Top

Difference between SET ROWCOUNT & Top

Lets see the differences between SET ROWCOUNT & Top in SQL Server:

Below tables are used:

Table A:

A
1
2
3
4

Table B:

B
3
4
5
6

1. Example of 'Set Rowcount' & 'Top': Take a look at an example first so as to get a feel of what Set Rowcount and Top exactly do:

Let's first take a look at SET ROWCOUNT:

Set Rowcount 2
Select * from A

Result:
A
1
2

Now similarly run query using Top:

Select Top 2 *
from A

Result:
A
1
2

2. 'Set Rowcount' & 'Top' during SQL connection

Set Rowcount persists for the life of the SQL connection.

Set Rowcount 2
Select * from A

Result:
A
1
2

Now after above query, run this below query:
Select * from B

Result:
B
3
4

Notice that now without 'Set Rowcount 2' statement, this second query is also returning first 2 rows only. Not only this but see how this 'Set Rowcount' statement overpowers 'Top' also.

Set Rowcount 2
Select * from A

Result:
A
1
2

Now run below query:
Select Top 3 *
from A

Result:
A
1
2

As you can see that although you wanted 'Top 3' rows but it returned only first two, because 'Set Rowcount 2' still persists.

Now do 'Set Rowcount 0' and then again run:

Select Top 3 *
from A

Result:
A
1
2
3

On the other hand 'Top' only affects the query you are running.

3. Scope of 'Set Rowcount' & 'Top': Using these in a procedure.

SET ROWCOUNT's scope is current procedure only.

Create proc proc_1
as
begin
    set rowcount 2
    exec ('select top 3 * from A')
end
GO

Exec proc_1

Result:
A
1
2

Now Run:
Select Top 3 *
from A

Result:
A
1
2
3

Again I would say that 'Top' only affects the query you are running.

4. Using variables with 'Set Rowcount' & 'Top':

Using variables with Set Rowcount:
declare @rc int
set @rc=1

Set Rowcount @rc
Select * from A

Result:
A
1

Using variables with Top:
declare @rc int
Set @rc=2
Select Top (@rc) * from A

Result:
A
1
2