how to select first N rows from a table in T-SQL?

Question

Is there any way to select, for example, first 10 rows of a table in T-SQL (working MSSQL)?
I think I saw something in Oracle defined as rownum meta variable, used in a following way

 select * from Users where rownum<=10 

But what about MSSQL?

Answer 1

select top(@count) * from users 

If @count is a constant, you can drop the parentheses:

select top 42 * from users 

(the latter works on SQL Server 2000 too, while the former requires at least 2005)

Answer 2

You can use Microsoft's row_number() function to decide which rows to return. That means that you aren't limited to just the top X results, you can take pages.

SELECT *  FROM (SELECT row_number() over (order by UserID) AS line_no, *        FROM dbo.User) as users WHERE users.line_no < 10 OR users.line_no BETWEEN 34 and 67 

You have to nest the original query though, because otherwise you'll get an error message telling you that you can't do what you want to in the way you probably should be able to in an ideal world.

Msg 4108, Level 15, State 1, Line 3 Windowed functions can only appear in the SELECT or ORDER BY clauses.