Converting MySQL LIMIT to SQL Server "Denali"

[Updated 2/6/2012 Selina Jia - Microsoft SQL Server Migration Assistant (SSMA) for MySQL v5.2.  The information provided below is still valid for SSMA for MySQL v5.2, but SQL Server "Denali" is changed to 2012.  Users should download the lastest SSMA for MySQL]

One of the new feature in SSMA for MySQL v.5.1 is the support for converting MySQL LIMIT clause to SQL Server “Denali” OFFSET/FETCH NEXT clause.

LIMIT is commonly used in MySQL statement to support query paging functionality. SQL Server “Denali” includes OFFSET/FETCH NEXT clause which can be used when requesting range of rows from a SELECT statement based on the specified starting position (offset) and number of rows to be returned.

When converting to SQL Server “Denali”, SSMA for MySQL v.5.1 automatically converts LIMIT clause to the OFFSET/FETCH NEXT clause. For example

MySQL:
    SELECT           col1, MAX(col2)
    FROM             table1
    GROUP BY     col1
    ORDER BY      col1
    LIMIT              5,2;
     
    Or
     
    SELECT           col1, MAX(col2)
    FROM             table1
    GROUP BY     col1
    ORDER BY      col1
    LIMIT              2
    OFFSET           5;
     
 
SQL Server:
    SELECT            col1, max(col2)
    FROM              table1
    GROUP BY      col1
    ORDER BY       col1
    OFFSET            5 ROWS
    FETCH NEXT   2 ROWS ONLY

When the source statement contains LIMIT with only one argument, SSMA converts the statement using TOP as follows:

MySQL:
    SELECT col1 FROM table_1 LIMIT 5;

SQL Server:
    SELECT TOP 5 col1 FROM table_1

 

For more example, visit Brian Swan's blog SQL Server Migration Assistant 5.1 Supports SQL Server v-Next