OFFSET and FETCH

Feature number 1 (Revolution): OFFSET and FETCH

Both TOP and OFFSET & FETCH can be used to limit the number of rows returned.  OFFSET and FETCH can return similar results to top, but there are differences which may influence which method is best for you to use in your given situation.

TOP is used to return a specific number of rows from a query result.

OFFSET specifies how many rows to skip within the result, and FETCH specifies how many rows from that point forward to return in the result.

•       ANSI Complaint:

–      TOP isn’t ANSI compliant and its use manly limited to Microsoft products such as SQL Server and MS-Access.

–      OFFSET and FETCH are ANSI compliant.

•       SQL Version:

–      The TOP clause has been available for some time in older versions of SQL server, such as SQL 2005.

–      OFFSET and FETCH were recently introduced in SQL Server 2012

•       Order By Required:

–      The TOP clause is used in the SELECT statement’s column list and can be use with or without an ORDER BY.

As without it, you have no guarantee for which rows are returned.

–      You can use OFFSET without FETCH, but FETCH can’t be used by itself.  Regardless, OFFSET must be used with an ORDER BY clause.

•       Percentage of rows:

–      One item unique to TOP is the ability to return a percentage of rows.

Returns the top 51 products (i.e. 504 rows x 10% = 50.4, rounded up to 51)

–      Unlike TOP, with OFFSET there is not built in means to return a percentage of rows.

•       Return Bottom Rows:

–      To do this you can use a trick to order the records in descending order

                                SELECT   TOP 10 Name,

                                                   StandardCost

                                FROM     Production.Product

                                ORDER BY StandardCost DESC

–      We can reverse the sort order as we did for TOP

                                SELECT   Name,

                                                StandardCost

                                FROM     Production.Product

                                ORDER BY StandardCost DESC

                                OFFSET 0 ROWS

                                FETCH NEXT 10 ROWS ONLY

•       Return Middle Records:

–      No

–      The first ten rows of the result are skipped, then the next 10 displayed in the result.

                                SELECT   Name,

                                                StandardCost

                                FROM     Production.Product

                                ORDER BY StandardCost

                                OFFSET 10 ROWS

                                FETCH NEXT 10 ROWS ONLY

                                Return 11 to 20 records

•       With Ties:

•       Yes

–      WITH TIES allows you to also display additional records that have the same value as the last row from the base result.

•       No