Using CTE in data paging

Transact-SQL

Mark Shvarts


I explore a possibility of using Common Table Expression (CTE) instead of temporary table in data paging. But the problem is that it looks like I can reference CTE only once in my script.

I need to return data for a current page as well as the row count for entire selection. Here is my script:

with names(row_num, first_name, last_name)
as
(
select
row_num = row_number() over(order by last_name, first_name),
first_name,
last_name
from contacts
where last_name like 's%'
and first_name like 'm%'
)


select
row_num,
first_name,
last_name
from names
where row_num between 41 and 60 -- 3rd page
order by row_num

select count(*) from names
go


It selects 20 records, however it displays an error when it tries to execute select count(*):

Msg 208, Level 16, State 1, Line 24
Invalid object name 'names'.

If I try to swap these two queries, it does select count(*) but displays same message for the second query. That means that I can reference CTE only once I tried to play with ; but result is the same.

Top

Re: Using CTE in data paging

Bushan


You cannot use the CTE more than once



Top

Re: Using CTE in data paging

Mark Shvarts

This is what I suspected, unfortunately.



Top

Re: Using CTE in data paging

Umachandar Jayachandran - MS

You can do below instead:
with names(row_num, first_name, last_name)
as
(
select
row_num = row_number() over(order by last_name, first_name),
first_name,
last_name
from contacts
where last_name like 's%'
and first_name like 'm%'
)
select
row_num,
first_name,
last_name
from names
where row_num between 41 and 60 -- 3rd page
order by row_num;

select @@ROWCOUNT

go

But this is really unnecessary. Why can't you get the count on the client side after you fetch the results This is more efficient than sending one more result from the server. CTE can be referenced (one or more times) only in one query and statement.





Top

Re: Using CTE in data paging

Mark Shvarts

I select only 20 records at a time. But I need to send the count for entire selection. If I count on the client side I will always get up to 20 .


Top

Re: Using CTE in data paging

Umachandar Jayachandran - MS

Oh, I see. But that count is dynamic and couldn't really serve any purpose in most cases. If there are new rows being added in the table or existing rows being modified to match the WHERE clause then the next time you page the results will be entirely different. It is also possible that you get the same rows that you visited in the previous page. It really depends on the query, data characteristics etc to determine the actual number of rows and then page through it. This will work for static results.



Top