Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee selects all records in the Employee table (to create the MyRowNumber values).

I think, this causes selection of all records in the Employee table.

Does it really work so? Or is SQL Server optimized to select only the 5 records from the original Employee table too?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize 
share|improve this question

migrated from programmers.stackexchange.com Jan 14 '13 at 14:57

This question came from our site for professional programmers interested in conceptual questions about software development.

3  
See sqlservercentral.com/articles/T-SQL/66030 and more importantly the ensuing discussion. –  Aaron Bertrand Jan 14 '13 at 14:54
add comment

4 Answers

An alternative to test might be:

;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize
ORDER BY ...;

Yes, you hit the table twice, but in the CTE where you scan the whole table you are only grabbing the key, not ALL of the data. But you really should look at this article:

http://www.sqlservercentral.com/articles/T-SQL/66030/

And the follow-up discussion:

http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx

In SQL Server 2012 of course you can use the new OFFSET / FETCH NEXT syntax:

;WITH x AS 
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...; 
share|improve this answer
    
Should be noted though that OFFSET/FETCH NEXT do not offer any performance benefits over the CTE method –  Akash Jan 14 '13 at 18:02
    
@Akash have you thoroughly tested this? I've observed some plan differences but did not specifically mention anything about the performance because I haven't done any extensive testing. Even if the performance is the same the syntax is slightly less cumbersome. I did blog about it here: sqlblog.com/blogs/aaron_bertrand/archive/2010/11/10/… –  Aaron Bertrand Jan 14 '13 at 18:09
    
Ah, you're right, theres a performance difference. I had read this: blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/… where he mentions no difference, but just saw channel9.msdn.com/posts/SQL11UPD03-REC-02 where he shows theres a lot of difference.. (though in the audio underemphasis the performance difference) –  Akash Jan 14 '13 at 18:22
add comment

Although you may not know the mechanism behind it, you could test this yourself by comparing the performance of your query to: select * from Employee.

The more recent versions of SQL Server do a pretty good job of optimizing, but it can depend on several factors.

How your ROW_NUMBER function performs is going to be driven by the Order By clause. In your example, most would guess EmpID is the primary key.

There are some where clauses that are so complex and/or poorly coded or indexed, you may be better off just returning the whole dataset (it's rare and can be fixed). Using BETWEEN has issues.

Before you assume it would be better to return all the rows to your application and let it figure it out, you should work on optimizing your query. Check the estimates. Ask the Query Analyzer. Test some alternatives.

share|improve this answer
add comment

I know the question is regarding row_number() but i want to add one new feature of sql server 2012. In sql server 2012 new feature OFFSET Fetch next introduced and it is very fast than row_number(). I have used it and it gives me good result hope you guys also fill same experience.

I found one example on http://blogfornet.com/2013/06/sql-server-2012-offset-use/

which is useful. Hope it will help you too for implement new features....

share|improve this answer
1  
Don't down vote without writing why? –  Peter May 6 at 6:34
    
Knee-jerk down voting because of reputation score perhaps? –  BrianV May 21 at 15:41
add comment

I don't think it evaluates to return all rows in the original table. SQL server optimizes. Otherwise it will take a huge amount of time to select a million entries. I am currently using this and it is much faster than selecting all rows. So, sure does not get all the rows. It is however slower than just fetching the first five rows, probably due to time taken in ordering

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.