Over a million developers have joined DZone.

PAGEDATA T-SQL in SQL 2012

· Java Zone

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

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.

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}