Systems Engineering and RDBMS

Paging of Record-Sets in SQL Server 2005

Posted by decipherinfosys on August 10, 2007

Yesterday, we had covered the paging of record-sets and the COUNT(STOPKEY) optimization in Oracle. We will use the same set of examples today for SQL Server 2005. So, how does this work in the case of SQL Server 2005 then since ROW_NUMBER() analytic function has been made available now in this release. Let us modify the code from the previous post to work in SQL 2005:

Option#1:

select * from (
select x.*, RowNum row_num
from (select *, row_number() over (order by FirstName, LastName) as RowNum from big_test_tbl) x
where RowNum <= 100
) as y where row_num >= 75

Option#2:

select * from (
select x.*, RowNum row_num
from (select *, row_number() over (order by FirstName, LastName) as RowNum from big_test_tbl) x
) as y where row_num between 75 and 100

There is no difference in performance in the case of SQL Server 2005 for either of these options. If you run these commands using the “SET STATISTICS PROFILE ON” option, you can see that for both of these queries, the optimizer choses to scan the entire table for the 3 million records and then restricts the output. This is the starting operator:

|–Table Scan(OBJECT:([AdventureWorks].[dbo].[big_test_tbl]))

The EstimateRows will show you 3 million mark and TotalSubtreeCost and the EstimateIO and EstimateCPU will be pretty high.

Now, if we create an index on the ordered columns:

create index test on big_test_tbl (FirstName, LastName)
/*filegroup clause and other index parameters go here*/

In that case, either of the above two options perform the same – however, they now use this operator:

|–RID Lookup(OBJECT:([AdventureWorks].[dbo].[big_test_tbl]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

And they restrict based on the look-up against the 100 record mark. The EstimateIO, EstimateCPU and the TotalSubtreeCost values are very less as well. And more than likely, that will be your scenario as well since you will never be querying a heap table in actual production code. However, if the order by columns are not indexed and even if you have a filter criteria that is indexed, there is no COUNT(STOPKEY) optimization in SQL Server 2005 to provide the same level of benefit as Oracle does. Here is an example:

drop index big_test_tbl.test
go

create unique index test on big_test_tbl (ContactID)
/*filegroup clause and other index parameters go here*/

select * from (
select x.*, RowNum row_num
from (select *, row_number() over (order by FirstName, LastName) as RowNum from big_test_tbl where ContactID < 10000) x
where RowNum <= 100
) as y where row_num >= 75

The operator used is again the same:

|–RID Lookup(OBJECT:([AdventureWorks].[dbo].[big_test_tbl]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

However, since the ordered columns are not in the index, it still goes through 9999 records prior to filtering the recorods out. So, the bottom line is that there is no equivalent to the COUNT(STOPKEY) optimization in SQL Server 2005, however, if you have done your index designs carefully, you will achieve the same thing in SQL Server which is what is needed anyways.

One Response to “Paging of Record-Sets in SQL Server 2005”

  1. […] Paging of record sets in SQL Server 2005 or SQL Server 2008: https://decipherinfosys.wordpress.com/2007/08/10/paging-of-record-sets-in-sql-server-2005/ […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: