Beware of the Usage of SET ROWCOUNT
This is used to set the number of rows affected for DML
Declare
@test table(number int)
set
rowcount 1
insert
into @test
select 11 union all select 10
set rowcount 0
select
number from @test
The above inserts only one row to the table variable @test
But beware that this wont be supported in Future versions of SQL Server
Here is the Remarks from BOL, SQL Server help file
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.