Paging through a SQL result set

by jim morgan26. January 2013 14:38

For an application to browse a large dataset, you need to set a limit to the number of rows returned. As users page up and down through the results, the application needs to return the next block of results. This can be done with a Select with a Subquery. The Subquery have virtually no performance penalties over a straight query. The advantage is that you can use the TOP function to get the next block in the subquery, but return the results in a different order.

The key concepts here are:

1. Save the identifiers to the first and last row of the block for pagination.

2. To get the last or previous block, toggle the ASC/DESC on each element of the original order by.

3. To get the previous block, toggle the booleans <> in the Where Clause.

Examples of 30 row page blocks sorted by LastName, FirstName and SysID (to force uniqueness)

Last Page

SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    ORDER BY LastName DESC, FirstName Desc, SysID Desc) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

First Page

SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    ORDER BY LastName ASC, FirstName ASC, SysID ASC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

Next Page

DECLARE 	@HighLastName varchar(30) = 'MidLN',
    @HighFirstName varchar(30) = 'MidFN',
    @HighSysID int = 1000;
SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    WHERE LastName > @HighLastName
        OR (LastName = @HighLastName AND FirstName > @HighFirstName)
        OR (LastName = @HighLastName AND FirstName = @HighFirstName AND SysID > @HighSysID)
    ORDER BY LastName ASC, FirstName ASC, SysID ASC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

Previous Page

DECLARE 	@LowLastName varchar(30) = 'MidLN',
    @LowFirstName varchar(30) = 'MidFN',
    @LowSysID int = 1000;
SELECT * FROM (
    SELECT TOP (30) LastName, FirstName, SysID
    FROM YourTable
    WHERE LastName < @LowLastName
        OR (LastName = @LowLastName AND FirstName < @LowFirstName)
        OR (LastName = @LowLastName AND FirstName = @LowFirstName AND SysID < @LowSysID)
    ORDER BY LastName DESC, FirstName DESC, SysID DESC) A
ORDER BY LastName ASC, FirstName ASC, SysID ASC

Summary
While you could certainly reduce these statements in verboseness, the optimization on the backend is negligible.
This patterns allows for a generic pagination function to be developed with nirtualy no added overhead.

Tags:

.NET | Development | General | SQL

Use T-SQL ISDATE() function with care

by marc walgren14. January 2013 07:18

I have used the T-SQL function ISDATE() function in many places in my client projects. I came across an interesting behavior that caught me off guard. Try the following T-SQL:


declare @seedDate varchar(20) = '9966'
if isdate(@seedDate) = 1
 Print 'Good Date ' + @seedDate
else
 Print 'Invalid Date ' + @seedDate


declare @myDate Date
select @myDate = @seedDate
print @myDate

 

Running this bit of code produces this result:

Good Date 9966
9966-01-01

Notice that the "9966" value is implicitly converts to Jan. 1, 1996. This implicit conversion produces a valid date and ISDATE returns 1.

Here is a link to the ISDATE documentation from Microsoft.

http://msdn.microsoft.com/en-us/library/ms187347(SQL.105).aspx

Watch out for this situation. Validating with ISDATE without checking the length of the date leaves a hole in the validation.

 

Tags:

Development | SQL

Contact Us  Consulting  Web Development  Data Collection  Flexible Web Lists  Clarion Products  Downloads  How To Order  Site Map  Store  Home
Copyright (c) 1989-2013 Mitten Software Inc., All rights reserved.

Month List