Click here to Skip to main content
13,050,450 members (98,674 online)
Rate this:
Please Sign up or sign in to vote.

When we want to use row number function in cte is less efficient in paging instead of we can use temp table and create index on row number column.

When cte is use for paging than we need to use rownumber function. But we can create temp table and included with row number column and create index on row number column than sql will perform index seek within the index to reach low boundary row until it reaches the high boundary row.

By using temp table Only the rows of the requested page of results are scanned within the index. While in Cte Index scan is performed and it scans from first row.

So, i want to know that is it right or not?
Posted 8-Nov-12 20:52pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Maulesh1 10-Nov-12 0:16am
Thanks Deepak bt I am not getting my answer
when paging in gridview by cte than I am using following code,

SELECT @Start = 14000,@End = 14050;

WITH EmployeePage AS
(SELECT LastName, FirstName, EmailAddress,
ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
FROM Employee)
SELECT LastName, FirstName, EmailAddress
FROM EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress

This method is batter or by using temp table is batter.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100