PAGEDATA T-SQL in SQL 2012

Introduction

In this article we are trying to discuss about a new feature of Microsoft SQL server called page data. Hope you will find it informative.

What the Page Data is

When we are taking about front-end grid to display data we have limitation of spaces, so we represent it via paging in the grid. The paging system is a feature of grid that we used in our front-end. But when we move throw pages we fired the SQL Statement to retrieve data from our database. Suppose our grid can display five data at a time, so we retrieve 1 to 5 records first from database and when the user click on the next page we retrieve records from 6 to 10 and so on.

How We do it

Step-1 [ Create the Base Table  ]

IF OBJECT_ID(N'dbo.tbl_ItemMast', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_ItemMast];

   END

GO

CREATE TABLE [dbo].[tbl_ItemMast]

   (

      ITEMCD      BIGINT        NOT NULL PRIMARY KEY,

        ITEMNAME    VARCHAR(50)   NOT NULL

   );

GO

Step-2 [ Insert Some Records in our Base Table ]

INSERT INTO [dbo].[tbl_ItemMast]

       (ITEMCD, ITEMNAME)

VALUES (1, 'Tooth Paste'), (2, 'Tooth Brush'),

       (3, 'Banana'), (4, 'Apple'), (5, 'Orange'),

       (6, 'Saving Lootion'), (7, 'Oil'), (8, 'Saving Cream'),

       (9, 'Cake'), (10, 'Rice');

GO

Step-3 [ How we Retrieve Records Before MS SQL Server 2012 ]

SELECT a.*

FROM   (SELECT ROW_NUMBER() OVER (ORDER BY ITEMCD)AS RNUM, ITEMNAME

        FROM   [dbo].[tbl_ItemMast])AS a

WHERE  a.RNUM >=1 AND a.RNUM<=5;

Output :

SELECT a.*

FROM   (SELECT ROW_NUMBER() OVER (ORDER BY ITEMCD)AS RNUM, ITEMNAME

        FROM   [dbo].[tbl_ItemMast])AS a

WHERE  a.RNUM >=5 AND a.RNUM<=10;

Output:

Step-4 [ How we retrieve records in SQL Server 2012 ]

SELECT *

FROM   [dbo].[tbl_ItemMast]

ORDER BY ITEMCD

OFFSET 0 ROWS

FETCH NEXT 5 ROWS ONLY;

Output:

SELECT *

FROM   [dbo].[tbl_ItemMast]

ORDER BY ITEMCD

OFFSET 5 ROWS

FETCH NEXT 5 ROWS ONLY;

Output:

Hope you like it.

Posted by: MR. JOYDEEP DAS