SQL Server performance using ROWCOUNT

I use SET ROWCOUNT 27900 And then select two columns:

Select
   emp.employeeid,
   empd.employeedetailid
From
   employee开发者_开发问答 emp (NOLOCK)
join 
   employeedetail empd (NOLOCK) on emp.employeeid = empd.employeeid 

This query executes in 3 sec

If I use SET ROWCOUNT 27950 then the same query takes 20 sec to execute.

I am not a sql DBA, why there is a difference of 17 sec for just 50 rows. Is this anything related to page size or index?

Can anyone help me to fine tune the query?

Have you tried doing this using TOP instead of SET ROWCOUNT, and adding an ORDER BY?

SELECT TOP 27900 emp.employeeid, ...
ORDER BY ...;

This will give the optimizer a much better chance at optimizing. In simplest terms, SET ROWCOUNT is applied after the entire query has been processed, and only affects the rows that are sent back to the caller...