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?

Answer 1

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.