ROWCOUNT & TOP

`Just came across with this scenario. What if you are using both ROWCOUNT & TOP together in one T-SQL statement.

  1: DECLARE @rowcnt int = 100
  2: SET ROWCOUNT @rowcnt
  3: 
  4: SELECT Top 105 * FROM Sales.SalesOrderHeader

 

This will return 100 rows and easily you can come to a conclusion that ROWCOUNT will override TOP. Well, let us examine another scenario.

  1: 
  2: DECLARE @rowcnt int = 100
  3: SET ROWCOUNT @rowcnt
  4: 
  5: SELECT Top 5 * FROM Sales.SalesOrderHeader
  6: 

This will return only 5!!.

So the rule is,

SET ROWCOUNT overrides the SELECT statement TOP keyword if the ROWCOUNT is the smaller value.