Tag Archives: row_number

Asp.Net Ajax Web Continuous Pagination Thus Making Endless Scroll

Brief Intro

Nowadays, a web page contains continuous pagination to deal with retrieving lot of data.
This continuous pagination means data views per page doesnt requires to reload a whole page to get next / previous page. It partially reload and attached next data on same page.
So it makes seemingly endless scroll.

This blog post will give you an example on how to do this continuos paging using Asp .Net Ajax capabilities.

Database & Query

I use SQL Server 2008 Northwind Database as sample. Spesifically use Orders, OrderDetails, Products, Employees and Customers tables.

I will make a list of sales transaction along with purchased products, customers and salesman or employees. Total value also being presented.

Tables Design

Below are list of design of tables I used. These list converted into create table statement.
Continue reading

Optimize Large Recordset Custom Paging using ObjectDataSource, ListView, DataPager ASP .Net Controls and Row_number() SQL Server function

Introduction
Retrieve parts of recordset on web request is a very common task. Trivial method using SqlDataSource as Data Connector and GridView or List View with Data Pager Control to view recordset and its page.

Web request resulting million records will degrade performance even though developer break down onto pages. According to some experiments there is a better way than usual method to do web paging.
Please check these links http://www.4guysfromrolla.com/articles/031506-1.aspx, http://www.4guysfromrolla.com/webtech/042606-1.shtml

The best performance using record ranking method and fortunately start from SQL Server 2005 it has record ranking function Row_Number().
In earlier post about SQL Server Row_Number() function, I have explained how to code and the function’s result on sql.

To do custom paging on web, we need ObjectDataSource because it has attributes like EnablePaging, SelectMethod, SelectCountMethod, MaximumRowsParameterName, etc which are enabled us to write a code / class that handles web paging behaviour.
We can write any database operation on methods inside ObjectDataSource so that makes ObjectDataSource flexible.
Other common component on ASP .Net like SqlDataSource is only work for SQL Server and does not has default custom paging capabilities.
Continue reading

Limit Record Number on SQL Server 2005 using Row_number() function

SQL Server has capability to show sequence record number on ‘select’ statement. This also referred as record ranking. The function is Row_number() over (order by <columnname>).
The order of sequence relate to specific column name using Order by columnname part. The order can be ascending or descending.

I use AdventureWorks on SQL Server 2005 for demonstration purpose. The sql code will show Store Type Customer info like Store Name, Territory and Sales Value.
Here’s the sql code:

select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID

where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name order by Sales.Store.Name

It declare Rowno column as a sequence record number and its order is ascending Store Name.

Try with descending order of Store Name:

select row_number() over(order by Sales.Store.Name desc) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name
order by StoreName

We will see the sequence order is descending.

So how to limit record with row_number() function? We can use above sql code and declare it as a table and use that table to filter using rowno column.
For example we want to show first record until 50th use below code:

select * from (
select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer
inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID
inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
where CustomerType='S'
group by Sales.Store.Name,Sales.SalesTerritory.Name
) as tbl where rowno between 1 and 50
order by StoreName

As you can see that above query is declared as tbl table and it use a where and between clause to filter rows.
Try with different parameters i.e between 51 and 100.

This sql code is usefull to break one large recordset into several recordsets. Retrieve large recordset consumes a lot of resources and time on web request. We can use this sql method to devide one large web page into pages.

Cheers,
Agung Gugiaji

Limiting Record Number on DB2

DB2 has capabilities to limit recordset retrieved from ‘select’ statement. In these example, I use SAMPLE database with DB2 LUW.
To limit show only several top records use

select * from employee fetch first 10 rows only

This will show only to 10 records from employee table.
Continue reading