SQL, select Nth to Mth record from a table, which query is more efficient?

Question

An interview question:

write SQL Server query to return 30th to 40th record of a table A

my answer:

select top 10 * from (select top 40 * from tb desc) asc

select top 40 * from A where id not in(select top 30 id from A) 

which of above 2 is more efficient? why?

Answer 1

Don't use either of those, you can select those rows 30-40 somewhat more directly

See: http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server

Answer 2

Using Row_number() is probably the best way to do this !

;With CTETable AS 
( 
  SELECT ROW_NUMBER() OVER (ORDER BY Column_Name DESC) AS ROW_NUM, * FROM tb WHERE <CONDITION> 
) 

SELECT Column_List FROM CTETable WHERE ROW_NUM BETWEEN <StartNum> AND <EndNum>