SQL Server: Limit (MySQL) equal Function in SQL Server

SQL Server: Limit (MySQL) equal Function in SQL Server

In MySQL, Limit is a powerful function used with SELECT query to return rows BUT within given range. We commonly need it when returning only required rows to application according to paging range. For example to return rows from 11 to 15 we will use following query in MySQL

SELECT * FROM MyTable LIMIT 11, 5

In SQL Server, same functionality can be achieved with three different ways.

1.       With Derived Table (Most inefficient way, but applicable to all versions of SQL Server)

SELECT  *

      FROM    ( SELECT    *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
          FROM      MyTable ) DerivedTable
       WHERE   RowNum >= 11
        AND RowNum <= 11 + ( 5 - 1 )

2.       With CTE Common Table Expression (Applicable to SQL Server 2005/2008/2012)
   ;
     WITH   CTE
          AS ( SELECT   *, ROW_NUMBER() OVER ( ORDER BY ColumnName ) AS RowNum
               FROM     MyTable)
    SELECT  *
    FROM    CTE
    WHERE   RowNum >= 11
            AND RowNum <= 11 + ( 5 - 1 )

3.       With OFFSET_ROW FETCH (Applicable to SQL Server 2012 Only)
        Here “OFFSET” means, how many rows to skip, “ROWS FETCH NEXT” means, how many rows to skip

 SELECT * FROM MyTable  

        OFFSET 10 
        ROWS FETCH NEXT 5 ROWS ONLY;

ref:

http://www.connectsql.com/2012/07/sql-server-limit-mysql-equal-function.html