Is there an equivalent MYSQL "LIMIT" Clause in SQL Server?

Question

My pagination code in MySQL returns the query with a LIMIT (SELECT * FROM tableTest LIMIT $start, $display) to display the current rows.

Is there a way to have this logic in SQL Server?

I think this topic explain all - http://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server — Kostia Shiian, Feb 13 '15 at 06:27

score 1 · Accepted Answer · answered Feb 13 '15 at 06:27

In MSSQL you need to use FETCH-OFFSET

<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

From MSDN

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.

You should mention that `offset fetch` does not work for versions before 2012. — shree.pat18, Feb 13 '15 at 06:29

Is there an equivalent MYSQL "LIMIT" Clause in SQL Server?

1 Answers1