Sql Server Paging – part 2

March 22, 2018 Colin
0 Comment
T-SQL

I left off the last post by saying that this is the code pattern that I was going to follow in future

declare @page int = 103; --Page 1 is the first page
declare @pageSize int = 20;

-- Variables above this line can 

if OBJECT_ID('tempdb..#PagingTable' ) is not null
	drop table #PagingTable

declare @startRow int = ((@page - 1 ) * @pageSize) + 1;
declare @endRow int = @startRow + (@pageSize - 1);

create table #PagingTable(
	rowNum int not null IDENTITY(1,1) primary key clustered,
	[PurchaseOrderID] [int] NOT NULL,
	[SupplierID] [int] NOT NULL,
	[OrderDate] [date] NOT NULL,
	[DeliveryMethodID] [int] NOT NULL,
	[ContactPersonID] [int] NOT NULL,
	[ExpectedDeliveryDate] [date] NULL,
	[SupplierReference] [nvarchar](20) NULL,
	[IsOrderFinalized] [bit] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[InternalComments] [nvarchar](max) NULL,
	[LastEditedBy] [int] NOT NULL,
	[LastEditedWhen] [datetime2](7) NOT NULL,
)

insert into #PagingTable(
	PurchaseOrderID,
	SupplierID,
	OrderDate,
	DeliveryMethodID,
	ContactPersonID,
	ExpectedDeliveryDate,
	SupplierReference,
	IsOrderFinalized,
	Comments,
	InternalComments,
	LastEditedBy,
	LastEditedWhen
)
select
	po.PurchaseOrderID,
	po.SupplierID,
	po.OrderDate,
	po.DeliveryMethodID,
	po.ContactPersonID,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	po.LastEditedBy,
	po.LastEditedWhen
from [Purchasing].[PurchaseOrders] as po
order by PurchaseOrderID desc
offset @startRow rows
fetch next @pageSize rows only;

select
	po.PurchaseOrderID,
	supplier.SupplierName,
	supplier.PhoneNumber,
	po.OrderDate,
	deliveryMethods.DeliveryMethodName,
	contactPeople.FullName,
	contactPeople.EmailAddress,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	people.FullName,
	people.EmailAddress,
	po.LastEditedWhen
from #PagingTable as po
join [Application].[People] as people on people.PersonID = po.LastEditedBy
join [Purchasing].[Suppliers] as supplier on supplier.SupplierID = po.SupplierID
join [Application].[DeliveryMethods] as DeliveryMethods on deliveryMethods.DeliveryMethodID = po.DeliveryMethodID
join [Application].[People] as contactPeople on contactPeople.PersonID = po.ContactPersonID
order by
	po.rowNum asc;

select
	@startRow as startRow,
	@endRow as endRow,
	count(*) as rows
from [Purchasing].[PurchaseOrders]

However, that it’s quite true.

Let’s carry on optimising this query batch and see if we can improve it some more…

 

First things first.  Let’s cut out the stuff that’s not really needed.

There’s no need to calculate the end row, so lets loose that piece of wasted CPU time.

declare @endRow int = @startRow + (@pageSize - 1);

Whilst we are at it, let’s make the last query simpler, we don’t need to return the start and end rows.

select
	count(*) as rows
from [Purchasing].[PurchaseOrders]

 

I’ve also got rid of the commented out lines as they’re no l0nger providing anything useful.   Great, that’s the cosmetic stuff done, but it has no effect on the query execution.

 

Changing the sort order.

In the first post I mentioned that being able to change the sort order was an advantage of pulling down the entire dataset.  There’s nothing stopping us from being able to change the sorting using SQL Server.  Let’s implement that now.

For this let’s introduce two new control variables.

declare @sortColumn int = 1;
declare @sortAscending bit = 1;

The @sortColumn will identify which column will be sorted whilst @sortAscending will define the direction.

As we are already using a temporary table with an identity to determine the output order all we need to do is modify the insert into the #PagingTable to change as we change the values of our new variables.   This means making use of Dynamic SQL.

Dynamic SQL is a double edged sword.  It allows us to create some very power SQL code.   However at the same time there is an opportunity to open up the code to SQL Injection attacks.  This implementation is going to protect against that.

Firstly, as the sortColumn is an integer, there’s no opportunity to send in SQL syntax to the procedure.  The second thing is that we are still going to use parametised SQL even inside the dynamic SQL.

Here’s the code changed over to make use of Dynamic SQL

declare @page int = 103; --Page 1 is the first page
declare @pageSize int = 20;
declare @sortColumn int = 1;
declare @sortAscending bit = 1;

-- Variables above this line can 

if OBJECT_ID('tempdb..#PagingTable' ) is not null
	drop table #PagingTable

declare @startRow int = ((@page - 1 ) * @pageSize) + 1;

create table #PagingTable(
	rowNum int not null identity(1,1) primary key clustered,
	[PurchaseOrderID] [int] NOT NULL,
	[SupplierID] [int] NOT NULL,
	[OrderDate] [date] NOT NULL,
	[DeliveryMethodID] [int] NOT NULL,
	[ContactPersonID] [int] NOT NULL,
	[ExpectedDeliveryDate] [date] NULL,
	[SupplierReference] [nvarchar](20) NULL,
	[IsOrderFinalized] [bit] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[InternalComments] [nvarchar](max) NULL,
	[LastEditedBy] [int] NOT NULL,
	[LastEditedWhen] [datetime2](7) NOT NULL,
)

declare @pagingSql NVarchar(max);

set @pagingSql = 'insert into #PagingTable(
	PurchaseOrderID,
	SupplierID,
	OrderDate,
	DeliveryMethodID,
	ContactPersonID,
	ExpectedDeliveryDate,
	SupplierReference,
	IsOrderFinalized,
	Comments,
	InternalComments,
	LastEditedBy,
	LastEditedWhen
)
select
	po.PurchaseOrderID,
	po.SupplierID,
	po.OrderDate,
	po.DeliveryMethodID,
	po.ContactPersonID,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	po.LastEditedBy,
	po.LastEditedWhen
from [Purchasing].[PurchaseOrders] as po
order by '

set @pagingSql = @pagingSql + ( select
	case @sortColumn
		when 1 then 'OrderDate'
		when 2 then 'ExpectedDeliveryDate'
		when 3 then 'LastEditedWhen'
		else 'PurchaseOrderID'
	end );

set @pagingSql = @pagingSql +' ' + ( select
	case @sortAscending
		when 1 then 'asc'
		else 'desc'
	end );

set @pagingSql = @pagingSql + '
offset @startRow rows
fetch next @pageSize rows only;';

exec sp_executesql
	@stmt = @pagingSql,
	@params = N'@startRow int, @pageSize int',
	@startRow = @startRow,
	@pageSize = @pageSize;

select
	po.PurchaseOrderID,
	supplier.SupplierName,
	supplier.PhoneNumber,
	po.OrderDate,
	deliveryMethods.DeliveryMethodName,
	contactPeople.FullName,
	contactPeople.EmailAddress,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	people.FullName,
	people.EmailAddress,
	po.LastEditedWhen
from #PagingTable as po
join [Application].[People] as people on people.PersonID = po.LastEditedBy
join [Purchasing].[Suppliers] as supplier on supplier.SupplierID = po.SupplierID
join [Application].[DeliveryMethods] as DeliveryMethods on deliveryMethods.DeliveryMethodID = po.DeliveryMethodID
join [Application].[People] as contactPeople on contactPeople.PersonID = po.ContactPersonID
order by
	po.rowNum asc;

select
	count(*) as rows
from [Purchasing].[PurchaseOrders]

 

Here’s the key points from the above changes.

The sql for the paging insert is now wrapped up in a variable

declare @pagingSql NVarchar(max);

This vaidable contains the entire sql statement that is going to be executed.    However, it never contains data, and it never contains values directly from the user input – that would allow SQL Injection attacks.

The column for the sort order is defined using the following case statement

set @pagingSql = @pagingSql + ( select
	case @sortColumn
		when 1 then 'OrderDate'
		when 2 then 'ExpectedDeliveryDate'
		when 3 then 'LastEditedWhen'
		else 'PurchaseOrderID'
	end );

This is decoding the integer @sortColumn and replacing it with the real name of the column to sort on.   The point here is that the calling code does not need to know what the column name is.  The @sortColumns value determines this, and you can expand this as much as you like.

The sort order is then defined

set @pagingSql = @pagingSql +' ' + ( select
	case @sortAscending
		when 1 then 'asc'
		else 'desc'
	end );

Then the query is completed as normal.

the sp_executesql command is used to execute the procedure

exec sp_executesql
	@stmt = @pagingSql,
	@params = N'@startRow int, @pageSize int',
	@startRow = @startRow,
	@pageSize = @pageSize;

This looks a little strange when you first look at it, let me explain what’s happening here.

@stmt = @pagingSql – this passes in the SQL statement that we want to execute.

@Params    this is used to tell the command about any parameters that are used inside the @stmt.  In our case we have @startRow and @pageSize.

So far so good.

@startRow = @startRow,

@pageSize = @pageSize;

This looks really confusing at first, however, we are doing here is passing the parameters values into the dynamic sql.   the @startRow on the left is the name of the variable as it appears in the @stmt.  The one of the right is our locally defined @startRow.

 

If that’s still confusing, here’s a verison of the syntax, with the parameter names changed to make it easier to follow

declare @page int = 103; --Page 1 is the first page
declare @pageSize int = 20;
declare @sortColumn int = 1;
declare @sortAscending bit = 1;

-- Variables above this line can 

if OBJECT_ID('tempdb..#PagingTable' ) is not null
	drop table #PagingTable

declare @startRow int = ((@page - 1 ) * @pageSize) + 1;

create table #PagingTable(
	rowNum int not null identity(1,1) primary key clustered,
	[PurchaseOrderID] [int] NOT NULL,
	[SupplierID] [int] NOT NULL,
	[OrderDate] [date] NOT NULL,
	[DeliveryMethodID] [int] NOT NULL,
	[ContactPersonID] [int] NOT NULL,
	[ExpectedDeliveryDate] [date] NULL,
	[SupplierReference] [nvarchar](20) NULL,
	[IsOrderFinalized] [bit] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[InternalComments] [nvarchar](max) NULL,
	[LastEditedBy] [int] NOT NULL,
	[LastEditedWhen] [datetime2](7) NOT NULL,
)

declare @pagingSql NVarchar(max);

set @pagingSql = 'insert into #PagingTable(
	PurchaseOrderID,
	SupplierID,
	OrderDate,
	DeliveryMethodID,
	ContactPersonID,
	ExpectedDeliveryDate,
	SupplierReference,
	IsOrderFinalized,
	Comments,
	InternalComments,
	LastEditedBy,
	LastEditedWhen
)
select
	po.PurchaseOrderID,
	po.SupplierID,
	po.OrderDate,
	po.DeliveryMethodID,
	po.ContactPersonID,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	po.LastEditedBy,
	po.LastEditedWhen
from [Purchasing].[PurchaseOrders] as po
order by '

set @pagingSql = @pagingSql + ( select
	case @sortColumn
		when 1 then 'OrderDate'
		when 2 then 'ExpectedDeliveryDate'
		when 3 then 'LastEditedWhen'
		else 'PurchaseOrderID'
	end );

set @pagingSql = @pagingSql +' ' + ( select
	case @sortAscending
		when 1 then 'asc'
		else 'desc'
	end );

set @pagingSql = @pagingSql + '
offset @startingRow rows
fetch next @pageSizeToGet rows only;';

exec sp_executesql
	@stmt = @pagingSql,
	@params = N'@startingRow int, @pageSizeToGet int',
	@startingRow = @startRow,
	@pageSizeToGet = @pageSize;

select
	po.PurchaseOrderID,
	supplier.SupplierName,
	supplier.PhoneNumber,
	po.OrderDate,
	deliveryMethods.DeliveryMethodName,
	contactPeople.FullName,
	contactPeople.EmailAddress,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	people.FullName,
	people.EmailAddress,
	po.LastEditedWhen
from #PagingTable as po
join [Application].[People] as people on people.PersonID = po.LastEditedBy
join [Purchasing].[Suppliers] as supplier on supplier.SupplierID = po.SupplierID
join [Application].[DeliveryMethods] as DeliveryMethods on deliveryMethods.DeliveryMethodID = po.DeliveryMethodID
join [Application].[People] as contactPeople on contactPeople.PersonID = po.ContactPersonID
order by
	po.rowNum asc;

select
	count(*) as rows
from [Purchasing].[PurchaseOrders]

 

There we have it.   Wrap that into a stored procedure and hey presto you have paging optimised, and supporting custom sort ordering.

declare @page int = 103; --Page 1 is the first page
declare @pageSize int = 20;
declare @sortColumn int = 1;
declare @sortAscending bit = 1;

-- Variables above this line can 

if OBJECT_ID('tempdb..#PagingTable' ) is not null
	drop table #PagingTable

declare @startRow int = ((@page - 1 ) * @pageSize) + 1;

create table #PagingTable(
	rowNum int not null identity(1,1) primary key clustered,
	[PurchaseOrderID] [int] NOT NULL,
	[SupplierID] [int] NOT NULL,
	[OrderDate] [date] NOT NULL,
	[DeliveryMethodID] [int] NOT NULL,
	[ContactPersonID] [int] NOT NULL,
	[ExpectedDeliveryDate] [date] NULL,
	[SupplierReference] [nvarchar](20) NULL,
	[IsOrderFinalized] [bit] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[InternalComments] [nvarchar](max) NULL,
	[LastEditedBy] [int] NOT NULL,
	[LastEditedWhen] [datetime2](7) NOT NULL,
)

declare @pagingSql NVarchar(max);

set @pagingSql = 'insert into #PagingTable(
	PurchaseOrderID,
	SupplierID,
	OrderDate,
	DeliveryMethodID,
	ContactPersonID,
	ExpectedDeliveryDate,
	SupplierReference,
	IsOrderFinalized,
	Comments,
	InternalComments,
	LastEditedBy,
	LastEditedWhen
)
select
	po.PurchaseOrderID,
	po.SupplierID,
	po.OrderDate,
	po.DeliveryMethodID,
	po.ContactPersonID,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	po.LastEditedBy,
	po.LastEditedWhen
from [Purchasing].[PurchaseOrders] as po
order by '

set @pagingSql = @pagingSql + ( select
	case @sortColumn
		when 1 then 'OrderDate'
		when 2 then 'ExpectedDeliveryDate'
		when 3 then 'LastEditedWhen'
		else 'PurchaseOrderID'
	end );

set @pagingSql = @pagingSql +' ' + ( select
	case @sortAscending
		when 1 then 'asc'
		else 'desc'
	end );

set @pagingSql = @pagingSql + '
offset @startRow rows
fetch next @pageSize rows only;';

exec sp_executesql
	@stmt = @pagingSql,
	@params = N'@startRow int, @pageSize int',
	@startRow = @startRow,
	@pageSize = @pageSize;

select
	po.PurchaseOrderID,
	supplier.SupplierName,
	supplier.PhoneNumber,
	po.OrderDate,
	deliveryMethods.DeliveryMethodName,
	contactPeople.FullName,
	contactPeople.EmailAddress,
	po.ExpectedDeliveryDate,
	po.SupplierReference,
	po.IsOrderFinalized,
	po.Comments,
	po.InternalComments,
	people.FullName,
	people.EmailAddress,
	po.LastEditedWhen
from #PagingTable as po
join [Application].[People] as people on people.PersonID = po.LastEditedBy
join [Purchasing].[Suppliers] as supplier on supplier.SupplierID = po.SupplierID
join [Application].[DeliveryMethods] as DeliveryMethods on deliveryMethods.DeliveryMethodID = po.DeliveryMethodID
join [Application].[People] as contactPeople on contactPeople.PersonID = po.ContactPersonID
order by
	po.rowNum asc;

select
	count(*) as rows
from [Purchasing].[PurchaseOrders]
T-SQL
T-SQL

Leave a Reply

Your email address will not be published. Required fields are marked *