SQL ENQUIRY In how to Get defined number of records

I have a select statement, retrieve about 1000 record 开发者_StackOverflow社区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

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

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