Limit Function in MSSQL

In Microsoft SQL Server, the LIMIT function is not directly available. Instead, SQL Server provides the TOP clause to achieve similar functionality. The TOP clause is used to limit the number of rows returned by a query. Here's an example of how to use it:

Let's say you have a table called employees with columns employee_id, first_name, and last_name, and you want to retrieve the first 5 employees ordered by employee_id:

SELECT TOP 5 *

FROM employees

ORDER BY employee_id;

In this query:

- TOP 5 specifies that you want to retrieve the first 5 rows from the result set.

- * selects all columns from the employees table.

- FROM employees specifies the table from which to retrieve the data.

- ORDER BY employee_id ensures that the results are sorted by the employee_id column before applying the row limiting condition.

This query will return the first 5 rows from the employees table, sorted by employee_id.

You can also use the ORDER BY clause with the TOP clause to sort the result set in a specific order before applying the row limiting condition.

If you want to skip a certain number of rows and fetch the next set of rows, you can combine TOP with OFFSET and FETCH as of SQL Server 2012:

SELECT *

FROM employees

ORDER BY employee_id

OFFSET 5 ROWS -- skip the first 5 rows

FETCH NEXT 5 ROWS ONLY; -- fetch the next 5 rows

This query skips the first 5 rows and then fetches the next 5 rows from the employees table, sorted by employee_id.