difference between TOP and SET ROWCOUNT

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 347
TOP and SET ROWCOUNT are both valid methods of limiting the result sets from a query. They are however very different commands. The TOP clause of a command limits that single command, while the SET ROWCOUNT command limits all eligible queries within the connection until another SET ROWCOUNT is called.

SET ROWCOUNT 10 
SELECT * FROM CITY ORDER BY CITY_NAME
SELECT * FROM STATE ORDER BY STATE_CD
SET ROWCOUNT 0


Returns first 10 records from CITY table as well as 10 records from STATE table.

The equivalent code for the above using TOP n clause is as follows:

SELECT TOP 10 * FROM CITY ORDER BY CITY_NAME
SELECT TOP 10 * FROM STATE ORDER BY STATE_CD

Comments or Responses

Login to post response