SQL Server 2012: Pagination with Order By and Offset Fetch

Many times developers need to implement pagination on search results. Pagination is the process of dividing the results of a query into discrete numbered pages.

SQL Server 2012 has introduced a new and easy method to implement pagination using OFFSET and FETCH NEXT. This process is actually faster compared to previous complex methods like using row_number. This feature is somewhat similar to the MySQL Limit/Offset clause.

OFFSET: specifies the number of rows to skip before it starts returning rows
FETCH NEXT: the number of rows to display in the result

Let us explore Order By Offset fetch in SQL Server 2012 with examples.

Create a table called COMPANY, and populate it with some data.

IF OBJECT_ID('COMPANY') IS NOT NULL
DROP TABLE COMPANY
GO

CREATE TABLE COMPANY
(
ID        INT PRIMARY KEY,
NAME      VARCHAR(25),
LOCATION  VARCHAR(25)
)
GO

INSERT INTO COMPANY
VALUES (1,'HCL','London'),
       (2,'HP','Bangalore'),
       (3,'Microsoft','Bangalore'),
       (4,'Infosys','Pune'),
       (5,'Google','London'),
       (6,'GE', 'London'),
       (7,'AltiSource','New York'),
       (8,'Facebook','Palo alto'),
       (9,'IBM','New York'),
       (10,'TCS','Mumbai')
GO

SELECT * FROM COMPANY
GO

Problem 1.1 – Using only OFFSET

SELECT   ID, NAME, LOCATION
FROM     COMPANY
ORDER BY ID
OFFSET   3 ROWS

In the query above, we are using only OFFSET, so it will skip the first three rows and will return all remaining rows in a determined order.

Problem 1.2 – Skip zero rows, and fetch the first five rows.

SELECT     ID, NAME, LOCATION
FROM       COMPANY
ORDER BY   ID
OFFSET     0 ROWS
FETCH NEXT 5 ROWS ONLY

In the query above, OFFSET 0 ROWS means we have skipped zero and FETCH NEXT 5 intends to retrieve the next five rows.

Problem 1.3 – Skip the first five rows, and fetch the next five rows.

SELECT     ID, NAME, LOCATION
FROM       COMPANY
ORDER BY   ID
OFFSET     5 ROWS
FETCH NEXT 5 ROWS ONLY

Here, we are skipping the first five rows and fetching the next five rows.

The result above can be achieved using SQL Server 2005/2008 using row_number and derived table.

SELECT ID, NAME, LOCATION
FROM
(
SELECT ID, NAME, LOCATION, ROW_NUMBER() OVER(ORDER BY ID) as rownum
FROM   COMPANY c
) DT
WHERE  DT.rownum BETWEEN 6 AND 10

Performance comparison between OFFSET FETCH and ROW_NUMBER

The OFFSET FETCH approach took 0.003294, and the row_number approach took 0.0033038. This shows the newer approach OFFSET FETCH in SQL Server 2012 is faster.

Problem 1.4 – Using Variables with OFFSET and FETCH

DECLARE @OffSetRows AS INT = 5
DECLARE @FetchRows AS INT = 5

SELECT     ID, NAME, LOCATION
FROM       COMPANY
ORDER BY   ID
OFFSET     @OffSetRows ROWS
FETCH NEXT @FetchRows ROWS ONLY

This is the same as Problem 1.3, but here we are using variables to store OFFSET and FETCH values.

Inner and Left Outer Join with Where Clause vs ON Clause

Filtering results with the [ON] clause and the [Where] clause using LEFT OUTER JOIN and INNER JOIN is a very powerful technique.

An INNER JOIN gives rows which match on the values in common columns of two or more tables using an operator like (=) equal.

A LEFT JOIN or LEFT OUTER JOIN gives all the rows from the left table with matched rows from both tables. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Now, we will demonstrate how these work.

We will create two tables: FRUIT and FRUIT_COLOR.

IF OBJECT_ID('FRUIT') IS NOT NULL
DROP TABLE FRUIT;
GO

CREATE TABLE FRUIT (
name VARCHAR(25),
color INT
);
GO

IF OBJECT_ID('FRUIT_COLOR') IS NOT NULL
DROP TABLE FRUIT_COLOR;
GO

CREATE TABLE FRUIT_COLOR
(
id INT,
name VARCHAR(25)
);
GO

Regarding the structure of the created tables, the FRUIT_COLOR table has a unique column (ID) and color (name)s.

The FRUIT table consists of fruit (name)s and their respective (color).

I have not created any primary key and foreign key relationships to keep it simple, but you can easily understand the primary key FRUIT_COLOR(ID) is a foreign key to FRUIT(color).

Populate these tables with some data.

INSERT into FRUIT_COLOR VALUES (1,'orange');
INSERT into FRUIT_COLOR VALUES(2,'yellow');
INSERT into FRUIT_COLOR VALUES (3,'red');
INSERT into FRUIT_COLOR VALUES (4,'blue');
GO

INSERT into FRUIT VALUES ('banana',2);
INSERT into FRUIT VALUES ('mango',2);
INSERT into FRUIT VALUES ('orange',1);
INSERT into FRUIT VALUES ('apple',3);
INSERT into FRUIT VALUES ('grapes',null);
INSERT into FRUIT VALUES ('avocado',null);
GO

SELECT * from FRUIT;
SELECT * from FRUIT_COLOR;
GO

We will demonstrate different scenarios with a left outer join.

Query 1.1 – A Plain, Simple Left Outer Join

SELECT  *
FROM    FRUIT F LEFT outer join FRUIT_COLOR FC
ON      F.color = FC.id;
GO

As we know the concept of a LEFT OUTER JOIN, we know it will show all the rows from the left table. In this case, FRUIT is the left table, and if any match is found with the right table called FRUIT_COLOR, it will show the matching rows. Otherwise, it will return NULL.

In the example above, grapes and avocados have their color as NULL, so the NULL comparison fails and returns NULL.

Query 1.2 – A Left Outer Join with the Where Clause

SELECT  *
FROM    FRUIT F LEFT outer join FRUIT_COLOR FC
ON      F.color = FC.id
WHERE   F.name='apple';
GO

When we add a where clause with a left outer join, it behaves like an inner join, where the filter is applied after the ON clause, showing only those rows having fruit with the name “apple.”

Query 1.3 – A Left Outer Join with the ON Clause

SELECT  *
from    FRUIT F LEFT outer join FRUIT_COLOR FC
ON      F.color = FC.id AND F.name='apple';
GO

In the query above, most people get confused about how this query is evaluated. You can see the results below all the rows from the left table are included in the result set as a plain left outer join query that we saw in Query 1.1, but from the second table (i.e., fruit_color), only those rows are shown which match the condition f.name=’apple.’ Otherwise, it is shown as NULL.

This is useful when filtering data with a left outer join. The [ON] clause can be useful when we need to return all the rows from the left table and only those rows from the right table which match the condition of the On clause.

– Query 1.4 – An Inner Join with the Where Clause

SELECT  *  
FROM    FRUIT F INNER join FRUIT_COLOR FC
ON      F.color = FC.id
WHERE   F.name='orange';
GO

– Query 1.5 – An Inner Join with the ON Clause

SELECT   *  
FROM    FRUIT F INNER join FRUIT_COLOR FC
ON        F.color = FC.id AND F.name='orange';
GO

You can see both query 1.4 and query 1.5, using an inner join, return the same result because it does not matter where you are filtering the rows with an inner join. With either the on clause or the where clause, both cases will give you same results.

How to use the ISNUMERIC & CASE functions?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the usage of the ISNUMERIC function and CASE expression.

-- T-SQL ISNUMERIC function usage - QUICK SYNTAX - 0 means not numeric

SELECT AlphaNumericZip=COUNT(*)  FROM AdventureWorks.Person.[Address]

  WHERE ISNUMERIC (PostalCode) = 0           -- 3644

-- SQL Server ISNUMERIC & CASE quick usage examples - t sql isnumeric and case functions

-- T-SQL IsNumber, IsINT, IsMoney - filtering numeric data from text columns 
SELECT ISNUMERIC('$12.09'), ISNUMERIC('12.09'), ISNUMERIC('$'), ISNUMERIC('Alpha')

--          1                 1                 1                             0

SELECT ISNUMERIC('-12.09'), ISNUMERIC('1209'), ISNUMERIC('1.0e9'), ISNUMERIC('A001')

--          1                 1                 1                             0

------------

SELECT   TOP (4) AddressID, 
                   City, 
                   PostalCode, 
                   CASE 
                     WHEN ISNUMERIC(PostalCode) = 1 THEN 'Y' 
                     ELSE 'N' 
                   END AS IsZipNumeric 
FROM     AdventureWorks2008.Person.Address 
ORDER BY NEWID() 
/* AddressID   City           PostalCode  IsZipNumeric
27625       Santa Monica      90401       Y
23787       London            SE1 8HL     N
24776       El Cajon          92020       Y
22120       Wollongong        2500        Y    */

------------

-- CASE function usage in SELECT list for data formatting & translation

SELECT   ProductID, ProductName=Name,

         ListPrice = CASE WHEN ListPrice > 0

                          THEN '$'+CONVERT(varchar(32),ListPrice,1) -- currency format

                     ELSE '' END,

         ProductLine = CASE ProductLine

                         WHEN 'R' THEN 'Road'

                         WHEN 'M' THEN 'Mountain'

                         WHEN 'T' THEN 'Touring'

                         WHEN 'S' THEN 'General'

                         ELSE 'Internal part'

                       END

FROM AdventureWorks2008.Production.Product

ORDER BY ProductLine, ProductName;

/* ProductID      ProductName       ListPrice   ProductLine

....

      330   Touring End Caps                    Internal part

      513   Touring Rim                         Internal part

      879   All-Purpose Bike Stand  $159.00     Mountain

      878   Fender Set - Mountain   $21.98      Mountain

      863   Full-Finger Gloves, L   $37.99      Mountain  .... */

------------

-- SQL CASE function in WHERE clause for search - QUICK SYNTAX

DECLARE @Color varchar(16) = NULL  -- input parameter

SELECT * FROM AdventureWorks2008.Production.Product

WHERE Color = CASE

                    WHEN @Color is not null THEN @Color

                    ELSE Color END

GO

-- (256 row(s) affected)

DECLARE @Color varchar(16) = 'Blue'  -- input parameter

SELECT * FROM AdventureWorks2008.Production.Product

WHERE Color = CASE

                    WHEN @Color is not null THEN @Color

                    ELSE Color END

-- (26 row(s) affected)

The ISNUMERIC function can be used for safe coding by checking the string data prior to CONVERT/CAST to numeric. The ISNUMERIC function as filter is important in data cleansing when internal/external feeds are loaded into the database or data warehouse.

The ISNUMERIC function takes a single string expression as parameter and returns:
  1 for numeric values
  0 for non-numeric values

----------------------------------------------------------------

-- Demonstration of IsNumeric for selected values

----------------------------------------------------------------

USE tempdb;

GO

CREATE TABLE IsNumericDemo (

ID smallint identity primary key,

NumberString varchar(32),

[IsNumeric] bit )

GO

INSERT IsNumericDemo(NumberString) VALUES ('$')

INSERT IsNumericDemo(NumberString) VALUES ('$100')

INSERT IsNumericDemo(NumberString) VALUES ('$200.01')

INSERT IsNumericDemo(NumberString) VALUES ('-$300')

INSERT IsNumericDemo(NumberString) VALUES ('$-400')

INSERT IsNumericDemo(NumberString) VALUES ('$123,568.00')

INSERT IsNumericDemo(NumberString) VALUES ('$234.568.00')

INSERT IsNumericDemo(NumberString) VALUES ('3.E4')

INSERT IsNumericDemo(NumberString) VALUES ('FFFF')

INSERT IsNumericDemo(NumberString) VALUES ('2^10')

INSERT IsNumericDemo(NumberString) VALUES ('0000001')

INSERT IsNumericDemo(NumberString) VALUES ('1234+')

INSERT IsNumericDemo(NumberString) VALUES ('+1234')

INSERT IsNumericDemo(NumberString) VALUES ('twenty')

GO

-- Set the IsNumeric flag

UPDATE IsNumericDemo SET [IsNumeric] = ISNUMERIC(NumberString)

-- Check results

SELECT * FROM IsNumericDemo

GO

/* Results

ID    NumberString      IsNumeric

1     $                 1

2     $100              1

3     $200.01           1

4     -$300             1

5     $-400             1

6     $123,568.00       1

7     $234.568.00       0

8     3.E4              1

9     FFFF              0

10    2^10              0

11    0000001           1

12    1234+             0

13    +1234             1

14    twenty            0

*/

-- Cleanup

DROP TABLE IsNumericDemo

GO

-- SQL ISALPHANUMERIC check

------------

-- SQL not alphanumeric string test - sql patindex pattern matching

SELECT DISTINCT LastName

FROM   AdventureWorks.Person.Contact

WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName) > 0

GO

/* Partial results

LastName

Mensa-Annan

Van Eaton

De Oliveira

*/

-- SQL ALPHANUMERIC test - isAlphaNumeric

SELECT DISTINCT LastName

FROM   AdventureWorks.Person.Contact

WHERE  PATINDEX('%[^A-Za-z0-9]%',LastName)= 0

GO

/* Partial results

LastName

Abbas

Abel

Abercrombie

*/

------------

-- Parsing for numeric data in alphanumeric string

USE Northwind;

SELECT QuantityPerUnit FROM Products ORDER BY ProductName

-- QuantityPerUnit: 10 - 500 g pkgs., 1 kg pkg., 24 - 250 g pkgs.

-- When left 3 characters satisfy isnumeric test, we convert to int

SELECT

  ProductName,

  QuntityInPackage=convert(int,left(QuantityPerUnit,3))

FROM Products

WHERE ISNUMERIC(left(QuantityPerUnit,3))=1

ORDER BY ProductName

/* Partial results

ProductName                         QuntityInPackage

Chef Anton's Cajun Seasoning        48

Chef Anton's Gumbo Mix              36

Grandma's Boysenberry Spread        12

Uncle Bob's Organic Dried Pears     12

Northwoods Cranberry Sauce          12

*/

-- Canadian & UK zipcodes would not be numeric - NULL also not numeric (function yields 0)

USE pubs;

SELECT

  Zip=zip,

  [Numeric = 1] = ISNUMERIC(zip)

FROM authors

/* Partial results

Zip         Numeric = 1

94025       1

NULL        0

94618       1

94705       1

*/

-- Filtering non-numeric postal codes

USE AdventureWorks;

SELECT Country=cr.Name,

       State=sp.StateProvinceCode,

       City,

       PostalCode

FROM Person.Address a

  INNER JOIN Person.StateProvince sp

      ON a.StateProvinceID = sp.StateProvinceID

  INNER JOIN Person.CountryRegion cr

      ON sp.CountryRegionCode = cr.CountryRegionCode

WHERE ISNUMERIC(PostalCode) = 0

/* Partial results

Country                 State       City        PostalCode

Canada                  ON          Ottawa      K4B 1S2

Canada                  BC          Burnaby     V5A 4X1

Canada                  BC          Haney       V2W 1W2

United Kingdom         ENG         Cambridge   CB4 4BZ

*/

-- Filtering for not numeric Size

SELECT * FROM AdventureWorks2008.Production.Product

WHERE Size is not NULL

  and ISNUMERIC(Size) = 0 -- NOT NUMERIC

-- 34 rows, Size like S, M, L

/*****

The following query ends in error without ISNUMERIC filtering:

Conversion failed when converting the nvarchar value 'M' to data type int.

****/

SELECT iSize=CONVERT(int, Size), *

FROM AdventureWorks2008.Production.Product

WHERE Size is not NULL

/*****

CONVERT to INT in the following query will not fail due to ISNUMERIC filtering

****/

SELECT iSize=CONVERT(int, Size), *

FROM AdventureWorks2008.Production.Product

WHERE Size is not NULL

  and ISNUMERIC(Size) = 1 -- NUMERIC

-- 177 rows, Size like 44, 58, 62

-- Using the CASE function to tag data numeric/alpha

SELECT   ProductName = Name,

         Size,

         [SizeType] = CASE

                        WHEN ISNUMERIC(Size) = 1 THEN 'Numeric'

                        ELSE 'Alpha/Alphanumeric'

                      END

FROM     AdventureWorks2008.Production.Product

WHERE    Size IS NOT NULL

ORDER BY SizeType,

         ProductName-- 211 rows

/* Partial results

ProductName       Size SizeType

Classic Vest, L   L    Alpha/Alphanumeric

Classic Vest, M   M    Alpha/Alphanumeric

Classic Vest, S   S    Alpha/Alphanumeric

*/

------------

-- Using IsNumeric with IF...ELSE conditional construct

------------

DECLARE @StringNumber varchar(32)

SET @StringNumber = '12,000,000'

IF EXISTS( SELECT * WHERE ISNUMERIC(@StringNumber) = 1)

      PRINT 'VALID NUMBER: ' + @StringNumber

ELSE

    PRINT 'INVALID NUMBER: ' + @StringNumber

GO

-- Result: VALID NUMBER: 12,000,000

DECLARE @StringNumber varchar(32)

SET @StringNumber = '12-34'

IF EXISTS( SELECT * WHERE ISNUMERIC(@StringNumber) = 1)

      PRINT 'VALID NUMBER: ' + @StringNumber

ELSE

    PRINT 'INVALID NUMBER: ' + @StringNumber

GO

-- Result: INVALID NUMBER: 12-34

------------

-- sql isnumeric function in data cleansing for valid numbers

------------

USE tempdb;

-- sql cast number to varchar - newid - random sort - t-sql top function

-- sql select into table create

-- sql convert number to text - numeric to text - numeric to varchar

SELECT top(3) PurchaseOrderID,

  stringSubTotal = CAST (SubTotal AS varchar)

INTO NumberValidation

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY NEWID()

GO

SELECT * FROM NumberValidation

/* Results

PurchaseOrderID   stringSubTotal

1027              20397.30

2815              525.00

340               7623.00

*/

-- SQL update with top

UPDATE TOP(1) NumberValidation

SET stringSubTotal = '91117 95'

GO

-- SQL string to numeric conversion fails without validation

SELECT PurchaseOrderID, SubTotal = CAST (stringSubTotal as money)

FROM NumberValidation

GO

/* Msg 235, Level 16, State 0, Line 2

Cannot convert a char value to money. The char value has incorrect syntax.

*/

-- sql isnumeric - filter for valid numbers

SELECT PurchaseOrderID, SubTotal = CAST (stringSubTotal as money)

FROM NumberValidation

WHERE ISnumeric(stringSubTotal) = 1

GO

/* Results

PurchaseOrderID   SubTotal

2815              525.00

340               7623.00

*/

-- SQL drop table

DROP TABLE NumberValidation

Go

-- SQL Server - Using the CASE function & ISNUMERIC function in ORDER BY clause

SELECT   p.BusinessEntityID,

         FullName = FirstName + ' ' + LastName,

         City,

         PostalCode

FROM     AdventureWorks2008.Person.Person p

         INNER JOIN AdventureWorks2008.Person.BusinessEntityAddress bea

           ON bea.BusinessEntityID = p.BusinessEntityID

         INNER JOIN AdventureWorks2008.Person.Address a

           ON bea.AddressID = a.AddressID

ORDER BY CASE

           WHEN ISNUMERIC(PostalCode) = 0 THEN 1

           ELSE 2

         END,

         City,

         FullName DESC

/* Partial results

BusinessEntityID  FullName          City              PostalCode

15254             Heidi Arun        Basingstoke Hants RG24 8PL

5329              Dale Andersen     Basingstoke Hants RG24 8PL

17511             Valerie Zhu       Berks             SL4 1RH

8625              Tasha Lal         Berks             SL4 1RH

20020             Tara Raji         Berks             SL4 1RH

5001              Suzanne Li        Berks             SL4 1RH */

------------

-- Using CASE, ISNUMERIC and LIKE for numeric/digits testing

------------

-- SQL numeric test - SQL isnumeric - ISNUMERIC sql

SELECT   TOP 5 CompanyName,

               City=City+', '+Country,

               PostalCode,

               IsPostalCodeNumeric =

CASE

                           WHEN Isnumeric(PostalCode) = 1 THEN 'Numeric'

                           ELSE 'Not Numeric'

                        END

FROM     Northwind.dbo.Suppliers

ORDER BY Newid()

GO

/* Results

CompanyName             City                    PostalCode IsPostalCodeNumeric

Refrescos Americanas    Sao Paulo, Brazil       5442        Numeric

Zaanse Snoepfabriek     Zaandam, Netherlands    9999 ZZ     Not Numeric

Formaggi Fortini s.r.   Ravenna, Italy          48100       Numeric

Gai pâturage            Annecy, France          74000       Numeric

Forêts d'érables        Ste-Hyacinthe, Canada   J2S 7S8     Not Numeric

*/

-- Alternate numeric test with like

-- SQL zipcode test - SQL test numeric - SQL CASE function

SELECT   TOP 5 CompanyName,

               City=City+', '+Country,

               PostalCode,

               [IsNumeric] =

               CASE

                   WHEN PostalCode like '[0-9][0-9][0-9][0-9][0-9]'

                     THEN '5-Digit Numeric'

                   ELSE 'Not 5-Digit Numeric'

               END

FROM     Northwind.dbo.Suppliers

ORDER BY Newid()

GO

/* Results

CompanyName             City                    PostalCode        IsNumeric

Escargots Nouveaux      Montceau, France        71300 5-Digit     Numeric

Norske Meierier         Sandvika, Norway        1320  Not 5-Digit Numeric

Pavlova, Ltd.           Melbourne, Australia    3058  Not 5-Digit Numeric

Zaanse Snoepfabriek     Zaandam, Netherlands    9999 ZZ           Not 5-Digit Numeric

Exotic Liquids          London, UK              EC1 4SD           Not 5-Digit Numeric

*/

-- SQL Server stored procedure outputting decimal (numeric) value

-- SQL decimal output parameter - SQL power math function

USE AdventureWorks2008;

GO

CREATE PROCEDURE uspDelta @PowerOf2 INT,

                @Parm DECIMAL(38,3)  OUTPUT

AS

BEGIN

  SET @Parm = POWER(CONVERT(BIGINT,2), @PowerOf2)+0.789

  RETURN (ISNUMERIC(CAST (@Parm as varchar)))

END

GO

-- Execute stored procedure - Outputs: @Isnumeric, @DecimalParm

DECLARE  @DecimalParm DECIMAL(38,3), @IsNumeric tinyint;

EXEC @IsNumeric = uspDelta 50, @DecimalParm OUTPUT;

SELECT IsItNumeric = @IsNumeric, sprocResult = @DecimalParm;

GO

/*  Result  

IsItNumeric       sprocResult

1                 1125899906842624.789

*/

-- Cleanup

DROP PROC uspDelta

GO

------------