LIMIT 10..20 in SQL Server

Use all SQL server:
;with tbl as (SELECT ROW_NUMBER() over(order by(select 1)) as RowIndex,* from table)
select top 10 * from tbl where RowIndex>=10

SELECT * FROM users WHERE Id Between 15 and 25


it will print from 15 to 25 as like limit in MYSQl

If you are using SQL Server 2012+ vote for [Martin Smith's answer][1] and use the `OFFSET` and `FETCH NEXT` extensions to `ORDER BY`,

If you are unfortunate enough to be stuck with an earlier version, you could do something like this,

WITH Rows AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dbo].[SomeColumn]) [Row]
, *
FROM
[dbo].[SomeTable]
)
SELECT TOP 10
*
FROM
Rows
WHERE Row > 10

I believe is functionaly equivalent to

SELECT * FROM SomeTable LIMIT 10 OFFSET 10 ORDER BY SomeColumn

and the best performing way I know of doing it in TSQL, before MS SQL 2012.

---

If there are very many rows you may get better performance using a temp table instead of a CTE.


[1]:

[To see links please register here]

Just for the record solution that works across most database engines though might not be the most efficient:

Select Top (ReturnCount) *
From (
Select Top (SkipCount + ReturnCount) *
From SourceTable
Order By ReverseSortCondition
) ReverseSorted
Order By SortCondition

Pelase note: the last page would still contain ReturnCount rows no matter what SkipCount is. But that might be a good thing in many cases.

The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:

;WITH Results_CTE AS
(
SELECT
Col1, Col2, ...,
ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
FROM Table
WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

Unfortunately, the `ROW_NUMBER()` is the best you can do. It's actually more correct, because the results of a `limit` or `top` clause don't really have meaning without respect to some specific order. But it's still a pain to do.

**Update:** Sql Server 2012 adds a `limit` -like feature via [OFFSET and FETCH keywords][1]. This is the ansi-standard approach, as opposed to `LIMIT`, which is a non-standard MySql extension.


[1]:

[To see links please register here]

You *could* use `LEAD(expression,offset)` , but this doesn't work with `SELECT *`. In addition to forcing your column collection to be verbose, this method will also end up with the potential to return more rows than there are results - filling in not-found rows with default values such as `NULL`.

```lang-sql

SELECT TOP(5)
LEAD( d.name , 5 ) OVER( ORDER BY d.name ASC ) [name]
, LEAD( d.database_id , 5 ) OVER( ORDER BY d.name ASC ) [database_id]
, LEAD( d.source_database_id , 5 ) OVER( ORDER BY d.name ASC ) [source_database_id]
, LEAD( d.owner_sid , 5 ) OVER( ORDER BY d.name ASC ) [owner_sid]
, LEAD( d.create_date , 5 ) OVER( ORDER BY d.name ASC ) [create_date]
, LEAD( d.compatibility_level , 5 ) OVER( ORDER BY d.name ASC ) [compatibility_level]
, LEAD( d.collation_name , 5 ) OVER( ORDER BY d.name ASC ) [collation_name]
, LEAD( d.user_access , 5 ) OVER( ORDER BY d.name ASC ) [user_access]
, LEAD( d.user_access_desc , 5 ) OVER( ORDER BY d.name ASC ) [user_access_desc]
, LEAD( d.is_read_only , 5 ) OVER( ORDER BY d.name ASC ) [is_read_only]
, LEAD( d.is_auto_close_on , 5 ) OVER( ORDER BY d.name ASC ) [is_auto_close_on]
, LEAD( d.is_auto_shrink_on , 5 ) OVER( ORDER BY d.name ASC ) [is_auto_shrink_on]
, LEAD( d.state , 5 ) OVER( ORDER BY d.name ASC ) [state]
, LEAD( d.state_desc , 5 ) OVER( ORDER BY d.name ASC ) [state_desc]
, LEAD( d.is_in_standby , 5 ) OVER( ORDER BY d.name ASC ) [is_in_standby]
, LEAD( d.is_cleanly_shutdown , 5 ) OVER( ORDER BY d.name ASC ) [is_cleanly_shutdown]
, LEAD( d.is_supplemental_logging_enabled , 5 ) OVER( ORDER BY d.name ASC ) [is_supplemental_logging_enabled]
, LEAD( d.snapshot_isolation_state , 5 ) OVER( ORDER BY d.name ASC ) [snapshot_isolation_state]
, LEAD( d.snapshot_isolation_state_desc , 5 ) OVER( ORDER BY d.name ASC ) [snapshot_isolation_state_desc]
, LEAD( d.is_read_committed_snapshot_on , 5 ) OVER( ORDER BY d.name ASC ) [is_read_committed_snapshot_on]
, LEAD( d.recovery_model , 5 ) OVER( ORDER BY d.name ASC ) [recovery_model]
, LEAD( d.recovery_model_desc , 5 ) OVER( ORDER BY d.name ASC ) [recovery_model_desc]
, LEAD( d.page_verify_option , 5 ) OVER( ORDER BY d.name ASC ) [page_verify_option]
, LEAD( d.page_verify_option_desc , 5 ) OVER( ORDER BY d.name ASC ) [page_verify_option_desc]
, LEAD( d.is_auto_create_stats_on , 5 ) OVER( ORDER BY d.name ASC ) [is_auto_create_stats_on]
, LEAD( d.is_auto_create_stats_incremental_on , 5 ) OVER( ORDER BY d.name ASC ) [is_auto_create_stats_incremental_on]
, LEAD( d.is_auto_update_stats_on , 5 ) OVER( ORDER BY d.name ASC ) [is_auto_update_stats_on]
, LEAD( d.is_auto_update_stats_async_on , 5 ) OVER( ORDER BY d.name ASC ) [is_auto_update_stats_async_on]
, LEAD( d.is_ansi_null_default_on , 5 ) OVER( ORDER BY d.name ASC ) [is_ansi_null_default_on]
, LEAD( d.is_ansi_nulls_on , 5 ) OVER( ORDER BY d.name ASC ) [is_ansi_nulls_on]
, LEAD( d.is_ansi_padding_on , 5 ) OVER( ORDER BY d.name ASC ) [is_ansi_padding_on]
, LEAD( d.is_ansi_warnings_on , 5 ) OVER( ORDER BY d.name ASC ) [is_ansi_warnings_on]
, LEAD( d.is_arithabort_on , 5 ) OVER( ORDER BY d.name ASC ) [is_arithabort_on]
, LEAD( d.is_concat_null_yields_null_on , 5 ) OVER( ORDER BY d.name ASC ) [is_concat_null_yields_null_on]
, LEAD( d.is_numeric_roundabort_on , 5 ) OVER( ORDER BY d.name ASC ) [is_numeric_roundabort_on]
, LEAD( d.is_quoted_identifier_on , 5 ) OVER( ORDER BY d.name ASC ) [is_quoted_identifier_on]
, LEAD( d.is_recursive_triggers_on , 5 ) OVER( ORDER BY d.name ASC ) [is_recursive_triggers_on]
, LEAD( d.is_cursor_close_on_commit_on , 5 ) OVER( ORDER BY d.name ASC ) [is_cursor_close_on_commit_on]
, LEAD( d.is_local_cursor_default , 5 ) OVER( ORDER BY d.name ASC ) [is_local_cursor_default]
, LEAD( d.is_fulltext_enabled , 5 ) OVER( ORDER BY d.name ASC ) [is_fulltext_enabled]
, LEAD( d.is_trustworthy_on , 5 ) OVER( ORDER BY d.name ASC ) [is_trustworthy_on]
, LEAD( d.is_db_chaining_on , 5 ) OVER( ORDER BY d.name ASC ) [is_db_chaining_on]
, LEAD( d.is_parameterization_forced , 5 ) OVER( ORDER BY d.name ASC ) [is_parameterization_forced]
, LEAD( d.is_master_key_encrypted_by_server , 5 ) OVER( ORDER BY d.name ASC ) [is_master_key_encrypted_by_server]
, LEAD( d.is_query_store_on , 5 ) OVER( ORDER BY d.name ASC ) [is_query_store_on]
, LEAD( d.is_published , 5 ) OVER( ORDER BY d.name ASC ) [is_published]
, LEAD( d.is_subscribed , 5 ) OVER( ORDER BY d.name ASC ) [is_subscribed]
, LEAD( d.is_merge_published , 5 ) OVER( ORDER BY d.name ASC ) [is_merge_published]
, LEAD( d.is_distributor , 5 ) OVER( ORDER BY d.name ASC ) [is_distributor]
, LEAD( d.is_sync_with_backup , 5 ) OVER( ORDER BY d.name ASC ) [is_sync_with_backup]
, LEAD( d.service_broker_guid , 5 ) OVER( ORDER BY d.name ASC ) [service_broker_guid]
, LEAD( d.is_broker_enabled , 5 ) OVER( ORDER BY d.name ASC ) [is_broker_enabled]
, LEAD( d.log_reuse_wait , 5 ) OVER( ORDER BY d.name ASC ) [log_reuse_wait]
, LEAD( d.log_reuse_wait_desc , 5 ) OVER( ORDER BY d.name ASC ) [log_reuse_wait_desc]
, LEAD( d.is_date_correlation_on , 5 ) OVER( ORDER BY d.name ASC ) [is_date_correlation_on]
, LEAD( d.is_cdc_enabled , 5 ) OVER( ORDER BY d.name ASC ) [is_cdc_enabled]
, LEAD( d.is_encrypted , 5 ) OVER( ORDER BY d.name ASC ) [is_encrypted]
, LEAD( d.is_honor_broker_priority_on , 5 ) OVER( ORDER BY d.name ASC ) [is_honor_broker_priority_on]
, LEAD( d.replica_id , 5 ) OVER( ORDER BY d.name ASC ) [replica_id]
, LEAD( d.group_database_id , 5 ) OVER( ORDER BY d.name ASC ) [group_database_id]
, LEAD( d.resource_pool_id , 5 ) OVER( ORDER BY d.name ASC ) [resource_pool_id]
, LEAD( d.default_language_lcid , 5 ) OVER( ORDER BY d.name ASC ) [default_language_lcid]
, LEAD( d.default_language_name , 5 ) OVER( ORDER BY d.name ASC ) [default_language_name]
, LEAD( d.default_fulltext_language_lcid , 5 ) OVER( ORDER BY d.name ASC ) [default_fulltext_language_lcid]
, LEAD( d.default_fulltext_language_name , 5 ) OVER( ORDER BY d.name ASC ) [default_fulltext_language_name]
, LEAD( d.is_nested_triggers_on , 5 ) OVER( ORDER BY d.name ASC ) [is_nested_triggers_on]
, LEAD( d.is_transform_noise_words_on , 5 ) OVER( ORDER BY d.name ASC ) [is_transform_noise_words_on]
, LEAD( d.two_digit_year_cutoff , 5 ) OVER( ORDER BY d.name ASC ) [two_digit_year_cutoff]
, LEAD( d.containment , 5 ) OVER( ORDER BY d.name ASC ) [containment]
, LEAD( d.containment_desc , 5 ) OVER( ORDER BY d.name ASC ) [containment_desc]
, LEAD( d.target_recovery_time_in_seconds , 5 ) OVER( ORDER BY d.name ASC ) [target_recovery_time_in_seconds]
, LEAD( d.delayed_durability , 5 ) OVER( ORDER BY d.name ASC ) [delayed_durability]
, LEAD( d.delayed_durability_desc , 5 ) OVER( ORDER BY d.name ASC ) [delayed_durability_desc]
, LEAD( d.is_memory_optimized_elevate_to_snapshot_on , 5 ) OVER( ORDER BY d.name ASC ) [is_memory_optimized_elevate_to_snapshot_on]
, LEAD( d.is_federation_member , 5 ) OVER( ORDER BY d.name ASC ) [is_federation_member]
, LEAD( d.is_remote_data_archive_enabled , 5 ) OVER( ORDER BY d.name ASC ) [is_remote_data_archive_enabled]
, LEAD( d.is_mixed_page_allocation_on , 5 ) OVER( ORDER BY d.name ASC ) [is_mixed_page_allocation_on]
FROM sys.databases d
ORDER BY d.name asc

```

As you can see, being a "Function", this also requires re-aliasing column names as well.