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

Learn how to troubleshoot and diagnose some of the most common performance issues in Java today. Brought to you in partnership with AppDynamics.

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.

Understand the needs and benefits around implementing the right monitoring solution for a growing containerized market. Brought to you in partnership with AppDynamics.

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}