Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

PAGEDATA T-SQL in SQL 2012

DZone's Guide to

PAGEDATA T-SQL in SQL 2012

· Java Zone ·
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

Introduction

In this article I will discuss a new feature of Microsoft SQL Server called page data. Hope you will find it informative.

What Page Data Is

When we are taking about a front-end grid to display data, we have a limitation of spaces, so we represent it via paging in the grid. The paging system is a feature of the grid that we use in our front-end. But when we move throw pages we fire the SQL Statement to retrieve data from our database. Suppose our grid can display five data records at a time, so we retrieve 1 to 5 records first from our database and when the user clicks 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.

Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}