sql server - Retrieving X rows from an ordered CTE, TOP vs Range -

objective:

want know faster/better performance when trying retrieve finite number of rows cte ordered.

example:

say have cte(intentionally simplified) looks this, , want top 5 rows :

with    cte           (                select  id =  rank() on (order t.actionid asc)                       , t.name                    tblsample t -- tblsample indexed on id                            ) 

which faster:

select top 5 * cte 

or

select * cte id between 1 , 5 ? 

notes:

1. i not db programmer, me top solution seems better once ss finds 5th row, stop executing , "return" (100% assumption) while in other method, feel unnecessarily process whole cte.
2. my question cte, answer question same if table?

the important thing note both queries not going produce same result set. consider following data:

create table #tblsample (actionid int not null, name varchar(10) not null); insert #tblsample values (1,'aaa'),(2,'bbb'),(3,'ccc'); 

both of these produce same result:

with cte  (   select id = rank() on (order t.actionid asc), t.name   #tblsample t ) select top(2) * cte;  cte  (   select id = rank() on (order t.actionid asc), t.name   #tblsample t ) select * cte id between 1 , 2; 

now let's update:

update #tblsample set actionid = 1; 

after update first query still returns 2 rows, second query returns 3. keep in mind that, without order in top query results not guaranteed because there no default order in sql.

with out of way - performs better? depends. depends on indexing, statistics, number of rows, , execution plan sql engine goes with.