Pagination in SQL Server 2012

Pagination in SQL Server 2012

/*Queries to Generate Data for pagination*/

DECLARE   @Passenger TABLE

  (

   ID INT,

       PassengerID VARCHAR(10),

       CityName   VARCHAR(10),

       StartDate  DATE

    )

INSERT INTO @Passenger VALUES (1,'P00001', 'Delhi', '1-Sep-2014')

INSERT INTO @Passenger VALUES (2,'P00001', 'Mathura', '1-Oct-2014')

INSERT INTO @Passenger VALUES (3,'P00001', 'Etah', '10-Oct-2014')

INSERT INTO @Passenger VALUES (4,'P00001', 'Kanpur', '25-Oct-2014')

INSERT INTO @Passenger VALUES (5,'P00001', 'Lucknow', '27-Oct-2014')

INSERT INTO @Passenger VALUES (6,'P00002', 'Allahabad', '1-Oct-2014')

INSERT INTO @Passenger VALUES (7,'P00002', 'Banaras', '1-Nov-2014')

INSERT INTO @Passenger VALUES (8,'P00002', 'Mumbai', '10-Nov-2014')

INSERT INTO @Passenger VALUES (9,'P00002', 'Banaras', '15-Nov-2014')

INSERT INTO @Passenger VALUES (10,'P00002', 'Pune', '25-Nov-2014')

INSERT INTO @Passenger VALUES (11,'P00002', 'Hyd', '28-Nov-2014')

INSERT INTO @Passenger VALUES (12,'P00002', 'Chennai', '30-Nov-2014')

INSERT INTO @Passenger VALUES (13,'P00003', 'Bangalore', '1-Dec-2014')

Query 1:

SELECT   * 

FROM     @Passenger 

ORDER BY Id

OFFSET 3 ROWS

FETCH NEXT 6 ROWS ONLY

Result:

ID	PassengerID	CityName	StartDate
4	P00001	Kanpur	25-Oct-2014
5	P00001	Lucknow	27-Oct-2014
6	P00002	Allahabad	1-Oct-2014
7	P00002	Banaras	1-Nov-2014
8	P00002	Mumbai	10-Nov-2014
9	P00002	Banaras	15-Nov-2014

Query 2:

SELECT   * 

FROM     @Passenger 

ORDER BY  Id

OFFSET 9 ROWS

FETCH NEXT 3 ROWS ONLY

Result:

ID	PassengerID	CityName	StartDate
10	P00002	Pune	25-Nov-2014
11	P00002	Hyd	28-Nov-2014
12	P00002	Chennai	30-Nov-2014

Note: OFFSETand Next Rows count never be Negative.