Click here to Skip to main content
12,244,103 members (55,236 online)
Rate this:
 
Please Sign up or sign in to vote.
Hi,


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 21:52pm
ExpertITM1.4K

1 solution

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

Solution 1

  Permalink  
Comments
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,


DECLARE @Start INT
DECLARE @End INT
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
GO


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.160426.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2016
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