In my earlier post i have wrote about new feature "Order By Clause Enhancement". I have given demonstration with examples as well as how can we use this feature.
It is used for SQL pagination and also explained for the same for pagination.
You can read that post here http://paresh-sqldba.blogspot.com/2011/04/order-by-clause-enhancement-and.html.
Here i want to give same demonstration but not with only Order By Clause enhancement, It will be various ways and methods for SQL pagination.
We will look for the native method and new method of SQL pagination and review it.
Inserting records using sequence and looks for inserted records.
We will fetch 10 records using various methods.
Using TempTable
Using query alias
Using Common Table Expression
Using OFFSET and FETCH NEXT and Order by introduced by SQL Server Denali.
I got one suggestion from Jacob(blog) , and he had a good and optimized suggestion for SQL Pagination as we should put TOP clause in script same as page size, so it will filter rows as per search criteria.
Like we have script using TOP clause and revised script is as following,
Here you have same output for all above such methods including last optimized suggested by Jacob used for SQL pagination.
You can share your ideas if you have a new methods for pagination.
It is used for SQL pagination and also explained for the same for pagination.
You can read that post here http://paresh-sqldba.blogspot.com/2011/04/order-by-clause-enhancement-and.html.
Here i want to give same demonstration but not with only Order By Clause enhancement, It will be various ways and methods for SQL pagination.
We will look for the native method and new method of SQL pagination and review it.
USE PaginationDB GO IF (OBJECT_ID('PaginationTable') >= 0) DROP TABLE PaginationTable GO CREATE table PaginationTable ( PageId INT, PageName VARCHAR(100) ) GO IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence') DROP SEQUENCE NewSequence; GO CREATE SEQUENCE [NewSequence] AS INT START with 1 INCREMENT by 1 MAXVALUE 1000000 GO
Inserting records using sequence and looks for inserted records.
INSERT INTO PaginationTable SELECT next value for [NewSequence], Name FROM sys.objects GO 5000
We will fetch 10 records using various methods.
Using TempTable
CREATE TABLE #PaginationTemp ( Id INT IDENTITY(1,1), PageId INT, PageName VARCHAR(100) ) INSERT INTO #PaginationTemp(PageId, PageName) SELECT PageId, PageName FROM PaginationTable ORDER BY PageName,PageId DECLARE @PageIndex INT = 1, @Pagesize INT = 10 SELECT * FROM #PaginationTemp WHERE Id BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize GO
Using query alias
DECLARE @PageIndex INT = 1, @Pagesize INT = 10 SELECT res.PageId, res.PageName FROM ( SELECT ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW, PageId, PageName FROM PaginationTable ) res WHERE RES.RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize GO
Using Common Table Expression
DECLARE @PageIndex INT = 1, @Pagesize INT = 10 ;With CTEPage AS ( SELECT ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW, PageId, PageName FROM PaginationTable ) SELECT PageId, PageName FROM CTEPage WHERE RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize GO
Using OFFSET and FETCH NEXT and Order by introduced by SQL Server Denali.
DECLARE @PageIndex INT = 1, @Pagesize INT = 10 SELECT PageId, PageName FROM PaginationTable ORDER BY PageName,PageId OFFSET (@PageIndex - 1) * @Pagesize ROWS FETCH NEXT @Pagesize ROWS ONLY GO
I got one suggestion from Jacob(blog) , and he had a good and optimized suggestion for SQL Pagination as we should put TOP clause in script same as page size, so it will filter rows as per search criteria.
Like we have script using TOP clause and revised script is as following,
DECLARE @PageIndex INT = 1, @Pagesize INT = 10 INSERT INTO #PaginationTemp(PageId, PageName) SELECT TOP (((@PageIndex - 1) * @Pagesize) + @Pagesize) PageId, PageName FROM PaginationTable ORDER BY PageName,PageId SELECT * FROM #PaginationTemp WHERE Id BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize GO
Here you have same output for all above such methods including last optimized suggested by Jacob used for SQL pagination.
You can share your ideas if you have a new methods for pagination.
Comments
Post a Comment