Top Versus Set Rowcount

TOP versus SET ROWCOUNT

Yes, functionally they are the same thing. As far as I know there are no significant performance differences between the two.

Just one thing to note is that once you have set rowcount this will persist for the life of the connection so make sure you reset it to 0 once you are done with it.

---

EDIT (post Martin's comment)

The scope of SET ROWCOUNT is for the current procedure only. This includes procedures called by the current procedure. It also includes dynamic SQL executed via EXEC or SP_EXECUTESQL since they are considered "child" scopes.

Notice that SET ROWCOUNT is in a BEGIN/END scope, but it extends beyond that.

create proc test1
as
begin
    begin
    set rowcount 100
    end
    exec ('select top 101 * from master..spt_values')
end
GO

exec test1
select top 102 * from master..spt_values

Result = 100 rows, then 102 rows

SQL Rowcount vs Top

Top can do a few more things for you. For one, you can specify a percentage, instead of an integer. You can also handle situations where ties in column values occur.

http://technet.microsoft.com/en-us/library/ms189463.aspx

Difference between top clause and set rocount

top and set rowcount are very similar, but top has a few restrictions and limitations that set rowcount does not.

From the Sybase Reference Manual: Commands

top unsigned_integer
is used with select...into statements to limit the number of rows inserted in the target table. This is different from set rowcount, which is ignored during a select...into.

•When used with delete, update, or in a view, you cannot specify ordering. If there is an implied order on the table from a clustered index, that order applies; otherwise, the results are unpredictable, as they can be in any order.

•When used with cursors, top n limits the overall size of the result set. Specifying set cursor rowcount limits the results of a single fetch.

•When a view definition contains select top n and a query with a where clause uses it, the results may be inconsistent.

Why does TOP or SET ROWCOUNT make my query so slow?

Apparently my index or the index statistics on Table2.rowId weren't fresh and the query plan for the TOP/SET ROWCOUNT queries to perform poorly.

I reorganized/rebuilt the index and the query performance for the TOP/SET ROWCOUNT queries was greatly improved.

Is it safe to use SET ROWCOUNT?

Rowcount is specific to your current scope, so you are safe there. However, Books Online tells me this (which may or may not affect your needs):

Using SET ROWCOUNT will not affect
DELETE, INSERT, and UPDATE statements
in the next release of SQL Server. Do
not use SET ROWCOUNT 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.
For more information, see DELETE
(Transact-SQL), INSERT (Transact-SQL),
or UPDATE (Transact-SQL).

TOP can use variables too and now can be used in INSERT,UPDATE and DELETE statments. (Hey I learned something new today.) Look up how to use TOP with variables in Books online.

Set Rowcount affecting embedded scalar function

SET ROWCOUNT will affect the rows returned by both the outer SELECT statement and the outer level of any SELECT statements contained in the scalar UDF.

So this isn't possible through use of SET ROWCOUNT alone unless you can change the definition of the Scalar UDF.

I assume that you are using the undocumented and un guaranteed variable assignment approach. If you change to the XML PATH approach then as the top most SELECT only returns 1 row anyway it will remain unaffected by the SET ROWCOUNT 1 command.

Example Code

    USE tempdb;
GO
    CREATE FUNCTION dbo.Concat1(@number INT = 0)
    RETURNS NVARCHAR(max)
    AS
      BEGIN
          DECLARE @Result NVARCHAR(max)

          SELECT @Result = COALESCE(@Result + ',','') + name
          FROM   master..spt_values
          WHERE  number = @number

          RETURN @Result
      END
GO
    CREATE FUNCTION dbo.Concat2(@number INT = 0)
    RETURNS NVARCHAR(max)
    AS
      BEGIN
          RETURN
            (SELECT STUFF((SELECT ',' + name AS [text()]
                           FROM   master..spt_values
                           WHERE  number = @number
                           FOR XML PATH('')), 1, 1, ''))
      END
GO
    SET ROWCOUNT 1;

    SELECT dbo.Concat1(number) AS Concat1,
           dbo.Concat2(number) AS Concat2
    FROM   master..spt_values
    WHERE  number = 1

    DROP FUNCTION dbo.Concat1, dbo.Concat2 

Results

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Concat1 |                                                                              Concat2                                                                               |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rpc     | rpc,yes,autoclose,published,WINDOWS/NT,trusted,ignore duplicate keys,binary,varbinary,primary,NULL,Xact,NUL,GRANT,system table,disable_def_cnst_check,default disk |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Scope of Set rowcount in SQL

Contrary to the accepted answer in the question linked to in the comments as far as I can see the scope rules for this are exactly the same as those for #temp table visibility. It propagates to child batches but when the batch exits it gets reset to the previous value.

CREATE PROC #bar
AS
SELECT * FROM sys.objects
EXEC ('SELECT * FROM sys.objects')
GO

CREATE PROC #foo
AS
SET ROWCOUNT 1

EXEC #bar
GO

SET ROWCOUNT 4
EXEC #foo /*Returns 2 resultsets with 1 row*/
EXEC #bar /*Returns 2 resultsets with 4 rows*/

DROP PROC #foo
DROP PROC #bar

Anyway to get a value similar to @@ROWCOUNT when TOP is used?

you'll have to run another COUNT() query:

SELECT TOP 5 
* 
FROM Person 
WHERE Name LIKE 'Sm%'
ORDER BY ID DESC

DECLARE @r int
SELECT
@r=COUNT(*)
FROM Person 
WHERE Name LIKE 'Sm%'

select @r

---

Related Topics