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:
