Optimized solution of Paging by using Count(*) OVER() functionality

Paging through a dataset is always a requirement of any data oriented application. There are several solutions to implement paging. Some of them are client based and some are server based.
Every paging solution needs:

1. Records of only selected page
2. Total no. of records to calculate total of pages available

Whatever solution you implement, you will end up doing at least two queries, first to find total no. of records and secondly to find the records of your selected page.

In oneof my previous posts, I showed you the way of implementing paging by using Row_Number() function of SQL Server 2005. Today, I am showing you an excellent trick to get total of records and list of selected records in just one query. Following is the sample code to implement this logic. This code demonstrates the way you can implement paging very effectively without hitting the server twice. Note down the use of Count(*) OVER() functionality.

Use AdventureWorks
Go

–Change these two variable to parameters of your
–actual stored procedure

Declare @PageNumber int
Declare @PageSize int

–Assume we need page 6 i.e. records from 51-60
Select @PageNumber=6
Select @PageSize=10

–Select only those records which
–fit into a single page
Select Top(@PageSize) * from
(
Select
RowID=ROW_NUMBER() OVER (ORDER BY Name),
ProductID,Name,ProductNumber,ListPrice,
TotalRows=Count(*) OVER() –Count all records
from Production.Product
) A
Where A.RowId > ((@PageNumber-1)*@PageSize)

Generating 1 million rows in less than a second

If you are a SQL Query developer like me than you must came across the situation where you need an auxiliary table of numbers which contain just one column and rows like 1,2,3…….n
There are so many uses of this table like generating dummy data by cross joining this table to another table etc.
Following is a very quick and efficient way of generating 1 million rows in less than a second by using Common Table Expressions or CTE and Recursion of SQL Server. This logic is actually proposed in the book called SQL Server 2005 TSQL Querying by Microsoft press.

Following is the code:

–Declare a variable to hold the
–count of rows to be generated
Declare @p_NumberOfRows Bigint

–We need 1 million rows
Select @p_NumberOfRows=100000;

With Base As
(
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
),
Expand As
(
Select 1 as C
From Base as B1, Base as B2
),
Nums As
(
Select Row_Number() OVER(ORDER BY C) As n
From Expand
)
Select n from Nums Where n<=@p_NumberOfRows
–Remove Maximum Recursion level constraint
OPTION (MaxRecursion 0);

Ranking in TSQL by RANK,DENSE_RANK and Row_Number

Ranking of results has many benefits and it is used in several scenarios of TSQL. Most common example of ranking is to ranking Sales person by their sales. This type of ranking requires you to assign same rank to all those Sales persons who generated the same sales amount, this is called tie. SQL Server 2005 provides you easy and efficient ways for different types of ranking requirements. There are four ranking functions in SQL Server 2005:

1. RANK
2. Dense_Rank
3. Row_Number
4. NTILE

NTILE is actually little bit different and it is used to assign a so called batch numbers or group numbers to the given result.

Following example will help you to understand Row_Number,RANK and Dense_Rank function which are mostly used in Ranking. For the detailed discussion of Ranking functions, please visit SQL Server Books Online

–Turn off intermediate messages
Set Nocount On

–Create a table variable
Declare @mTestTable table
(
id int,
city varchar(50)
)

–Insert some sample data
Insert into @mTestTable values (1,‘London’)
Insert into @mTestTable values (2,‘Karachi’)
Insert into @mTestTable values (3,‘New York’)
Insert into @mTestTable values (2,‘Paris’)

—Execute the query to compare Row_Number,Rank and Dense_Rank function
Select Id,city,
Row_Number() OVER (Order by id) as RowNumber,
Rank() OVER (Order by id) as RankNumber,
Dense_Rank() OVER (Order by id) as DenseRankNumber
from @mTestTable order by id

Finding N most recent orders of each customer – Wonders of Rom_Number()

Retrieving n most recent orders from each customer is a quite frequent requirement from management reports. Before SQL Server 2005, there were tricky and sometimes unmanageable TSQL solutions for this problem. In SQL Server 2005, you can use Row_Number() to get the n most recent orders. Following query uses AdventureWorks database to demonstrate the solution.

Use AdventureWorks
Go
Select CustomerID,OrderDate,SalesOrderNumber
From
(
Select Row_Number()
OVER (Partition by CustomerId Order by OrderDate Desc ) as RowNo,
CustomerID,OrderDate,SalesOrderNumber
from Sales.SalesOrderHeader
) A
Where A.RowNo < 3

How it works:
1. Firstly, the inner query sorts the order of each customer in descending order of OrderDate
2. It assigns a RowNumber to each order from latest to oldest for each customer.
3. RowNumber will become reset for each new customer.
4. After above steps, result will be returned to Outer query.
5. Outer Query just selects only those rows which have RowNumber less than n(here n is 3)

Possible Enhancements:

1. You can change hard coded 3 to a stored procedure parameter and can convert this query to stored procedure for any number of recent orders for each customers.
2. By changing the OVER clause with different combinations of sorting orders and columns, you can get n latest due orders etc. Possibilities are unlimited.

Finding Nth Highest value — Wonders of Row_Number()

Finding Nth highest value is one of the most common requirements of Reports. For example, you may have a requirement of finding 5th Highest salaried employee or 3rd most expensive item etc. In SQL Server 2005, it is now very easy to find Nth item in the result because of the Row_Number function. Using Row_Number is also very efficient and does not impact your system performance. Following is the code to demonstrate the usage of Row_Number() function to find 5th highest value. You can replace 5 by any number to find Nth highest value.

–Disable intermediate SQL Server messages
Set NoCount On

—Create a table variable
Declare @m_TestTable table ( id int, value int)

–Insert 100 Random values
Declare @m_Index int
Select @m_Index=1
While @m_Index <101
Begin
Insert into @m_TestTable values (@m_Index,Ceiling(10000 * Rand()))
Select @m_Index=@m_Index+1
End

—Return 5th highest
Select id,Value from
(
Select id,value ,(Row_Number() Over (Order by value desc)) rowNum
from @m_TestTable
) A
Where A.rowNum=5

Calculate Difference between current and previous rows…..CTE and Row_Number() rocks!

Ever wanted to calculate difference between current row’s column to previous row’s column? It has several usages like calculating trends, monitoring changes etc. Several TSQL solutions are available for this problem but Common Table Expressions and Row_Number function in SQL Server 2005 provides an excellent way to solve this. By using CTE to calculate the difference between current and last row gives you excellent performance and can be used for huge tables. Following is the TSQL code to calculate the difference between CurrentPointValue to PreviousPointValue.

—Declare a test Table variable for our sample
Declare @m_TestTable table
(
DateRecorded datetime,
PointValue int
)

—Insert sample data
Insert into @m_TestTable Values (dateadd(day,1,GetDate()),150)
Insert into @m_TestTable Values (dateadd(day,2,GetDate()),350)
Insert into @m_TestTable Values (dateadd(day,3,GetDate()),500)
Insert into @m_TestTable Values (dateadd(day,4,GetDate()),100)
Insert into @m_TestTable Values (dateadd(day,5,GetDate()),150);

—Create CTE
With tblDifference as
(
Select Row_Number() OVER (Order by DateRecorded) as RowNumber,DateRecorded,PointValue from @m_TestTable
)

—Actual Query
Select convert(varchar, Cur.DateRecorded,103) as CurrentDay, convert(varchar, Prv.DateRecorded,103) as PreviousDay,Cur.PointValue as CurrentValue, Prv.PointValue as PreviousValue,Cur.PointValue-Prv.PointValue as Difference from

tblDifference Cur Left Outer Join tblDifference Prv
On Cur.RowNumber=Prv.RowNumber+1
Order by Cur.DateRecorded