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
  )
)