Retrieving or Limiting the First N Records from a SQL Query

In SQL server 2005, SET ROWCOUNT n has the same behavior as SQL server 2000. It’s recommended to use TOP (n) instead of SET ROWCOUNT n.But in SQL server 2008 you are able to use variable at place of n in TOP (n).

Example:
for RowCount
DECLARE @RowsCount INT
SELECT @RowsCount = 0

SET ROWCOUNT @RowsCount
SELECT * FROM MyTable
SET ROWCOUNT 0

for TOP (n)

DECLARE @RowsCount INT
SELECT @RowsCount = 0

SELECT TOP (@RowsCount ) * FROM MyTable