SQL ENQUIRY In how to Get defined number of records

Question

I have a select statement, retrieve about 1000 record I want to modify it to return only some records defined by @startIndex and @count e.g. : If I said @startIndex=20 and @count=20 the result will be : from the 21th record to 40th
I try to make it, but it take the same time as if I retrieve the 1000 record

what is the best way to do that

Answer 1

This is typically called Paging, there are lots of samples if you google for 'sql server paging', for example this MSDN blog post.

Answer 2

WITH data AS (SELECT DISTINCT name FROM sysobjects),
     ranked AS (SELECT ROW_NUMBER() OVER (ORDER BY name) rownum, * FROM data)
SELECT * FROM ranked 
WHERE rownum BETWEEN @startIndex + 1 AND @startIndex + @count 
ORDER BY rownum