What is the Equivalent syntax of mysql " LIMIT " clause in SQL Server

Question

What is the Equivalent syntax of MySQL " LIMIT " clause in SQL Server . I would like to use it for doing paging of my results. (want to show records5 to 10 )

Answer 1

The closest thing is TOP:

Select top 5 * from tablename

You can get a range ( rows 5 - 10)

SELECT * FROM (
  SELECT TOP n * FROM (
    SELECT TOP z columns      -- (z=n+skip)
    FROM tablename
    ORDER BY key ASC
  )
)

Answer 2

The closest to it is SELECT TOP X but it is only equivalent to LIMIT X.

For LIMIT X, Y, there is no direct MS-SQL equivalent (as far as I know). Christian's solution is a good one though.

MSSQL2005 (onwards) has the ROW_NUMBER syntax which might be useful:
http://msdn.microsoft.com/en-us/library/ms186734%28SQL.90%29.aspx