SET ROWCOUNT is equivalent of LIMIT, but if you want the generic pagination then it's better to write the query this way:
;WITH Results_CTE AS
Col1, Col2, ...,
ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit
The advantage of using the above code is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).
Note: the @Offset parameter can use one-based indexing for this rather than the normal zero-based indexing.