Thursday, 9 April 2015

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

2 comments:

  1. Sir which one is preferable as performance point of view where clause or Fetch next option. How it would be execute into execution plan.

    ReplyDelete