Paging in SQL Server 2000 and 2005?

Abstract

In this article I have discussed in detail about the easier way of paging records using SQL Server 2005. In the due course I have introduced the nuances of using TOP keyword and the cool enhancements added to it. Along with that I would show you how to use TOP command to do paging in SQL Server 2000, albeit at a performance loss, then show how the same result can be achieved with higher performance thanks to new features in SQL Server 2005.

Introduction

Databases products always attract me for many reasons. One simple reason is, .NET framework 1.0 was released and then in a short time its “version 1.1” has been released. If at all you are watching the market you would know by this time, few months back .NET Framework 2.0 has also been released. So what’s the point I am trying to make? The application layer or the front end layer changes very frequently and we need to be updating our self on a daily basis. That’s the pace of the technology either keep up or be left out :)

On the flip side, if we take SQL Server a version was released on 2000 then you had 5 years gap before another major version was released. This gives enough time to master a product and appreciate its advantages. Approximately 6 months back SQL Server 2005 was released (Previously it was code named as Yukon).

Paging is one of the very common features expected in any application. It can either be done at the “Application level” or at the “Database level”. In this article I am concentrating on “Database level Paging”.

Though paging can be done in SQL Server 2000 itself there are performance issues with it. Like, either we need to use Temporary Tables OR Dynamic queries in order to do paging in SQL Server 2000 which by itself is a drawback. I have shown the way I used to do paging in the past with fully functional stored procedure. Subsequently I have discussed about the way to avoid “Temporary tables and Dynamic queries” in the process of paging records in SQL Server 2005.

In short, I have shown the way to do paging using the following new features introduced in SQL Server 2005:

1. Enhancements to TOP keyword
2. Row_number() function
3. CTE – Common Table Expression

Enhancements to Top Keyword:

TOP keyword was very much available in the previous versions of SQL Server itself. If at all one needs to appreciate the enhancements they need to first understand the limitations of the TOP keyword in SQL Server 2000. So let me walk you all through it.

In T-SQL TOP keyword is used to limit the number of rows or list fixed number of rows from the top. For example,

// this would list top 5 records from the specified table.
Select Top 5 FieldName from TableName

What if I would know the Top “N” only during runtime? Is there a way to dynamically pass the number to the TOP keyword? The answer is Big NO.

The work around which people used to do is make use of “Set RowCount On” which accepts parameter / variable. The downside to it is, it affects every query in the current connection.

Example:
Set Rowcount @LimitRowsSelect fieldname from tablename
Set Rowcount 0


The last statement in the above example is used to return SQL Server to normal state. If that line is skipped all future code snippets would also return only @LimitRows records. Hope I have made the limitation clear to you. Now let’s start looking at the enhancement done to this keyword in SQL Server 2005.

1. Now “TOP” keyword accepts parameters
2. Now “TOP” keyword can be used in Update statements as well
3. Now a T-SQL query can be passed as a parameter to “TOP” keyword.

Database Design

For the rest of this article the queries which I write would be based on a user defined table called “Sales”. Let me give you the scripts for the same with some sample data to populate it. This would surely help you to test the code then and there for better understanding.

I refrained from writing my scripts around either Northwind or Pubs database because those databases, by default, don’t come along with SQL Server 2005. The work around is to download the scripts of those 2 database from Microsoft site but its size is 1.5 MB. Downloading it just for testing the scripts in this article would be asking for too much J that’s the reason I created a sample table and worked on it.

//table structure
Create table Sales
(
Sno int identity,
YearOfSales Int,
SalesQuarter Int,
Amount money
)
go

//test records to populate the sales table
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2004, 1, 100)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2004, 2, 200)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2004, 3, 300)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2004, 4, 400)
go

Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2005, 1, 500)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2005, 2, 600)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2005, 3, 700)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2005, 4, 800)
go

Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2006, 1, 900)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2006, 2, 1000)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2006, 3, 1100)
Insert into Sales (YearOfSales, SalesQuarter, Amount) Values (2006, 4, 1200)
go

Enhancements to TOP Keyword – Explained with code snippets

The below code snippet would work in both SQL Server 2000 and SQL Server 2005

/* TSQL stops processing when they have been affected by the specified no of rows */
Set RowCount 2

-- SalesQuarter of first two records would be set as 999
Begin Tran
Update Sales SET SalesQuarter = 999
If @@RowCount = 0
Begin
Print 'No Record found'
Set RowCount 0
End
Commit Tran

To list all the records again we need to set the RowCount as 0.

Set RowCount 0
Select * from Sales

The below code snippet would work only in SQL Server 2005.

As you could see Set Rowcount statement is not used at all. Instead we have made use of TOP keyword.

Begin Tran
Update TOP (2) Sales SET SalesQuarter = 999
If @@RowCount = 0
Begin
Print 'No Record found'
Set RowCount 0
End
Commit Tran

Passing parameters to the TOP keyword

One of the features which were missing in SQL Server 2000 was passing parameters to the TOP keyword. That is now possible in SQL Server 2005.

Declare @topValue Int
Set @topValue = 2
Select Top (@topValue) * from Sales

Even if you give decimal Values it takes only the integer part and ignores the decimal part. The below query would list the Top 3 records.

Declare @topValue Int
Set @topValue = 3.9
Select Top (@topValue) * from Sales

Listing Top n percentage of records from a table

Declare @topValue Int
Set @topValue = 25
Select TOP (@topValue) Percent * from Sales

Out of the entire stuff the coolest enhancement is we could now pass a query as a parameter to TOP keyword. The below code snippet helps us in finding out the top half of the existing records.
-- It would list 50% of the records
Select TOP (Select cast((Select Count(*)/2 from Sales ) as int)) * from Sales

Hope that you would have got the feel of what’s there in store as far as TOP keyword is concerned in SQL Server 2005.

In a nutshell, TOP keyword in SQL Server 2005 can be used for the following:
1. It can be used to specify numbers or percent of rows to be returned
2. It can be used along with Insert, Update and Delete statements
3. Now we can pass expressions to it.

Now, let me explain the way in which I used to do paging while working with SQL Server 2000.

Paging explained in SQL Server 2000

There are two ways; either we need to make use of Temporary tables or dynamic queries for doing paging in SQL Server 2000. In SQL Server 2000, the maximum length of dynamic SQL Statement is limited to the maximum size of an nvarchar field. That is, max can be 4000 characters. Since I want to keep the sample as simple as possible and as I am sure in this example we won't need more than 1000 characters I have declared @strQuery as nvarchar (1000).

Create Procedure dbo.testPaging_SalesTable
@intNumOfRecords int,
@intPageNum int
As

/*
Stored Procedure: dbo.testingPaging_SalesTable
Creation Date: 06/20/2006
Written by: Vadivel Mohanakrishnan

Purpose : Helps in paging records within Sales table by just passing two variables.

Testing :
Exec dbo.testPaging_SalesTable 5,1
Exec dbo.testPaging_SalesTable 5,2
*/

Declare @strQuery nvarchar(1000)
Declare @intTotal int
Set @intTotal = @intNumOfRecords * @intPageNum

Set @strQuery = 'Select TOP ' + Cast(@intNumOfRecords as Varchar(5)) + ' * From Sales'
Set @strQuery = @strQuery + ' Where Sno Not in (Select TOP ' + Cast(@intTotal as Varchar(5))
Set @strQuery = @strQuery + ' Sno From Sales Order By Sno ) '

--Lets execute the dynamically built query
Exec sp_executesql @strQuery

Go

If I execute the stored procedure as Exec dbo.testPaging_SalesTable 5, 0 it means give me 5 records for page 1. Similarly if pass parameters as 5, 1 then it means give me 5 records for page 2. Hope till this part it’s clear for you!

Normally dynamic queries are something which one would prefer to avoid for performance reasons. Discussion in this topic is totally out of context so let me move on to show you all the way to implemented paging in SQL Server 2005.

Paging explained in SQL Server 2005
In SQL Server 2005, a new function by name “Row_Number()” has been introduced. This function helps us in returning the running count of all the rows in the specified or current scope.
Method 1:

With the help of Row_Number() function we can get the running count of all the rows and by using the between clause we can filter the records as per our need.

With SQLPaging As
(
Select Row_number() Over (Order by Sno) as RunningNumber, YearOfSales, SalesQuarter, Amount
From Sales
)
Select * from SQLPaging
Where RunningNumber between 2 and 3

Here in this method I have introduced you to the new concept called “Common Table Expression” shortly called as CTE.

CTE is an expression that returns temporary named result set from a query. Find below a very basic explanation for the above code snippet.

1. In the WITH clause we need to defines the table(s) and column(s). In our case the name of the CTE is SQLPaging.
2. Within the WITH clause is where we need to write our query which would return the expected result set.
3. Outside of the WITH clause is where we write a SELECT statement that references the SQLPaging table.

If you look at the above syntax you can draw similarity with Views. The only difference is Views are permanent in the database as they are created as a database object; whereas CTE aren’t created as a database object.

Method 2:

This method is an extension of the above method. I would pass page size and page number as input and then with the help of Row_Number() function filter out data pertaining to that particular page.

Create Proc dbo.uspFetchPagedData
(
@pPageSize as int,
@pPageNum int
)
As

/*
Stored Procedure: dbo.testingPaging_AuthorsTable
Creation Date: 06/22/2006
Written by: Vadivel Mohanakrishnan

Purpose : Paging using Row_Number and CTE in SQL Server 2005.

Testing :
Exec dbo.uspFetchPagedData 5,1
Exec dbo.uspFetchPagedData 5,2

*/

With SQLPaging2
As
(
Select Top(@pPageSize * @pPageNum) Row_number() Over (Order by Sno) as RunningNumber,
YearOfSales, SalesQuarter, Amount
From Sales
)
Select * from SQLPaging2 where RunningNumber > ((@pPageNum - 1) * @pPageSize)

Now that the stored procedure is also completed you can go ahead and test it yourself. Moreover the logic in the above code is pretty straight forward and I haven’t done any rocket science here :)

Conclusion

Hope I have made the point across.Let me recap what I have discussed in this article. I have explained the way in which Paging was done in SQL Server 2000 and the way we could do the same in SQL Server 2005. In that due course I introduced you all to the enhancements made to TOP keyword and about the new CTE and Row_Number() function in SQL Server 2005.

Happy programming!

Technorati tags: SQL, SQL Server, SQL Server 2005