Click here to Skip to main content
Rate this: bad
good
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 20:52pm
ExpertITM1.2K

1 solution

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

Solution 1

  Permalink  
Comments
Maulesh1 at 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
0 OriginalGriff 530
1 Sergey Alexandrovich Kryukov 340
2 BillWoodruff 259
3 PIEBALDconsult 230
4 CPallini 220
0 Sergey Alexandrovich Kryukov 10,615
1 Maciej Los 5,354
2 OriginalGriff 5,254
3 CPallini 3,719
4 BillWoodruff 3,476


Advertise | Privacy | Mobile
Web03 | 2.8.141029.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2014
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