OFFSET and FETCH FIRST - Row Limiting - IBM DB2 to SQL Server Migration

In IBM DB2, you can use the OFFSET and FETCH FIRST (or NEXT) clause to limit the rows returned by the query.

In SQL Server 2012 or later, you can also use the OFFSET and FETCH FIRST (or NEXT) clause but the ORDER BY clause must be also specified while this is optional in DB2.

Note that you can also use the TOP clause in SQL Server when you need to limit the number of rows only without the offset.

Consider the following sample table:

  CREATE TABLE products (name VARCHAR(30));
 
  INSERT INTO products VALUES ('Apple');
  INSERT INTO products VALUES ('Melon');
  INSERT INTO products VALUES ('Orange');

IBM DB2:

  -- Select 1 row only but with offset 2
  SELECT name FROM products ORDER BY name OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY;
  /* Orange */
 
  -- Without offset
  SELECT name FROM products ORDER BY name DESC FETCH FIRST 1 ROWS ONLY;
  /* Orange */
 
  -- Without ORDER BY and OFFSET
  SELECT name FROM products FETCH FIRST 1 ROWS ONLY;
  /* Apple */
 
  -- Shorthand syntax for selecting 1 row
  SELECT name FROM products FETCH FIRST ROW ONLY;
  /* Apple */

SQL Server:

  -- Select 1 row only but with offset 2
  SELECT name FROM products ORDER BY name OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY;
  /* Orange */
 
  -- OFFSET must be specified
  SELECT name FROM products ORDER BY name DESC FETCH FIRST 1 ROWS ONLY;
  /* Msg 153, Level 15, State 2, Line 1 */
  /* Invalid usage of the option FIRST in the FETCH statement. */
 
  -- Using OFFSET 0 if no offset required
  SELECT name FROM products ORDER BY name DESC OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY;
  /* Orange */
 
  -- Both ORDER BY and OFFSET must be specified
  SELECT name FROM products FETCH FIRST 1 ROWS ONLY;
  /* Msg 153, Level 15, State 2, Line 1 */
  /* Invalid usage of the option FIRST in the FETCH statement. */
 
   -- Using ORDER BY (SELECT NULL) and OFFSET 0 
  SELECT name FROM products ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY;
  /* Apple */
 
  -- Or you can just use the TOP clause
  SELECT TOP 1 name FROM products;
  /* Apple */

Note that SQL Server requires both ORDER BY and OFFSET clauses to be specified if you use the FETCH FIRST clause.

For more information, see IBM DB2 to SQL Server Migration.