I have received few emails and comments about my post SQL SERVER - T-SQL Paging Query Technique Comparison - SQL 2000 vs SQL 2005. The main question was is this can be done using CTE? Absolutely! What about Performance? It is same! Please refer above mentioned article for history of paging.
SQL 2005 Paging Method Using Derived Table
USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140
SELECT FirstName, LastName, EmailAddress
FROM (
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC) PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO
SQL 2005 Paging Method Using CTE
USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO
Following Image of Execution Plan displays that the performance for both of them is same with regarding to each other in one batch. This MAY NOT be true when there is complex query in issue. For most of the time, it is will be same.
Reference : Pinal Dave (http://www.SQLAuthority.com), SQL SERVER - T-SQL Paging Query Technique Comparison - SQL 2000 vs SQL 2005
pinaldave
[...] 9) If retrieving multiple rows, always use database paging techniques to retrieve only the rows which are necessary (displayed on screen). Click here for more information on how to do this [...]
Hello, Pinal ,
I am facing one problem. I did the paging is MSSQL 2005. and now i binded it to the controls ( returned datasets). When i clicked on the next page then, is i have to come back to the sqlserver with new startrow and endrow values ?.
Hi pinal
Thanks for your valuable suggestion about paging. I am very thankful for that.
Please suggest some good method to do paging for sql server 2000.
thanks
vijay
So it’s creating a Derived Table, almost like a temp table and creating a row number column?
What happens if you have to page through 50,000 rows???
First it will create a massive 50,000 row Derived table - with the row number column, then you select from the Derived table.
Can’t see that being good for memory or cpu performance.
Any other solutions?
You could always use “top” to limit the number of rows returned by the subquery…then you would only have the massive derived table if you actually paged to the end of the data.
The code is pretty simple. But the problem is that it sorts the data that is coming out. And OVER() method needs an “order by” in it’s parameter. So if we want the data as it is in the database it is not possible in this way. If there is any way to eliminate the sorting then i think the code will be more appropriate.
Check out my blog for a solution to fetching the last page efficiently.
Hi Pinal,
I have a table with 2 million records, i have created a Full text Index on a nvarchar column ‘itemName’ of size 70. When i do a query like:
WITH ListTemp AS (Select ROW_NUMBER() OVER( ORDER BY [main].[itemName] ASC) as rownum,
main.itemname,main.itemdescription
FROM
CONTAINSTABLE([ItemTemp1], itemname, ‘chevrolet’) AS A
join [ItemTemp1] main on a.[key] = main.id
)
SELECT * FROM ListTemp WHERE rownum > 0 AND rownum <11 ORDER BY rownum
it takes more than a minute to execute.
While a query like:
select main.itemname,main.itemdescription
FROM CONTAINSTABLE([ItemTemp1], itemname, ‘chevrolet’) AS A
join [ItemTemp1] main on a.[key] = main.id
executes in a few seconds.
Will not a full text search perform well when placed in a CTE?
The post is very usefull and so much thanks.
here i have one more doubts that how I can send criteria (dynamic) to this qeury. I want to show the result by page. But the result for some particular conditions. If you have any idea.. Please give me some shot.
thanks again